PowerBI and Jira integration guide

4 min readMay 7, 2020

After weeks of looking online on how to create reports with popular reporting app PowerBI and JIRA, I finally found a way that does not require any add-ons and it is pretty straightforward.

This guide assumes you have PowerBI Pro and your Jira password available.

Create a filter on Jira

Start by going on your Jira page Issues -> Search for issues enter JQL query into the field and save the query. Once that is done go Issues -> your saved filter and then in the URL you will find the filter ID, it should be something like filter=1111, take a note of it, you will need in the next step.

Add new source in PowerBI

Now in PowerBI click Get Data -> Web and enter URI using a structure as defined on JIRA REST API guide where jira.example.com should be changed to your Jira URL and filter=11111 to your filter ID

https://jira.example.com/rest/api/2/search?jql=filter=111111

You can test if the URL is right by pasting it into a browser and checking if you receive an answer from the server. You should be able to see the issues in JSON format.

Entering credentials

Once the PowerBi connects to the Jira, it will prompt you to enter your credentials. Select Basic -> enter your credentials and click connect.

Pagination

In this part, we will create a function that will load data multiple times as one load only allows 50 tickets loaded per one time.

After entering credentials a Power Query Editor will load.

STEP 1: Rename your query to StartAt

STEP 2: Expand list near issues

STEP 3: Convert it to a table, select delimiter None and How to handle extra columns as Show as Errors

STEP 4: Expand the table to a key and fields

STEP 5: Make this query a function by going to an advanced editor and pasting this at the beginning of the code (start as text) => and adding ,[Query=[startAt=start]] after your Jira query link. Your full code should look like this now

(start as text) =>
let
    Source = Json.Document(Web.Contents("https://jira.example.com/rest/api/2/search?jql=filter=111111",[Query=[startAt=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"

STEP 6: Add a new source by going New Source -> Web -> paste your link to the Jira query.

STEP 7: Go to advance editor of the new query and paste add the following after the Source step (don’t forget a comma after the Source line). Your code will look like this :

let
    Source = Json.Document(Web.Contents("https://jira.example.com/rest/api/2/search?jql=filter=111111")),
total1 = List.Numbers(0,Number.RoundUp(Source[total]/50),50),
    #"Converted to Table" = Table.FromList(total1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}})
in
    #"Changed Type"

STEP 8: Invoke StartAt function by going Add Column -> Invoke Custom Function. Call the New Column name Data and select Function query as StartAt and start as Column1