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

[Anonymous]

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"

[Anonymous]

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]

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

[filiparibeiro]

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]

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]

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]

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

[antonstorozhuk]

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]

Hi @Anonymous ,

Is this a sharing?

Best Regards,

Cherry

[Anonymous · Accepted solution]

Yes Sharing it for other people benefit.

[codelover87]

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.

[Anonymous]

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

[Syndicate_Admin]

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