May 10, 2021

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.


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:


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.


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 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

 

No comments:

Post a Comment