This post is a bit obsolete and was superseded by this version. I prefer a recursive approach to this List / Loop based one. The recursive approach doesn't require to check before for the total number of issues and is more generic for any Rest Api Pagination handling (e.g. HCL Connections export)
I keep this post here for reference.
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 Filter. There is no need to edit the M-code to use it.
It is crucial to reduced 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. (no hard to understand Jira internal field names)
Parameters
The implementation makes use of parameters. Information is provided in the parameter configurations especially descriptions.
You have to provide the Jira FilterId. 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.)
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.
You shall set the MaxResults parameter to the current server setting. (The max your admin can configure is 1000) If you have some memory issues you might want to lower down this value.
Implementation
Steps Decomposition
The process is decomposed in multiple steps as shows below:
See this file in Gist.
Some steps make use of previous variables defined in a previous step by reference. For example. StartAt function reference to GetFields. Or GetFields is used in StartAt and GetIssues.
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 one page of issues
This is handle in the StartAt function:
(start as number) =>
let
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))),
issues = Source[issues],
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"Column1.key", "Column1.fields"})
in
#"Expanded Column1"
Loop over pages / Bypass MaxResults limitation
The loop over all pages to bypass the maxresults Jira limitation is handled in the GetIssues step, specially this part:
cnt = Number.RoundUp(total/maxResults) ,
total1 = List.Numbers(0,cnt,maxResults),
#"Converted to Table" = Table.FromList(total1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each StartAt([Column1])),
Expand Fields and Rename
This is done via this code:
FilterColumnTable = GetFilterColumns,
IssuesRenamed = Table.ExpandRecordColumn(#"Expanded Data", "Column1.fields", FilterColumnTable[value],FilterColumnTable[label] )
Download
You can get my PowerBI template upon request (I would like to know how many are interested in it.)
No comments:
Post a Comment