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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get Data from JIRA API and import in Power BI with Pagination

The Best approach is to use the code below to get all data using a JQL ( Please modify the query on basis of what data u want to fetch from JIRA )

 

let 
    BaseUrl = "https://jira.company.com/rest/api/2/search?jql=project in ('ABC') AND issuetype in subTaskIssueTypes()",

    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"

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Yes Sharing it for other people benefit.

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

Hi Amkhullar,

 

First of all thank you for the query/solution. This works like charm however would you be able help on below -

 

1. I have close to 400 records however getting only 100 records. When I changed JIRAIDPerPage to 100.. it is giving me below error:

Preview.Error: The type of the current preview value is too complex to display.
Details:

2. For the 100 records the fields column shows the column names "CustomFiled XXXX" is it possible to fetch the names of those columns. 

 

Thanks,

filiparibeiro
Helper III
Helper III

Would you be able to explain how I can do that? I don't understand which steps I should take

filiparibeiro
Helper III
Helper III

Hello.

Thank you for your share. 

I've used this approach but as it's considered a dynamic source I can not schedule refresh in pbi service. Were you able to? Do you know how to workaround it?

Thanks. 

Anonymous
Not applicable

U need to map the dataset to power bi gateway for sync and schedule it there, for that first install the power bi gateway ona. Stand-alone machine and then publish the report , then go in the settings of data source and map it to the power bi gateway

Anonymous
Not applicable

Hi Amkhullar, Ive sent you message. I am new to this and also sorry for earlier typo.

As per this link, https://learn.microsoft.com/en-us/power-bi/connect-data/power-bi-data-sources PowerBI doesn't support JIRA hence they are not able to enable schedule refresh. Could you guide me on how we can proceed to do this PowerBI gateway.

 

As I am new on this tech for now so wanted to know how pagination without writing a code logic outside any server can be done.

Anonymous
Not applicable

Doesnt matter we can always create a dataset using API response - script published above to achieve the same.

Hello @Anonymous 

I’d like to propose and alternative solution for you, you might want to try out our app - 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.

We have a handy User/Admin Guide https://aserve.atlassian.net/wiki/spaces/PBCFJC/overview and fast/responsive support team at support@alpha-serve.com. Please let us know if you need any assistance.

Cheers!

Anton

===
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous ,

Is this a sharing?

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Yes Sharing it for other people benefit.

Hi I used your function and it works well on the PowerBi desktop. But please can help with this for my issues facing.

 

1. the load tome is huge any way can make ut sync

2. The dataset refresh after publishing doesn't work. Gives this error.

codelover87_0-1667639613310.png

 

 

Anonymous
Not applicable

U need to reduce ur query dataset , use date field to filter the dataset e.g created_date > 01-01-2022

Use power bi gateway to deploy ur source service and sync the data.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.