Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mike282
Helper III
Helper III

Connecting Jira to Power BI using the Web API

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 tokenFigure 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 gettingFigure 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 closeFigure 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.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

11 REPLIES 11
marineded
Regular Visitor

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

Screenshot 2023-04-24 at 16.15.25.png

 
Jeanxyz
Post Prodigy
Post Prodigy

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
Regular Visitor

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

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

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

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
Helper III
Helper III

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.

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

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors