Jira API connection and data refresh

[rishtinz]

Hello,

I have JIRA issues management system which gets updated on a daily basis. I used the following solution to read JIRA data through api in Power BI desktop and published it on Power BI service and scheduled a data refreshed. Funny thing is it worked for a month or two but just recently it start throwing the following error. 

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

It does not refresh and upon some research someone said that by using RelativePath function you could fix this problem but even that didn't work for me. 

Solved: Get Data from JIRA API and import in Power BI with... - Microsoft Fabric Community

My modifed code is the following. 

let 
    BaseUrl = "myhttpsURL",
    JiraIDPerPage = 1000,
 
 GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json = Json.Document(RawData)
        in  Json,
 
    GetJiraIDCount = () =>
        let Url = "myhttpsURL&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),
    Table1 = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column101" = Table.ExpandRecordColumn(Table1, "Column1", {"id", "key", "fields"}, {"Issue ID", "Issue Key", "Issue Fields"}),
    #"Expanded issue.fields" = Table.ExpandRecordColumn(#"Expanded Column101", "Issue Fields", {"issuetype", "customfield_10901", "resolutiondate", "lastViewed", "created", "priority", "assignee", "updated", "status", "description", "summary", "creator", "reporter", "duedate"}, {"issuetype", "customfield_10901", "resolutiondate", "lastViewed", "created", "priority", "assignee", "updated", "status", "description", "summary", "creator", "reporter", "duedate"})
in
    #"Expanded issue.fields"

1. Does anyone know of another solution, or maybe a workaround to this solution with a slight twist. I am also looking into exporting data using power automate but power automate exports data each issue at a time, which is a pain as well. Does anyone know of a workable solution for this JIRA -> Power BI mystery?

[Anonymous]

Hello @rishtinz ,

Would you consider trying an alternative approach? You may connect your Jira on-premise to Power BI with the help of our app - Power BI Connector for Jira: https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=cloud&tab=overvie...

With our Power BI Connector you will be able to:

- build reports based on filtering of Jira custom fields and Marketplace apps custom fields
- combine reporting on multiple Jira projects
- use different data sources in single analytics (Jira, CRM, ERP, ITSM, Financial management software, etc)
- create Jira reports in Power BI with built-in Power BI dashboard templates

- scheduled refreshes for actual Jira reporting.

Daria

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

[Aryna]

Hi @rishtinz , I would like to share with you solutions that I found. The first one is to use apps on Atlassian website, but subsription can reach $30000 for larger team. Alternatively, you can try one-off service for installing the script that connects Power BI to Jira: https://vidi-corp.com/how-to-connect-jira-to-power-bi/