Transforming Jira Dashboard: Automating Updates with Jira API

In our previous article on the Alternative Jira Dashboard, we provided a general overview of the Jira Dashboard we actively use within our company. In that setup, Excel was used as the data source. Updates were done manually using a free Jira Cloud plugin.

By transitioning the data source from Excel to the Jira API, we have automated the update process, taking the project to the next level.

Below is the Power Query M Script that pulls the main table of the project.

let
    //Fetch Data
    url = "https://<yourcompany>.atlassian.net/rest/api/2/search?fields=issuetype,key,summary,assignee,reporter,priority,status,resolution,created,updated,timeoriginalestimate,timespent,components,labels,resolutiondate,parent,customfield_10020,customfield_10161,customfield_10026,customfield_10021&jql=<your_filter>",

    GetPage = (startAt as number) =>
        let
            response = Json.Document(Web.Contents(url, [Query=[startAt=Text.From(startAt), maxResults="100"]])),
            issues = response[issues],
            nextStartAt = if List.Count(issues) = 100 then startAt + 100 else null
        in
            [issues=issues, nextStartAt=nextStartAt],
    
    GetAllPages = List.Generate(
        () => [result = GetPage(0)],
        each [result][nextStartAt] <> null,
        each [result = GetPage([result][nextStartAt])],
        each [result][issues]
    ),

    allIssues = List.Combine(GetAllPages),
    issuesTable = Table.FromList(allIssues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    //Expend Fields
    expandedFields = Table.ExpandRecordColumn(issuesTable, "Column1", {"key", "fields"}, {"Key", "fields"}),

    expandedTable = Table.ExpandRecordColumn(expandedFields, "fields", 
        {"issuetype" , "summary", "assignee", "reporter", "priority", "status", "resolution", "created"    , "updated"    , "timespent"   , "timeoriginalestimate", "components", "labels", "parent"   , "resolutiondate", "customfield_10020","customfield_10161", "customfield_10026", "customfield_10021"},
        {"Issue Type", "Summary", "Assignee", "Reporter", "Priority", "Status", "Resolution", "createdZone", "updatedZone", "Σ Time Spent", "Σ Original Estimate" , "Components", "Labels", "Epic Link", "resolvedZone"  , "Sprint"           ,"Tester"           , "StoryPoints"      , "Flagged"}),

    expandedIssuetype = Table.ExpandRecordColumn(expandedTable, "Issue Type", {"name"}, {"Issue Type"}),
    expandedAssignee = Table.ExpandRecordColumn(expandedIssuetype, "Assignee", {"displayName"}, {"Assignee"}),
    expandedReporter = Table.ExpandRecordColumn(expandedAssignee, "Reporter", {"displayName"}, {"Reporter"}),
    expandedPriority = Table.ExpandRecordColumn(expandedReporter, "Priority", {"name"}, {"Priority"}),
    expandedStatus = Table.ExpandRecordColumn(expandedPriority, "Status", {"name"}, {"Status"}),
    expandedResolution = Table.ExpandRecordColumn(expandedStatus, "Resolution", {"name"}, {"Resolution"}),
    expandedComponents = Table.TransformColumns(expandedResolution, {"Components", each Text.Combine(List.Transform(_, each _[name]), ", ")}),
    expandedLabels = Table.TransformColumns(expandedComponents, {"Labels", each Text.Combine(_, ", ")}),
    expandedEpiclink = Table.ExpandRecordColumn(expandedLabels, "Epic Link", {"name"}, {"Epic Link"}),
    expandedSprint = Table.TransformColumns(expandedEpiclink, {"Sprint", each if _ = null then null else Text.Combine(List.Transform(_, each _[name]), ", ")}),
    expandedTester = Table.ExpandRecordColumn(expandedSprint, "Tester", {"displayName"}, {"Tester"}),
    expandedFlag = Table.TransformColumns(expandedTester, {"Flagged", each if _ = null then null else Text.Combine(List.Transform(_, each _[value]), ", ")}),
    
    //Transform Data
    expandedCreated = Table.AddColumn(expandedFlag, "Created", each DateTimeZone.FromText([createdZone])),
    expandedUpdated = Table.AddColumn(expandedCreated, "Updated", each DateTimeZone.FromText([updatedZone])),
    expandedResolved = Table.AddColumn(expandedUpdated, "Resolved", each DateTimeZone.FromText([resolvedZone])),

    #"Replaced Value1" = Table.ReplaceValue(expandedResolved,null,0,Replacer.ReplaceValue,{"StoryPoints"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value1","Waiting for Deployment","Wait Depl.",Replacer.ReplaceText,{"Status"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Issue Type", type text}, {"Key", type text}, {"Summary", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Priority", type text}, {"Status", type text}, {"Resolution", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Σ Original Estimate", Int64.Type}, {"Σ Time Spent", Int64.Type}, {"Components", type text}, {"Labels", type text}, {"Epic Link", type text}, {"Resolved", type datetime}, {"Tester", type text}, {"StoryPoints", Int64.Type}})
    
in
    //Finalize Data
    #"Changed Type"

The Jira Cloud platform REST API (atlassian.com)

Prompt: Steps to connect Jira with Power Query M script?