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 internal field names to display names is done automatically. (WYSIWYG)
See this file in Gist.
Go to File-> Options&Settings->Options->Privacy and select 'Always ignore Privacy Level settings'
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 internal field names to display names is done automatically. (WYSIWYG)
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.
JiraFilterId
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, it will try to use the fields as defined in the filter custom column.
If you haven't set the column manually you will get an error on the rest API call.
maxResults
You shall set the MaxResults parameter to your server 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.
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 e.g. filter columns, a call to this rest API is done: JiraRootUrl&"/rest/api/2/filter/"&JiraFilterId&"/columns"
// STEP: GetFilterColumns
let
// Get list of fields from Filter definition
FilterColumns = Json.Document(Web.Contents(JiraRootUrl&"/rest/api/2/filter/"&JiraFilterId&"/columns")),
#"Converted to Table" = Table.FromList(FilterColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"label", "value"}, {"label", "value"})
in
#"Expanded Column1"
// STEP: GetFields
let
// Get list of fields from Filter definition
Fields = if JiraFields <> null then JiraFields else
let
FilterColumnsTable = GetFilterColumns,
Fields2 = Text.Combine(FilterColumnsTable[value],", ")
in
Fields2
in
Fields
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 GetIssuesTable step:
// STEP: GetIssuesTable
let
IssuesTable = GetItems(),
ExpIssuesTable = Table.ExpandRecordColumn(IssuesTable, "Column1", {"key", "fields"}, {"Column1.key", "Column1.fields"}),
Issues = if (JiraFields = null) then
let
FilterColumnTable = GetFilterColumns,
IssuesRenamed = Table.ExpandRecordColumn(ExpIssuesTable, "Column1.fields", FilterColumnTable[value],FilterColumnTable[label] )
in
IssuesRenamed
else
let
Fields = GetFields,
FieldsList = Text.Split(Fields,","),
IssuesNoRenamed = Table.ExpandRecordColumn(IssuesTable, "Column1.fields", FieldsList,FieldsList )
in
IssuesNoRenamed
in
Issues
The automatic renaming from the filter column definition is only done if no JiraFields are configured manually.
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.
// 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 (I would like to know how many are interested in it.)
Troubleshooting
Error on rest API call
If you get an error on this API call
FilterColumns = Json.Document(Web.Contents(JiraRootUrl&"/rest/api/2/filter/"&JiraFilterId&"/columns"))
it is because you haven't customized the column fields in your filter.
In your filter view, customize which fields you want to be displayed as a column:
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
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
No comments:
Post a Comment