Jira to Power BI

I have worked on an improved version for importing a Jira filter into Microsoft Power BI.
The main advantage of the new approach is that it will import only the fields as defined in your filter columns and also the expanding and renaming from field Ids to real field names is done automatically. (WYSIWYG)

 

This post was also cross-shared on LinkedIn here. You can follow comments and reactions there.

Video Screencast

Key advantages

The approach below provides an easy-to-use/ configure solution with parameters e.g. for your Jira Url, Filter, Fields, etc. There is no need to edit the M-code to use it.

It is crucial to reduce the fields imported to avoid blowing up your data. In this solution, the fields imported do not have to be configured and found out manually. You configure your filter in Jira with the fields you want as columns and you will get these fields without any step required in PowerBI.

Moreover,  the fields will be automatically renamed according to Jira UI display names. (so it becomes easy to understand Jira internal field names WYSIWYG)

Parameters

The implementation makes use of parameters. Information is provided in the parameter configurations, especially descriptions.

JiraRootUrl (mandatory)

JiraFilterId (mandatory)

You have to provide the Jira FilterId. This must be a number. You can get it easily if you open your filter in Jira from the URL in the address bar. The URL normally looks like <jirarooturl>/issues?filter=1234

(The FilterId is required for renaming the fields. To get the date the FilterName would have worked as well.)

JiraFields (optional)

If this parameter is left empty or set to "*filter", it will try to use the fields as defined in the filter custom column.

(image source https://community.atlassian.com/t5/Jira-Software-articles/Introducing-the-new-issue-search-to-Jira-Cloud-users-updated/ba-p/2276375)

It is quite nice to be able to import only the fields you have defined in Jira.

But if you don't bother about importing too much data into PBI, you can also import all fields and do the sorting out in PowerBI. 

By default, if no Fitler columns were set, all "*navigable" are imported.

If you want to overwrite your Filter column settings, you can choose "*navigable" or "*all* as parameter value.

maxResults

You shall set the MaxResults parameter to your instance current setting. (The max your admin can configure is 1000) If you have some memory issues you might want to lower this value. 

maxTotal (optional)

You can optionally limit the total number of issues imported. (By default all the issues returned by the filter are imported.) This might be useful for playing/debugging with light data.

Connection

When using the template, PowerBI will prompt you to enter your credentials for connecting to your Jira Instance.

Here you shall choose Basic and enter your REST API Key as password.

Implementation

Steps Decomposition

The process is decomposed in multiple steps as shown below:

See this file in Gist.

Some steps make use of previous variables defined in a previous step by reference. For example. GetFilterColumns is used in GetFields and GetIssuesTable.

Get Filter Field Names

To get the list of Fields to be imported as defined in the Jira UI via filter columns, a call to this rest API is done: JiraRootUrl&"/rest/api/2/filter/"&JiraFilterId&"/columns"

// STEP GetIssuesTable

let

    // Get fields url parameters (txt CSV) from Filter definition (uses JiraFilterId) or user parameter JiraFields

    FieldsText = if JiraFields <> null then JiraFields else

        let

            restUrl = JiraRootUrl & "/rest/api/2/filter/" & Text.From(JiraFilterId) & "/columns",

            response = Web.Contents(restUrl,

                        [ManualStatusHandling={404}]),

            responseMetadata = Value.Metadata(response),

            responseStatus = responseMetadata[Response.Status],

            Columns = if responseStatus =404 then "" else

                let

                    FilterColumns = Json.Document(response),

                    #"Converted to Table" = Table.FromList(FilterColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

                    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"label", "value"}, {"label", "value"}),

                    //Columns2 = Table.RemoveMatchingRows(#"Expanded Column1",{[label="Key"]},"label") // remove Key because not exported as field

                    Fields = Text.Combine(#"Expanded Column1"[value],",")

                in

                    Fields

        in

            Columns,    

    // Call function GetItems

    IssuesTable = GetItems(null,FieldsText)

in

    IssuesTable

Note the use of Text.Combine to convert the Table Column List to a String with , as delimiter.

Export issues (recursive approach)

This is done in the GetItems function:

// STEP (function): GetItems

(optional previousTable as table) =>

let

    

    start = if (previousTable = null) then 0 else Table.RowCount(previousTable),

    maxRes = if (maxTotal=null) or (start + maxResults < maxTotal) then maxResults else maxTotal - start ,

    Fields = GetFields,

    Source = Json.Document(Web.Contents(JiraRootUrl&"/rest/api/2/search?jql=filter="&JiraFilterId&"&maxResults="&Text.From(maxRes)&"&fields="&Fields&"&startAt="&Text.From(start))),

    ChunkTable = Table.FromList(Source[issues], Splitter.SplitByNothing(), {"Column1"}),

    //ChunkTable = Table.FromList(Source[issues], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    //#"Expanded Column1" = Table.ExpandRecordColumn(ChunkTable, "Column1", {"key", "fields"}, {"Column1.key", "Column1.fields"})

    table1 = if previousTable = null then ChunkTable else Table.Combine({previousTable,ChunkTable}),

    ItemsTable = if (Table.RowCount(ChunkTable)< maxRes) or (maxRes = maxTotal-start) then table1 else @GetItems(table1)

in

   ItemsTable

Renaming Fields

This is done in the Expand&Rename step:

// STEP Expand&Rename

let

    IssuesTable = GetIssuesTable,

    ExpIssues = Table.ExpandRecordColumn(IssuesTable, "Column1", {"key", "fields"}, {"Key", "Fields"}),

    // expand fields s. https://exceed.hr/blog/dynamically-expand-table-or-record-columns-in-power-query/

    IssuesRaw = Table.ExpandRecordColumn(ExpIssues, "Fields", Record.FieldNames(ExpIssues{0}[Fields]),Record.FieldNames(ExpIssues{0}[Fields]) ),

   

    // extracting field info

    restUrl = JiraRootUrl&"/rest/api/2/field",

    FieldsList= Json.Document(Web.Contents(restUrl)),

    #"Converted to Table" = Table.FromList(FieldsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    FieldsIdNameTable = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"}),

   

    // rename using https://learn.microsoft.com/en-us/powerquery-m/table-renamecolumns and MissingField.Ignore

    Issues = Table.RenameColumns(IssuesRaw,Table.ToRows(FieldsIdNameTable),MissingField.Ignore),

   

   

    #"Expanded Issue Type" = if not(Table.HasColumns(Issues,"Issue Type")) then Issues else

        Table.ExpandRecordColumn(Issues, "Issue Type", {"name"}, {"Issue Type"}),

    #"Expanded Status" = if not(Table.HasColumns(Issues,"Issue Type")) then #"Expanded Issue Type" else

       Table.ExpandRecordColumn(#"Expanded Issue Type", "Status", {"name"}, {"Status"}),

    #"Expanded Risk Probability" = if not(Table.HasColumns(Issues,"Risk probability")) then #"Expanded Status" else

       Table.ExpandRecordColumn(#"Expanded Status", "Risk probability", {"value"}, {"Risk probability"}),

    #"Expanded Risk Consequence" = if not(Table.HasColumns(Issues,"Risk consequence")) then #"Expanded Risk Probability" else

       Table.ExpandRecordColumn(#"Expanded Risk Probability", "Risk consequence", {"value"}, {"Risk consequence"}) ,

   #"Expanded Assignee" = if not(Table.HasColumns(Issues,"Assignee")) then #"Expanded Risk Consequence" else

       Table.ExpandRecordColumn(#"Expanded Risk Consequence", "Assignee", {"name"}, {"Assignee"}),

   #"Expanded Reporter" = if not(Table.HasColumns(Issues,"Reporter")) then #"Expanded Assignee" else

       Table.ExpandRecordColumn(#"Expanded Assignee", "Reporter", {"name"}, {"Reporter"})    

in

    #"Expanded Reporter"

Expanding Fields

In a last separate step you can expand the fields to get the relying key property. See for example for project name and status.

You can code this in a generic way testing if the field was exported with HasColumns see extract below.

// STEP: Expand&Rename

let Issues = GetIssuesTable, #"Expanded Issue Type" = if not(Table.HasColumns(Issues,"Issue Type")) then Issues else Table.ExpandRecordColumn(Issues, "Issue Type", {"name"}, {"Issue Type"}), #"Expanded Status" = if not(Table.HasColumns(Issues,"Issue Type")) then #"Expanded Issue Type" else Table.ExpandRecordColumn(#"Expanded Issue Type", "Status", {"name"}, {"Status"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Status",{{"Column1.key", "Key"}}) in #"Renamed Columns"

Download

You can get my PowerBI template upon request by email (I would like to know how many are interested in it.)

Troubleshooting

Avoid Firewall Error

In some cases, you might get such an error: Formula.Firewall: Query 'GetIssuesTable' (step 'IssuesTable') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Explanation see https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall

To solve this I recommend deactivating the Firewall as shown below:

Go to File-> Options&Settings->Options->Privacy and select 'Always ignore Privacy Level settings'

Changelog

2024-04-25

+: Improved handling if Filter Columns are not set

fix: firewall error

+: Renaming of Field Ids to Field Labels also if Filter Column is not set

+: Improved handling of default value for JiraFields. Default is "*filter" instead of manually preset

2022-02-01

+: Expand Issue Type, Status - if exported

+: Expand Key

bug: remove [] around Fields in rest URL. First and Last Fields were not imported

+: Remove Key from Fields

c: Fields as Optional parameters

c: Improve Parameter description e.g. rooturl wihout ending /

c: change parameter FilterId from Text to Numeric 

See also

Jira to Power BI (Recursive approach)

PowerBI and Jira integration guide | by Auguste | Medium