Jira and Power BI

Hi,

You can also use Power BI Connector for Jira app to fetch Jira data (both Cloud and on-premise) to Power BI Desktop.

BR,

Liubov

Alternate 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,

Do you connect to JIRA PBIT  in Power BI Desktop? What error message do you get?

In addition, please make sure you are using the latest version of Power BI Desktop(2.57.5068.501).

Regards,
Lydia

Hi, tried connecting Jira Cloud in Power BI Web with the standard included app/connector, but no data is shown.  All labels loaded but no data.

Using this URL

https://xxxxxx.atlassian.net where xxxx is my domain

also tried adding the URL with the project name, like:

https://xxxxxx.atlassian.net/projects/AAA

but it never loads data to PBI.

Basic authentication works OK with a Token created in Jira and authentication works OK, actually in Jira it shows token was "used seconds ago".. and refresh in PowerBI shows OK, but all dashboard is empty, only column names are shown.

In Jira I have a lot of issues, and even a few assigned to myself, and I have several projects.

What should I check?

thanks!

JP

How can I connect Jira to Power BI service? Can someone share a printscreen?

Anonymous, Jeanxyz 
Thank you guys for providing the code and help. 
I'm working on a similar project in PBI, where I used to connect Jira with Power BI and successfully get the relevant data. However, I have two issues: -

1.  While using the below-mentioned code, the result is giving 3-time duplicates (I'm expecting to get 242 rows, but end result is 726 rows). Can you please guide me on how to change this code to get the right result rather than duplicates?

let
BaseUrl = "https://(Company domain)/rest/api/2/search?jql=project="Project name"&startAt=0&maxResults=1000",

JiraIDPerPage = 100,

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"​

2. Is it possible to make an auto-refresh functionality for this code, I have uploaded the report to the server and got an error while trying to make an auto-refresh. "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. Data source for Query1"

Can you please guide me on these two topics? 

Thank you for your support.

Regards,

Ahsan

Ahmedx , amitchandak , lbendlin , you guys can also support if used to work on this type of data. Thanks.

Hi,

In case anyone is looking for a no-code solution, we built a plugin for this and it allows you to easily select the Jira data using basic filters, or JQL. We also offer a 30 day free trial and are completely free for teams with 10 users or less.

Power BI Connector: https://marketplace.atlassian.com/apps/1238345/power-bi-connector?hosting=cloud&tab=overview

Feel free to reach out to us at [email protected]

Best,
Jack