JIRA & PowerBI

[Anonymous]

Hey everyone,

I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I use, when PBI asks me to enter the URL for JIRA. 

https://jira.rd.abcpharma.net/secure/RapidBoard.jspa?rapidView=5048&projectKey=SSCL

This is the link to the Kanban board that is in JIRA, and I'd like to bring it to Power BI. Any help would be much appreciated! 🙂

TIA 🙂

[ltopchyi]

Hi,

You can also use Power BI Connector for Jira to set up Jira to Power BI integration.

Liubov

[Anonymous]

this thread and code was a savior, and a bit more work got it working completely

 others items that allowed the completion of the process for me : 

- the auth key needed to be jirausername@domain.com:JIRA_API_KEY

- changing the last bit of the previous code to remove the data1 = data{0} allowed all data to be retreived.

- converting to table and expanding the fields resulted in all data.

i then removed all the fields i didnt need and expanded some to data.

the code below has a fake auth key

let

    Source = Json.Document(Web.Contents("https://siteid.atlassian.net/rest/api/2/search?jql:project=RPSD&maxResults=300", [Headers=[ContentType="application/json", Authorization="Basic cGF1bC5234sdfsdf2345dfgc2dRV0tiQzM4Qg=="]])),

    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),
    #"total" = #"Removed Other Columns"{0}[total],
    #"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),
    #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "https://siteid.atlassian.net/rest/api/2/search?jql:project=RPSD&maxResults=100&startAt=" & Text.From([startAt])),
   data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_, [Headers=[ContentType="application/json", Authorization="Basic cGF1bC5234sdfsdf2345dfgc2dRV0tiQzM4Qg=="]]))),
    #"Converted to Table2" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Column1 = #"Converted to Table2"[Column1],
    #"Converted to Table3" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table3", "Column1", {"expand", "startAt", "maxResults", "total", "issues"}, {"expand", "startAt", "maxResults", "total", "issues"}),
    #"Expanded issues" = Table.ExpandListColumn(#"Expanded Column1", "issues"),
    #"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"expand.1", "id", "self", "key", "fields"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded issues1", "fields", {"statuscategorychangedate", "issuetype", "timespent", "project", "customfield_11000", "customfield_13465", "fixVersions", "customfield_13464", "customfield_11200", "aggregatetimespent", "customfield_13467", "customfield_13466", "resolution", "customfield_13468", "customfield_12800", "customfield_10501", "customfield_10504", "resolutiondate", "workratio", "watches", "lastViewed", "created", "customfield_13410", "priority", "customfield_10100", "customfield_13412", "customfield_10101", "customfield_13411", "customfield_10102", "customfield_13458", "customfield_10025", "customfield_13457", "labels", "customfield_11303", "customfield_13407", "customfield_11306", "customfield_13406", "aggregatetimeoriginalestimate", "timeestimate", "versions", "issuelinks", "assignee", "updated", "status", "components", "timeoriginalestimate", "customfield_13441", "customfield_13440", "description", "customfield_13443", "customfield_10010", "customfield_13442", "customfield_10011", "customfield_11100", "customfield_13401", "customfield_13400", "customfield_11500", "customfield_13402", "customfield_10006", "customfield_10600", "customfield_12901", "customfield_10007", "security", "customfield_10601", "customfield_12900", "customfield_10008", "customfield_12903", "aggregatetimeestimate", "customfield_10009", "customfield_12902", "customfield_12905", "customfield_12904", "summary", "creator", "subtasks", "customfield_13474", "reporter", "customfield_10000", "customfield_13475", "aggregateprogress", "customfield_10001", "customfield_10002", "environment", "duedate", "progress", "votes"}, {"statuscategorychangedate", "issuetype", "timespent", "project", "customfield_11000", "customfield_13465", "fixVersions", "customfield_13464", "customfield_11200", "aggregatetimespent", "customfield_13467", "customfield_13466", "resolution", "customfield_13468", "customfield_12800", "customfield_10501", "customfield_10504", "resolutiondate", "workratio", "watches", "lastViewed", "created", "customfield_13410", "priority", "customfield_10100", "customfield_13412", "customfield_10101", "customfield_13411", "customfield_10102", "customfield_13458", "customfield_10025", "customfield_13457", "labels", "customfield_11303", "customfield_13407", "customfield_11306", "customfield_13406", "aggregatetimeoriginalestimate", "timeestimate", "versions", "issuelinks", "assignee", "updated", "status", "components", "timeoriginalestimate", "customfield_13441", "customfield_13440", "description", "customfield_13443", "customfield_10010", "customfield_13442", "customfield_10011", "customfield_11100", "customfield_13401", "customfield_13400", "customfield_11500", "customfield_13402", "customfield_10006", "customfield_10600", "customfield_12901", "customfield_10007", "security", "customfield_10601", "customfield_12900", "customfield_10008", "customfield_12903", "aggregatetimeestimate", "customfield_10009", "customfield_12902", "customfield_12905", "customfield_12904", "summary", "creator", "subtasks", "customfield_13474", "reporter", "customfield_10000", "customfield_13475", "aggregateprogress", "customfield_10001", "customfield_10002", "environment", "duedate", "progress", "votes"}),
    

    #"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded fields", "issuetype", {"name"}, {"issuetype.name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded issuetype",{"expand", "startAt", "maxResults", "total", "expand.1"}),
    #"Expanded project1" = Table.ExpandRecordColumn(#"Removed Columns", "project", {"name"}, {"project.name"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded project1",{{"customfield_13465", "Platform Component"}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"customfield_13464", "Client Organisation"}}),
    #"Expanded Client Organisation" = Table.ExpandRecordColumn(#"Renamed Columns2", "Client Organisation", {"value"}, {"Client Organisation.value"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Expanded Client Organisation",{{"customfield_13467", "Client Reference"}}),
    #"Expanded resolution" = Table.ExpandRecordColumn(#"Renamed Columns3", "resolution", {"name"}, {"resolution.name"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Expanded resolution",{{"customfield_12800", "VSTS ID"}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Renamed Columns4",{{"customfield_13410", "Cab Approvers"}}),
    #"Expanded priority" = Table.ExpandRecordColumn(#"Renamed Columns5", "priority", {"name", "id"}, {"priority.name", "priority.id"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Expanded priority",{{"customfield_13411", "Time to Close"}, {"customfield_10102", "Time to Respond"}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Renamed Columns6",{{"customfield_13458", "Time to Investigate"}}),
    #"Renamed Columns8" = Table.RenameColumns(#"Renamed Columns7",{{"customfield_10025", "Time To Resolve"}}),
    #"Expanded Time To Resolve" = Table.ExpandRecordColumn(#"Renamed Columns8", "Time To Resolve", {"completedCycles"}, {"Time To Resolve.completedCycles"}),
    #"Expanded customfield_13457" = Table.ExpandRecordColumn(#"Expanded Time To Resolve", "customfield_13457", {"value"}, {"value"}),
    #"Renamed Columns9" = Table.RenameColumns(#"Expanded customfield_13457",{{"value", "Environment"}}),
    #"Expanded assignee" = Table.ExpandRecordColumn(#"Renamed Columns9", "assignee", {"displayName"}, {"assignee.displayName"}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Expanded assignee", "status", {"name"}, {"status.name"}),
    #"Renamed Columns10" = Table.RenameColumns(#"Expanded status",{{"customfield_13401", "Urgency"}}),
    #"Renamed Columns11" = Table.RenameColumns(#"Renamed Columns10",{{"customfield_12901", "Change Type"}}),
    #"Renamed Columns12" = Table.RenameColumns(#"Renamed Columns11",{{"customfield_12900", "Impact"}}),
    #"Expanded customfield_10008" = Table.ExpandRecordColumn(#"Renamed Columns12", "customfield_10008", {"requestType"}, {"customfield_10008.requestType"}),
    #"Renamed Columns13" = Table.RenameColumns(#"Expanded customfield_10008",{{"customfield_10008.requestType", "Customer requestType"}}),
    #"Expanded Customer requestType" = Table.ExpandRecordColumn(#"Renamed Columns13", "Customer requestType", {"name"}, {"Customer requestType.name"}),
    #"Renamed Columns14" = Table.RenameColumns(#"Expanded Customer requestType",{{"customfield_12903", "Change Reason"}}),
    #"Renamed Columns15" = Table.RenameColumns(#"Renamed Columns14",{{"customfield_12902", "Change Risk"}, {"customfield_12905", "Change Complete Date"}}),
    #"Renamed Columns16" = Table.RenameColumns(#"Renamed Columns15",{{"customfield_12904", "Change Start date"}}),
    #"Expanded creator" = Table.ExpandRecordColumn(#"Renamed Columns16", "creator", {"displayName"}, {"creator.displayName"}),
    #"Renamed Columns17" = Table.RenameColumns(#"Expanded creator",{{"customfield_13474", "Domain Of Reporter"}}),
    #"Expanded reporter" = Table.ExpandRecordColumn(#"Renamed Columns17", "reporter", {"emailAddress"}, {"reporter.emailAddress"}),
    #"Renamed Columns18" = Table.RenameColumns(#"Expanded reporter",{{"customfield_10000", "Date of First Response"}}),
    #"Renamed Columns19" = Table.RenameColumns(#"Renamed Columns18",{{"customfield_13475", "VSTS Status"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns19",{"customfield_11000", "customfield_11200", "customfield_13466", "customfield_13468", "customfield_10501", "customfield_10504", "customfield_10100", "customfield_13412", "customfield_10101", "customfield_11303", "customfield_13407", "customfield_11306", "customfield_13406", "aggregatetimeoriginalestimate", "timeestimate", "versions", "timeoriginalestimate", "customfield_13441", "customfield_13440", "customfield_13443", "customfield_10010", "customfield_13442", "customfield_10011", "customfield_11100", "customfield_13400", "customfield_11500", "customfield_13402", "customfield_10006", "customfield_10600", "customfield_10007", "customfield_10601", "customfield_10009", "aggregateprogress", "customfield_10001", "customfield_10002", "progress", "votes", "fixVersions", "watches", "Time to Investigate", "Time To Resolve.completedCycles", "issuelinks", "labels", "components", "subtasks"})
in
    #"Removed Columns1"

[Anonymous]

Hi,

@Anonymous  Thanks for sharing this, When i tried using your code basically just by updating the JIRA URL of my need. 

I got the following error : 

Expression.Error: The 'ContentType' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer

So can you please elaborate a bit more on the authentication details as i am very new to this, on how does the authentication of JIRA should be supplied? 

[Anonymous]

Hi, 

@Anonymous , I obtained my requirement using the experience shared in this link : https://community.atlassian.com/t5/Jira-questions/PowerBI-content-pack-only-pulling-50-rows-from-API/qaq-p/888050  .

So my above open query can be ignored. 

Thanks,

Govind

[TiagoMachado]

Hi,

@Eric_Zhangis correct - in order to get specific data from JIRA, we must use the JIRA REST API.

The idea of this reply is to give a more step-by-step solution for those not so familiar with M, JIRA REST API or both.

Here I'm going to detail how I was able to retrieve data from JIRA - this was my first contact with PowerBI and worked, but I'm not happy with the performance - if anyone has any suggestion on improvements, just let me know !! (Thanks in advance)

Enough introduction done, let's get our hands on data !! So we must start clicking on "Edit Queries" in order to be able to edit the query internals. Later on it will make more sense why we are doing so (instead of clicking on Get Data -> Web)

Now we must:

1. Create a new blank query (New Source -> Blank Query)
2. Open the Advanced Editor (Right click on your new query -> Advanced Editor)

Now for those unfamiliar with M, we are going to just make a web query and parsing the results as a JSON format :

let
    Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser()")),
in
   Source

This query will return all the items assigned to the current user. Please remember to replace "xxxxxx.atlassian.net" with your specific domain.

An improvement I made here is to add a parameter named "Query" in order to make it easier to customize the data I want from JIRA.

And that's it. Power BI will require your JIRA login and password, query your JIRA and present your data.

No.... Unfortunately, it is not that easy...

It is true that some data will be presented, but not necessarily all data.

Let me explain - for those unfamiliar with JIRA REST - the JSON result will have 3 important parameters:

• total - The total number of records of your query
• maxResults - The max number of records of this page
• startAt - The first item showed on this page

So, if total is less or equal than maxResults, all your data is being presented. But, if your query contains more records than maxResults, we need to query JIRA a couple more times to get everything.

Remark: maxResults cannot be larger than 100 at this moment

So, we are going to prepare all the URL's, query them in JIRA and return a single table with all the data. In order to do it:

let

    // The same query as before
    Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/search?jql=assignee=currentUser())),

    // Converting data from List to Table, so
    #"Converted to Table" = Record.ToTable(Source),

    // we will be able to transpose it
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),

    // and make the field names the column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),

    // Now we are going to assign the correct data types for each column
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),

    // And keep only the total column
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),

    // Now we are going to get the first (and only) record and assign it to a variable named #"total"
    #"total" = #"Removed Other Columns"{0}[total],

    // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
    #"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),

    // Converting the startAt list into Table - not sure if this is really necessary
    #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // and renaming its single column
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),

    // Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "https://xxxxxx.atlassian.net/rest/api/2/search?maxResults=100&jql=assignee=currentUser()&startAt=" & Text.From([startAt])),

    // And finally retrieve them all
    data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_)))
in
    data

Now, no matter how many results you have on your query, all the data will be retrieved. Later you will need to expand the issues field in order to work with your data, but this will have to be done accordingly to your needs/query/JIRA instance.

As I said earlier, this solution retrieves all JIRA data, but I'm not happy with the performance of it. The few improvements I made were on the first query - where we are going to get the total value, where I filter the fields (fields=id) as I'm not interested on the issue data itself at the moment - and getting only one record (maxResults=1).

I hope this helps anyone who is starting on PowerBI and/or JIRA...

If you liked this answer or if you have any performance improvement suggestion, please, leave a reply.

Regards,

Tiago.

[fredsp]

Hi Tiago and thanks for sharing how to retrieve Jira data from Power BI. When I tried your Powerquery I get an error message saying:

[Anonymous]

Thank you for finally finding a way to do this!

One issue I am having is our JIRA url: https://enterprise-jira.xxxx.com
I'm not sure how that translates since all the data I see says xxxx.atlassian.com

Thanks in advance!

UPDATE: I was able to get it working simply by using the enterprise-jira.company.com!

I just found out that we have over 400k issues and it may take awhile to load...

[axy5093]

Hi @TiagoMachado, Firstly thanks for your query i was able to get all the issues for a specific project with just a little tweaking! But now i realized that the hidden custom fields are not retrieved using REST API, i need to be able to get values for them. Do you know of a workaround to get this data ?

Thanks in Advance,

Aish 

[TiagoMachado]

Hi @axy5093

I answered this question on this thread as well:

In order to get the custom_field "human" names, you need to perform another REST call at:

https://xxxxxx.atlassian.net/rest/api/2/issue/createmeta?expand=projects.issuetypes.fields

(where xxxx is your cloud domain)

And then rename the columns on your Power BI.

If there aren't many customfields, it is faster to hardcode the values (the above REST call takes some time to return data - specially when the number of projects you have on your cloud instance increases)

Some helpful references:

https://community.atlassian.com/t5/Answers-Developer-Questions/Custom-Field-Name-in-Jira-JSON/qaq-p/...

https://developer.atlassian.com/cloud/jira/platform/rest/?_ga=2.106182916.649572337.1531849237-10605...

Please let me know if it was helpful

Regards

[axy5093]

Hi @TiagoA

Thanks thats helpful, my problem was that i wasn't getting all the fields. The issue was that with JIRA REST API search function the default behaviour is to get only navigable fields and i needed all of them, Anyways tweaking the url like below worked for me.

Search documentation

https://xxxxxx.atlassian.net/rest/api/2/search?jql=project=XYZ&fields=*all

Aishwarya 

[rsmccall33]

My apologies for reviving an old thread, but I have implemented the code that@TiagoMacado has provided and it has allowed me to bring in 1000 rows instead of just 50.  However, the expected results is 3839 rows.  Is it possible to get all 3839 rows?  I have another table that has 40k+ rows and 1000 rows doesn't paint an accurate picture.

Edit:  to clarify, the line below returns a total of 3839.  So at this point, this step shows 3839 rows, but when I create a report that shows the total number of records, it = 1000

 #"total" = #"Removed Other Columns"{0}[total],

[savethepennies]

Thanks for the great explanation btw. This was very helpful in getting going. I'm trying to work out how to expand down to the issue fields so we'll see how that goes.

[AlphaBeta]

Can someone help me understand how I would call all users rather than just my own assigned tickets?

[savethepennies]

Change the criteria in the jql section. Whatever change you make should be made to both the Source line and the #"Added Custom" line. If you want all tickets then you can remove the jql section but be careful. You could be waiting awhile depending on your issue count.

[Eric_Zhang · Accepted solution]

---

@Anonymous wrote:

Hey everyone,

I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I use, when PBI asks me to enter the URL for JIRA. 

https://jira.rd.abcpharma.net/secure/RapidBoard.jspa?rapidView=5048&projectKey=SSCL

This is the link to the Kanban board that is in JIRA, and I'd like to bring it to Power BI. Any help would be much appreciated! 🙂

TIA 🙂

---

@Anonymous

When connecting to JIRA, the required URL is like "https://xxxxxx.atlassian.net", while clicking next, it acutally calls the API "https://xxxxxx.atlassian.net/rest/api/2/search".

So simplely speaking, at this moment, Power BI now can only get the data that returnbed by API "https://xxxxxx.atlassian.net/rest/api/2/search", As to your link to other JIRA REST APIs, you can do it by calling REST API in Power BI desktop, this would require REST API and programing skill. Check how to call such similar APIs in a demo Get Data from Twitter API with Power Query.

You can also submit your idea at Power BI Ideas and vote it up.

[Anonymous]

for the solution: do you connect from web service?

when i use web service i can connect to Jira but it creates an app for me; how do i use this solution so i can pull data and create charts in PBI?