From Power BI, how do you retrieve all the issues related to a Jira project?
To follow along with this post, you’ll need the following:
Power Query – Excel or Power BI
Jira API key
Powershell, Python, or a tool to base64 encode a string
To get a Jira API key, navigate to your Jira homepage, click your account icon (top right), and select Manage account. The shortcut to the account page is here: https://id.atlassian.com/manage-profile/security
From the account page, click on the Security header link, then click on Create and manage API tokens.
From the API Tokens page, click on the Create API token button. When the window opens, enter a value in the Label field and click the Create button. From your new API token window, copy the API token value. YOU WILL NEED this later in this demo. 
When accessing the Jira API, basic auth is used, and for this example, we need to base64 encode the login credentials. You can use your favorite programming/scripting language or a website like Base64Encode.org to encode the credentials. In a production scenario, I do NOT suggest using a public website to encode/decode anything. This is just a demo; my API key will be revoked once I finish writing this.
Example string of what needs to be encoded:
Your Jira login email address + colon + API token
youEmail@example:APItoken
Example:
ian@example.com:ETETT3xFfGF009ETR_3bbA7Gk_ZLzPCAocvKcAvSzKe5-ysU_8fGwBxuRSsyx7efUcvaTACOSh3sgJWGtictvqGBF0yCy3ZzzKb39_gHBgYxnxjBURRITO3ofEWea_Wf4P9XWWmiNACHOWxUUA7O9cwFhH9WO6hq4-yAwEnbQUESOc=AEAA3875
Encoded value:
aWFuQGV4YW1wbGUuY29tOkVURVRUM3hGZkdGMDA5RVRSXzNiYkE3R2tfWkx6UENBb2N2S2NBdlN6S2U1LXlzVV84Zkd3Qnh1UlNzeXg3ZWZVY3ZhVEFDT1NoM3NnSldHdGljdHZxR0JGMHlDeTNaenpLYjM5X2dIQmdZeG54akJVUlJJVE8zb2ZFV2VhX1dmNFA5WFdXbWlOQUNIT1d4VVVBN085Y3dGaEg5V082aHE0LXlBd0VuYlFVRVNPYz1BRUFBMzg3NQ== Next, we need to open Power Query to capture the Jira data.
Excel: Data tab –> Get Data –> Launch Power Query editor
Power BI: Home tab –>Transform data –> Transform data
In Power Query, select New Source, then select Blank Query. From the toolbar, click Advanced Editor. Delete everything in the Query window. Below is the M code for connecting to Jira and parsing the API response. You will want to update the code to replace the Url value, the Authorization value, and jql=”project = YourProject” values! You can get the URL and YourProject value from your Jira project homepage.
let
Source = (startAt as number) as table =>
let
Url = "https://YOUR-COMPANY.atlassian.net/rest/api/3/search",
WebContents = Web.Contents(Url,
[
Headers = [#"Authorization"="Basic aWFuQGV4YW1wbGUuY29tOkVURVRUM3hGZkdGMDA5RVRSXzNiYkE3R2tfWkx6UENBb2N2S2NBdlN6S2U1LXlzVV84Zkd3Qnh1UlNzeXg3ZWZVY3ZhVEFDT1NoM3NnSldHdGljdHZxR0JGMHlDeTNaenpLYjM5X2dIQmdZeG54akJVUlJJVE8zb2ZFV2VhX1dmNFA5WFdXbWlOQUNIT1d4VVVBN085Y3dGaEg5V082aHE0LXlBd0VuYlFVRVNPYz1BRUFBMzg3NQ==", #"Content-Type"="application/json"],
Query=[startAt=Text.From(startAt), jql="project = YourProject"]
]
),
ParsedJson = Json.Document(WebContents),
Issues = ParsedJson[issues],
TableFromJson = Table.FromList(Issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Total = ParsedJson[total],
NextStartAt = startAt + List.Count(Issues)
in
if NextStartAt < Total then
Table.Combine({TableFromJson, @Source(NextStartAt)})
else
TableFromJson,
InitialCall = Source(0),
#"Expanded Column1" = Table.ExpandRecordColumn(InitialCall, "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
#"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"statuscategorychangedate", "customfield_10990", "fixVersions", "customfield_10991", "customfield_10992", "resolution", "customfield_10751", "customfield_10104", "customfield_10984", "customfield_10985", "customfield_10986", "customfield_10987", "customfield_10989", "lastViewed", "customfield_10462", "priority", "customfield_10980", "customfield_10100", "customfield_10463", "customfield_10101", "customfield_10465", "customfield_10102", "customfield_10103", "labels", "customfield_10973", "customfield_10974", "customfield_10732", "customfield_10733", "customfield_10975", "customfield_10976", "aggregatetimeoriginalestimate", "customfield_10977", "timeestimate", "customfield_10978", "versions", "customfield_10979", "issuelinks", "assignee", "status", "components", "customfield_10450", "customfield_10451", "customfield_10452", "customfield_10453", "customfield_10454", "customfield_10841", "customfield_10445", "customfield_10842", "customfield_10843", "customfield_10447", "customfield_10448", "customfield_10844", "customfield_10569", "customfield_10449", "aggregatetimeestimate", "customfield_10728", "creator", "subtasks", "reporter", "aggregateprogress", "customfield_10200", "customfield_10676", "customfield_10830", "customfield_10831", "customfield_10710", "customfield_10438", "progress", "votes", "issuetype", "timespent", "project", "aggregatetimespent", "customfield_11003", "customfield_10423", "customfield_10544", "customfield_10545", "customfield_10424", "customfield_10942", "customfield_10546", "customfield_10821", "customfield_10822", "customfield_10943", "customfield_10427", "customfield_10823", "customfield_10944", "customfield_10945", "customfield_10428", "resolutiondate", "customfield_10946", "customfield_10827", "customfield_10948", "workratio", "customfield_10949", "customfield_10828", "customfield_10829", "watches", "created", "customfield_10541", "customfield_10543", "customfield_10533", "customfield_10654", "customfield_10655", "customfield_10930", "customfield_10931", "customfield_10414", "customfield_10656", "customfield_10657", "customfield_10415", "customfield_10811", "customfield_10933", "customfield_10537", "customfield_10812", "customfield_10416", "customfield_10658", "customfield_10659", "customfield_10538", "customfield_10417", "customfield_10935", "customfield_10418", "customfield_10419", "customfield_10818", "customfield_10819", "updated", "timeoriginalestimate", "customfield_10492", "description", "customfield_10893", "customfield_10531", "customfield_10895", "customfield_10653", "customfield_10006", "customfield_10007", "security", "customfield_10801", "customfield_10802", "customfield_10805", "customfield_10806", "customfield_10807", "customfield_10928", "customfield_10809", "summary", "customfield_10000", "customfield_10001", "customfield_10002", "customfield_10640", "customfield_10641", "customfield_10642", "customfield_10753", "customfield_10478", "customfield_10633", "customfield_10513", "environment", "customfield_10637", "customfield_10516", "customfield_10879", "customfield_10517", "duedate", "customfield_10639"}, {"Column1.fields.statuscategorychangedate", "Column1.fields.customfield_10990", "Column1.fields.fixVersions", "Column1.fields.customfield_10991", "Column1.fields.customfield_10992", "Column1.fields.resolution", "Column1.fields.customfield_10751", "Column1.fields.customfield_10104", "Column1.fields.customfield_10984", "Column1.fields.customfield_10985", "Column1.fields.customfield_10986", "Column1.fields.customfield_10987", "Column1.fields.customfield_10989", "Column1.fields.lastViewed", "Column1.fields.customfield_10462", "Column1.fields.priority", "Column1.fields.customfield_10980", "Column1.fields.customfield_10100", "Column1.fields.customfield_10463", "Column1.fields.customfield_10101", "Column1.fields.customfield_10465", "Column1.fields.customfield_10102", "Column1.fields.customfield_10103", "Column1.fields.labels", "Column1.fields.customfield_10973", "Column1.fields.customfield_10974", "Column1.fields.customfield_10732", "Column1.fields.customfield_10733", "Column1.fields.customfield_10975", "Column1.fields.customfield_10976", "Column1.fields.aggregatetimeoriginalestimate", "Column1.fields.customfield_10977", "Column1.fields.timeestimate", "Column1.fields.customfield_10978", "Column1.fields.versions", "Column1.fields.customfield_10979", "Column1.fields.issuelinks", "Column1.fields.assignee", "Column1.fields.status", "Column1.fields.components", "Column1.fields.customfield_10450", "Column1.fields.customfield_10451", "Column1.fields.customfield_10452", "Column1.fields.customfield_10453", "Column1.fields.customfield_10454", "Column1.fields.customfield_10841", "Column1.fields.customfield_10445", "Column1.fields.customfield_10842", "Column1.fields.customfield_10843", "Column1.fields.customfield_10447", "Column1.fields.customfield_10448", "Column1.fields.customfield_10844", "Column1.fields.customfield_10569", "Column1.fields.customfield_10449", "Column1.fields.aggregatetimeestimate", "Column1.fields.customfield_10728", "Column1.fields.creator", "Column1.fields.subtasks", "Column1.fields.reporter", "Column1.fields.aggregateprogress", "Column1.fields.customfield_10200", "Column1.fields.customfield_10676", "Column1.fields.customfield_10830", "Column1.fields.customfield_10831", "Column1.fields.customfield_10710", "Column1.fields.customfield_10438", "Column1.fields.progress", "Column1.fields.votes", "Column1.fields.issuetype", "Column1.fields.timespent", "Column1.fields.project", "Column1.fields.aggregatetimespent", "Column1.fields.customfield_11003", "Column1.fields.customfield_10423", "Column1.fields.customfield_10544", "Column1.fields.customfield_10545", "Column1.fields.customfield_10424", "Column1.fields.customfield_10942", "Column1.fields.customfield_10546", "Column1.fields.customfield_10821", "Column1.fields.customfield_10822", "Column1.fields.customfield_10943", "Column1.fields.customfield_10427", "Column1.fields.customfield_10823", "Column1.fields.customfield_10944", "Column1.fields.customfield_10945", "Column1.fields.customfield_10428", "Column1.fields.resolutiondate", "Column1.fields.customfield_10946", "Column1.fields.customfield_10827", "Column1.fields.customfield_10948", "Column1.fields.workratio", "Column1.fields.customfield_10949", "Column1.fields.customfield_10828", "Column1.fields.customfield_10829", "Column1.fields.watches", "Column1.fields.created", "Column1.fields.customfield_10541", "Column1.fields.customfield_10543", "Column1.fields.customfield_10533", "Column1.fields.customfield_10654", "Column1.fields.customfield_10655", "Column1.fields.customfield_10930", "Column1.fields.customfield_10931", "Column1.fields.customfield_10414", "Column1.fields.customfield_10656", "Column1.fields.customfield_10657", "Column1.fields.customfield_10415", "Column1.fields.customfield_10811", "Column1.fields.customfield_10933", "Column1.fields.customfield_10537", "Column1.fields.customfield_10812", "Column1.fields.customfield_10416", "Column1.fields.customfield_10658", "Column1.fields.customfield_10659", "Column1.fields.customfield_10538", "Column1.fields.customfield_10417", "Column1.fields.customfield_10935", "Column1.fields.customfield_10418", "Column1.fields.customfield_10419", "Column1.fields.customfield_10818", "Column1.fields.customfield_10819", "Column1.fields.updated", "Column1.fields.timeoriginalestimate", "Column1.fields.customfield_10492", "Column1.fields.description", "Column1.fields.customfield_10893", "Column1.fields.customfield_10531", "Column1.fields.customfield_10895", "Column1.fields.customfield_10653", "Column1.fields.customfield_10006", "Column1.fields.customfield_10007", "Column1.fields.security", "Column1.fields.customfield_10801", "Column1.fields.customfield_10802", "Column1.fields.customfield_10805", "Column1.fields.customfield_10806", "Column1.fields.customfield_10807", "Column1.fields.customfield_10928", "Column1.fields.customfield_10809", "Column1.fields.summary", "Column1.fields.customfield_10000", "Column1.fields.customfield_10001", "Column1.fields.customfield_10002", "Column1.fields.customfield_10640", "Column1.fields.customfield_10641", "Column1.fields.customfield_10642", "Column1.fields.customfield_10753", "Column1.fields.customfield_10478", "Column1.fields.customfield_10633", "Column1.fields.customfield_10513", "Column1.fields.environment", "Column1.fields.customfield_10637", "Column1.fields.customfield_10516", "Column1.fields.customfield_10879", "Column1.fields.customfield_10517", "Column1.fields.duedate", "Column1.fields.customfield_10639"}),
#"Expanded Column1.fields.status" = Table.ExpandRecordColumn(#"Expanded Column1.fields", "Column1.fields.status", {"name", "id"}, {"Column1.fields.status.name", "Column1.fields.status.id"}),
#"Expanded Column1.fields.assignee" = Table.ExpandRecordColumn(#"Expanded Column1.fields.status", "Column1.fields.assignee", {"accountId", "emailAddress", "displayName"}, {"Column1.fields.assignee.accountId", "Column1.fields.assignee.emailAddress", "Column1.fields.assignee.displayName"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.fields.assignee",{{"Column1.fields.updated", type datetimezone}}),
#"Expanded Column1.fields.issuetype" = Table.ExpandRecordColumn(#"Changed Type", "Column1.fields.issuetype", {"id", "name"}, {"Column1.fields.issuetype.id", "Column1.fields.issuetype.name"}),
#"Expanded Column1.fields.reporter" = Table.ExpandRecordColumn(#"Expanded Column1.fields.issuetype", "Column1.fields.reporter", {"accountId", "emailAddress", "displayName"}, {"Column1.fields.reporter.accountId", "Column1.fields.reporter.emailAddress", "Column1.fields.reporter.displayName"}),
#"Expanded Column1.fields.priority" = Table.ExpandRecordColumn(#"Expanded Column1.fields.reporter", "Column1.fields.priority", {"name", "id"}, {"Column1.fields.priority.name", "Column1.fields.priority.id"})
in
#"Expanded Column1.fields.priority"
After pasting the M code in the Query window, click Done. When the Access Web content window opens, select Anonymous!
Once connected, Power Query will hit the Jira API and loop through the response, building a rich dataset. In the right column, my M code added a few extra Applied Steps. You can remove these or add steps to clean up the returned dataset.
When you are ready, click the Close & Apply button. The next couple of screenshots are focused on Power BI, but the same data will be available in Excel. Here, you can see the total number of items returned by the calls to the Jira API from Power Query.
One big thing to note: if you don’t need to pull the entire dataset down from Jira, try creating sample JQL queries and copying the URL value to Power Query. I grabbed this screenshot from the Issues page of my project. Note that adding more filters will change the URL to reflect the update. The URL contains the JQL query string.
You’d want to modify This section of Power Query M code. 