Connecting Jira to Power BI using the Web API

[Mike282]

Hi all,

I've recently been using Jira to manage my workload, utilising user stories, epics and sprints. I've been looking for a method to extract my user story and task data from a specific project in Jira and was looking at using Jira's web API to do the connection. I read another thread where someone has posted a M script code which utilises Jira's web API and I'm having some issues connecting with it (link to thread below).

https://community.powerbi.com/t5/Desktop/Jira-and-Power-BI/td-p/393785/page/2

The above code required authenticating, after reading Jira's Authentication document I created another table that encoded the API token from Jira in Base 64.

Figure 1. Created a table which held the Base 64 encoded username and API token

I then copied the below M script into a new table. Note I added the authorization in the header. Note that in the header I've referenced the encoded Credentials table.

let 
    BaseUrl = Web.Contents("https://companyname.atlassian.net/rest/api/3/search?jql=project in ('TEST')", [Headers=[Authorization="Basic " & Credentials]]),

    JiraIDPerPage = 1000,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetJiraIDCount = () =>
        let Url   = BaseUrl & "&maxResults=0",
            Json  = GetJson(Url),
            Count = Json[#"total"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            Value = Json[#"issues"]
        in  Value,
 
    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
    PageCount   = Number.RoundUp(JiraIDCount / JiraIDPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    JiraID    = List.Union(Pages),
    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})
in
    #"Expanded Column1"

When I loaded the query I'm getting this error:

Figure 2. Error that I'm getting

Not sure what I'm doing wrong here. I quite like this script as it seems to account for the fact that the API only seems to retrieve a max of 50-100 issues and relies on pagination to retrieve additional records.

Now I've then tried using the web data source and adding in the credentials in the header like the below script:

let
    Source = Json.Document(Web.Contents("https://companyname.atlassian.net/rest/api/3/search?jql=project in ('TEST')", [Headers=[Authorization="Basic " & Credentials]])),
    issues = Source[issues],
    #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", 
in
    #"Expanded Column1"

This seems to work but the returend value in a nested JSON format seems only allow a Maxresult of 50 records.

Figure 3. Returned a max of 50. I've got 26 records so I'm getting close

I prefer to use the first code provided in the other thread but not sure how to get it to work. Any help would be greatly appreciated.

[marineded]

I used the script that you gave us.
I created the table for the credentials but I have this error... don't find why !

Could someone knows how to do it?

Thank you

[Jeanxyz]

I want to use the script to import Jira data into Power BI. How can I input my credentials in the script? I assume I need to update the following code? Which password should I use, my windows password or API token?

[HKINANI]

You can try : https://[YourJiraServer]/rest/api/2/search?maxResults=-1

Hope it helps

[v-shex-msft · Accepted solution]

Hi @Mike282 ,

It seems like you are trying to stored web connector result in BASEURL parameters. (obviously, it require to stored your rest API link instead of web.contents result)

let 
    BaseUrl = "https://companyname.atlassian.net/rest/api/3/search?jql=project in ('TEST')",
    JiraIDPerPage = 1000,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url,[Headers=[Authorization="Basic " & Credentials]]),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetJiraIDCount = () =>
        let Url   = BaseUrl & "&maxResults=0",
            Json  = GetJson(Url),
            Count = Json[#"total"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            Value = Json[#"issues"]
        in  Value,
 
    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
    PageCount   = Number.RoundUp(JiraIDCount / JiraIDPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    JiraID    = List.Union(Pages),
    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})
in
    #"Expanded Column1"

BTW, you can take a look at following link about getting more than 50 results from Jira rest API:

PowerBI content pack only pulling 50 rows from API 

Regards,
Xiaoxin Sheng

[julio1907]

Hello @v-shex-msft ,

I used your code to pull data from my Jira project and it worked like a charm, so thank you.

My question is, is there a way to modify this code so that I can upload this report to Power BI Service and setup a scheduled refresh?

Power BI does not currently allow refreshing of "Dynamic Data Sources" but I know there's workarounds.  I was wondering if you could help.

Thanks in advance.

[Kelly_Zhu]

Hi Xiaoxin,

When I use this way, I always fail to get data.

Error Message is "Expression.Error: Access to the resource is forbidden." 

How can I fix this?Thank you in advance.

Regards,

Kelly

[pablobarrachina]

Hi Kelly_Zhu,

I think you get this message due you are using the basic authentication with password. It seems that JIRA is not allowing basic authentication with password any more...

Link to deprecation notice: https://developer.atlassian.com/cloud/jira/platform/deprecation-notice-basic-auth-and-cookie-based-a... 

Now, you can use an API key (https://confluence.atlassian.com/cloud/api-tokens-938839638.html ) instead of the password. You must encode base 64 email:APIKey.

Regards!

Pablo Barrachina

[trangvo]

Hello @pablobarrachina,

Could I do the same way for JIRA server? I tried but got the error : 

Expression.Error: The name 'Credentials' wasn't recognized. Make sure it's spelled correctly.

Any suggestion please.

Thank you in advance. 

[Mike282]

Any help on this would be greatly appreciated. I'm unfamiliar with how I'd handle paging with the returned JSON. At the moment I only have 26 issues but I'd definitely go over the 50 issue mark and at that point I need to find a way to get results in the next page.

[antonstorozhuk]

Hi @Mike282 

Would you consider trying alternative approach? We've built an app for this: Power BI Connector for Jira https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=cloud&tab=overvie... it's available for Jira Cloud / Server / Data Center.

If you're a small team then it's always free for you. Also, you may DM me in case you need any assistance, we will be happy to help.

Best regards,

Anton

[Jeanxyz]

I tried the paid connector, but the issue is the connector is based on user license, we are a company of 100 users, but for some reason, the Jira cloud license is for 3000 users. As a result, we need to pay for a connector of 3000 users. That becomes very expensive. Or did I miss something about your pricing policy?