Below is what I got to. I don't recall exactly the person that I got this from.. I cut down on a lot of the extra coments and tried to make is simple. but basically it was from a post that showed how to seperate the "query" / post call into what is teh "WEbsite" from the parameters. This allows Power BI Service to recognize in essence that you are logging int the same server.
Where I put in "YOUR" are the main things you will need to change...
You can generate any JQL statement that you need to pull teh list of issues from Jira... Suggest that you find a good logical way to organize the query so you are not pulling 10's of thousands of rows back from Jira in one query. YOu can use PowerBI to execute multiple queries in parallel and then merge back into one table later in the ETL..
I suggest that you start with hardcoding the numIssues variable with a small number, so as to allow the query to run quicker and you can work through problems faster..
Good luck and I hope this helps..
***************************************************************************************************************************
let
// Get the Number of Issues to retrieve
// apikey is YOUR own web token api key from Atlassian
Source = Json.Document(
Web.Contents("https://YOURHOSTNAME.jira.com/",
[
RelativePath="rest/api/2/search",
Query=
[
maxResults="100",
jql="project=YOUR PROJECT",
startAt="0",
apikey="YOUR WEBKEY"
]
]
)),
// Number of issues to process
numIssues = Source[total],
// Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),
// User this to get a list of lists for 100 issues at a time.
data = List.Transform(startAtList, each Json.Document(Web.Contents("https://YOURHOSTNAME.jira.com/",
[
RelativePath="rest/api/2/search",
Query=
[
maxResults="100",
jql="project=YOUR PROJECT",
startAt=Text.From(_),
apikey="YOUR WEBKEY"
]
]))),
// ===== Consolidate records into a single list ======
// so we have all the records in data, but it is in a bunch of lists each 100 records long.
//
// In essence we need extract the separate lists of issues in each data{i}[issues] for 0<=i<#"total"
// and concatenate those into single list of issues .. from which then we can analyse
//
// so first create a single list that has as its members each sub-list of the issues,
// 100 in each except for the last one that will have just the residual list.
// So iLL is a List of Lists (of issues):
iLL = List.Generate(
() => [i=-1, iL={} ],
each [i] < List.Count(data),
each [
i = [i]+1,
iL = data{i}[issues]
],
each [iL]
),
// and finally, collapse that list of lists into just a single list (of issues)
issues = List.Combine(iLL),
// =============================================================
// so now we've got the long list of issues back from JIRA
// ... now to do something with this - extract the bits we want
//
// at this point you have two options
// 1. just keep all the code below in place
// .. and you'll see the results of this straight away. Do that first.
// 2. after you have done that option 1., I recommend:
// - re-edit this query in Advanced Editor
// - delete all the code below. yes, DELETE it all
// - close/save this query, and then
// - to figure out how all this works,
// - follow the guidance from this post:
// https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
// ... starting half way down with para that begins "Drilling from the top level of the record ..."
// Those details there made all the difference to me figuring this out
// righto .. so here is the code that is one way to split out the details into some fields that might be interesting to you
#"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"issue", "fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"assignee", "created", "creator", "description", "issuetype", "parent", "priority", "project", "reporter", "resolution", "resolutiondate", "status", "summary", "updated"}, {"assigneeF", "created", "creatorF", "description", "issuetypeF", "parentF", "priorityF", "projectF", "reporterF", "resolutionF", "resolutiondate", "statusF", "summary", "updated"}),
#"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded fields", "assigneeF", {"key"}, {"assignee"}),
#"Expanded creator" = Table.ExpandRecordColumn(#"Expanded assignee", "creatorF", {"key"}, {"creator"}),
#"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded creator", "issuetypeF", {"name"}, {"issuetype"}),
#"Expanded priority" = Table.ExpandRecordColumn(#"Expanded issuetype", "priorityF", {"name"}, {"priority"}),
#"Expanded project" = Table.ExpandRecordColumn(#"Expanded priority", "projectF", {"key"}, {"project"}),
#"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded project", "reporterF", {"key"}, {"reporter"}),
#"Expanded resolution" = Table.ExpandRecordColumn(#"Expanded reporter", "resolutionF", {"name"}, {"resolution"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded resolution", "statusF", {"name"}, {"status"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"created", type datetimezone}, {"resolutiondate", type datetimezone}, {"updated", type datetimezone}}),
#"Expanded parentF" = Table.ExpandRecordColumn(#"Changed Type", "parentF", {"key"}, {"parent"})
in
#"Expanded parentF"
*****************************************************************************************
