Pwer BI to Jira Integration

[GauravSinghPBI]

Hi Team,

We ae trying to import the data from Jira Cloud to Power BI. 

Our Major requirements are as follows.

1. We would like to fetch the jiras dynamically and in live/Direct/Import Modes.

2. We should be able to fetch all the jira assigned to any individual or basis on any jira project (jiras created in last one year for ex.)

3. Should be able to fetch the fields configured in jira projects with all issues/stories/epic etc

4. Can we have the main work request number (Story’s) and the Impact Assessments (subtasks) linking to the main work reception ticket

5. Will it be possible to show the name of the individual the impact assessment is assigned too or the team area it is attached too

6. Is it possible to see why something has not went ahead – can we get details on closed work requests that have not been completed

7. Will it be possible for PowerBI to give a full report from when he board was created to present day

Thanks,

Gaurav

[v-menakakota]

Hi @GauravSinghPBI  ,

Thank you for the reply, from your latest reply the error, indicates that the data returned from Jira isn’t in the structure expected by the sample code. This can happen due to differences in Jira project configuration, custom fields, or how the REST API is returning results in your environment.

At this point, as the issue is becoming more environment-specific and may require deeper troubleshooting with actual Jira data, we recommend raising a support ticket with Microsoft Support. This will allow our support team to review your full setup and provide hands-on guidance tailored to your environment.

Please refer below link on how to raise a contact support or support ticket.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn 

Thank you.

[v-menakakota]

Hi @GauravSinghPBI ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team.

[GauravSinghPBI]

@v-menakakota @jaineshp Thanks for the support and Guidance. We are able to fetch the data in Power BI Desktop using API method. Now we have two further requirements to complete this integration.

1. We would like to refresh the data in Power BI service automatically few times a day. We do not want to publish the report again to see the latest data. Is this possible without using Dataverse or power app or any other compenent.
2. We want to view Jira historic snapshot also. For ex. if we want to know how many jira were open almost a year back or last week, we should hae that data.

Thanks,

Gaurav

[v-menakakota]

Hi @GauravSinghPBI ,

Glad to hear you’re now able to fetch the data in Power BI Desktop! For your first question, yes, you can set up automatic refresh once the report is published to the Power BI Service. Power BI lets you schedule dataset refreshes so you don’t have to republish every time you need the latest data. With a Pro license you can refresh up to 8 times a day, and with Premium you can go up to 48 times a day. No extra components like Dataverse or Power Apps are needed for this.

For the historic snapshot requirement, it’s important to know that the Jira API only provides the current state of issues. If you want to see how many items were open at a certain point in the past (for example, last week or last year), you’ll need to preserve history in your dataset. You can do this by setting up Incremental Refresh in Power BI so old data stays in the model while only new or updated records are refreshed. Another approach is to pull Jira’s changelog fields, which track how issues moved between statuses over time, and use that to rebuild historical views.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[GauravSinghPBI]

Thanks @v-menakakota . Let me give it a try.

One of my colleague mentioned that we can't refresh the Model in Service directly instead we have to use On-Premise data gateway. Is that the case? When i refresh the Semantic Model, it provides the warning

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

Once we achieve the refresh of the dataset, will try to do the incremental refresh.

Thanks,

Gaurav

[v-menakakota]

Hi @GauravSinghPBI ,

Thanks for sharing the document. You don’t need an On-Premises Gateway for Jira Cloud, since it’s already an online source. The warning is happening because your query is seen as a dynamic data source.

As the doc explains, these types of queries won’t refresh in the Service unless you set them up in a supported way for example, by using parameters for the base URL or using the RelativePath and Query options in Web.Contents(). If you update your query like that, scheduled refresh should work fine in the Service without a gateway. After that, you can move on with setting up Incremental Refresh to capture the history.

Thank you.

[GauravSinghPBI]

Thanks @v-menakakota @jaineshp 

Just for community benefits i am providing the code which i am using in Power BI Desktop.

I am seeing Import Mode in Model of the query. Could you please help how can i refresh the data directly in service without using On-Premise Data gateway?

----------------Advance Query Code --------------

let

    GetJiraData = (projectCode as text, authToken as text, componentFilter as text) =>

    let

        // Helper function to fetch data from Jira API

        FetchData = (startAt as number, jiracode, auth, component) =>

        let

            componenturl = if component = null or component = "" then "" else " and component='" & component & "'",

            URL = "https://xxxx.atlassian.net/rest/api/3/search?jql=project=" & jiracode & "&maxResults=100&startAt=" & Text.From(startAt),

Headers=[Authorization="Basic " & Binary.ToText(Text.ToBinary("mail id:API Token"), BinaryEncoding.Base64)],

            Source = Json.Document(Web.Contents(URL, [Headers = Headers]))

        in

            Source,

        // Pagination helper function

        FetchPage = (startAt, jiracode, auth, component) as record =>

            let

                Data = FetchData(startAt, jiracode, auth, component),

                Items = Data[issues],

                Total = Data[total]

            in

                [Items = Items, Total = Total],

        // Initial data fetch and pagination setup

        InitialFetch = FetchPage(0, projectCode, authToken, componentFilter),

        FirstItems = InitialFetch[Items],

        Total = InitialFetch[Total],

        PageSize = 100,

        NumPages = Number.RoundUp(Total / PageSize),

        PageOffsets = List.Transform({1..NumPages - 1}, each _ * PageSize),

        // Fetch remaining pages and combine all data

        OtherPages = List.Transform(PageOffsets, each FetchPage(_, projectCode, authToken, componentFilter)[Items]),

        AllItems = List.Combine({FirstItems} & OtherPages),

        // Convert to table and begin transformations

        ConvertedToTable = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        // Expand main issue structure

        ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedToTable, "Column1", 

            {"expand", "id", "self", "key", "fields"}, 

            {"issues.expand", "issues.id", "issues.self", "issues.key", "issues.fields"}),

        // Expand main fields

        ExpandedIssueFields = Table.ExpandRecordColumn(ExpandedColumn1, "issues.fields", 

            {"resolution", "aggregatetimeoriginalestimate", "assignee", "votes", "issuetype", "timeestimate", "status", "aggregatetimeestimate", "creator", "timespent", "aggregatetimespent", "workratio", "labels", "components", "reporter", "progress", "project", "resolutiondate", "watches", "updated", "timeoriginalestimate", "description", "summary", "statuscategorychangedate", "fixVersions", "priority", "versions", "created", "security", "parent", "customfield_20757"}, 

            {"issues.fields.resolution", "issues.fields.aggregatetimeoriginalestimate", "issues.fields.assignee", "issues.fields.votes", "issues.fields.issuetype", "issues.fields.timeestimate", "issues.fields.status", "issues.fields.aggregatetimeestimate", "issues.fields.creator", "issues.fields.timespent", "issues.fields.aggregatetimespent", "issues.fields.workratio", "issues.fields.labels", "issues.fields.components", "issues.fields.reporter", "issues.fields.progress", "issues.fields.project", "issues.fields.resolutiondate", "issues.fields.watches", "issues.fields.updated", "issues.fields.timeoriginalestimate", "issues.fields.description", "issues.fields.summary", "issues.fields.statuscategorychangedate", "issues.fields.fixVersions", "issues.fields.priority", "issues.fields.versions", "issues.fields.created", "issues.fields.security", "issues.fields.parent", "Automated Status"}),

        // Expand parent issue information

        ExpandedParent = Table.ExpandRecordColumn(ExpandedIssueFields, "issues.fields.parent", 

            {"id", "key", "self", "fields"}, 

            {"issues.fields.parent.id", "issues.fields.parent.key", "issues.fields.parent.self", "issues.fields.parent.fields"}),

        ExpandedParentFields = Table.ExpandRecordColumn(ExpandedParent, "issues.fields.parent.fields", 

            {"summary", "status", "priority", "issuetype"}, 

            {"issues.fields.parent.fields.summary", "issues.fields.parent.fields.status", "issues.fields.parent.fields.priority", "issues.fields.parent.fields.issuetype"}),

        ExpandedParentIssueType = Table.ExpandRecordColumn(ExpandedParentFields, "issues.fields.parent.fields.issuetype", 

            {"self", "id", "description", "iconUrl", "name", "subtask", "avatarId", "hierarchyLevel"}, 

            {"issues.fields.parent.fields.issuetype.self", "issues.fields.parent.fields.issuetype.id", "issues.fields.parent.fields.issuetype.description", "issues.fields.parent.fields.issuetype.iconUrl", "issues.fields.parent.fields.issuetype.name", "issues.fields.parent.fields.issuetype.subtask", "issues.fields.parent.fields.issuetype.avatarId", "issues.fields.parent.fields.issuetype.hierarchyLevel"}),

        // Expand project information

        ExpandedProject = Table.ExpandRecordColumn(ExpandedParentIssueType, "issues.fields.project", 

            {"self", "id", "key", "name", "projectTypeKey", "simplified", "avatarUrls"}, 

            {"issues.fields.project.self", "issues.fields.project.id", "issues.fields.project.key", "issues.fields.project.name", "issues.fields.project.projectTypeKey", "issues.fields.project.simplified", "issues.fields.project.avatarUrls"}),

        // Expand issue type information

        ExpandedIssueType = Table.ExpandRecordColumn(ExpandedProject, "issues.fields.issuetype", 

            {"self", "id", "description", "iconUrl", "name", "subtask", "avatarId", "hierarchyLevel"}, 

            {"issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.name", "issues.fields.issuetype.subtask", "issues.fields.issuetype.avatarId", "issues.fields.issuetype.hierarchyLevel"}),

        // Expand assignee information

        ExpandedAssignee = Table.ExpandRecordColumn(ExpandedIssueType, "issues.fields.assignee", 

            {"self", "accountId", "emailAddress", "avatarUrls", "displayName", "active", "timeZone", "accountType"}, 

            {"issues.fields.assignee.self", "issues.fields.assignee.accountId", "issues.fields.assignee.emailAddress", "issues.fields.assignee.avatarUrls", "issues.fields.assignee.displayName", "issues.fields.assignee.active", "issues.fields.assignee.timeZone", "issues.fields.assignee.accountType"}),

        // Expand resolution information

        ExpandedResolution = Table.ExpandRecordColumn(ExpandedAssignee, "issues.fields.resolution", 

            {"self", "id", "description", "name"}, 

            {"issues.fields.resolution.self", "issues.fields.resolution.id", "issues.fields.resolution.description", "issues.fields.resolution.name"}),

        // First round of column renaming

        RenamedColumns1 = Table.RenameColumns(ExpandedResolution, {

            {"issues.id", "Issue ID"}, 

            {"issues.self", "Issue API Link"}, 

            {"issues.key", "Issue Key"}, 

            {"issues.fields.assignee.displayName", "Issue Assignee Name"}, 

            {"issues.fields.issuetype.name", "Issue Type"}

        }),

        // Transform labels array to comma-separated text

        ExtractedLabels = Table.TransformColumns(RenamedColumns1, {

            "issues.fields.labels", each Text.Combine(List.Transform(_, Text.From), ","), type text

        }),

        // Second round of column renaming

        RenamedColumns2 = Table.RenameColumns(ExtractedLabels, {

            {"issues.fields.labels", "Issue Labels"}, 

            {"issues.fields.project.key", "Project Key"}, 

            {"issues.fields.project.id", "Project ID"}, 

            {"issues.fields.project.name", "Project Name"}, 

            {"issues.fields.project.projectTypeKey", "Project Type"}, 

            {"issues.fields.summary", "Issue Summary"}

        }),

        // Expand priority information

        ExpandedPriority = Table.ExpandRecordColumn(RenamedColumns2, "issues.fields.priority", 

            {"self", "iconUrl", "name", "id"}, 

            {"issues.fields.priority.self", "issues.fields.priority.iconUrl", "issues.fields.priority.name", "issues.fields.priority.id"}),

        // Third round of column renaming

        RenamedColumns3 = Table.RenameColumns(ExpandedPriority, {

            {"issues.fields.priority.name", "Issue Priority"}, 

            {"issues.fields.created", "Issue Created At"}

        }),

        // Process datetime - split by timezone delimiter

        SplitDateTime = Table.SplitColumn(RenamedColumns3, "Issue Created At", 

            Splitter.SplitTextByDelimiter("+", QuoteStyle.Csv), 

            {"Issue Created At.1", "Issue Created At.2"}),

        // Convert data types

        ChangedTypes = Table.TransformColumnTypes(SplitDateTime, {

            {"Issue Created At.1", type datetime}, 

            {"Issue Created At.2", Int64.Type}

        }),

        // Clean up datetime columns

        RenamedDateTime = Table.RenameColumns(ChangedTypes, {{"Issue Created At.1", "Issue Created"}}),

        RemovedTimezoneColumn = Table.RemoveColumns(RenamedDateTime, {"Issue Created At.2"}),

        // Rename parent issue columns

        RenamedParentColumns = Table.RenameColumns(RemovedTimezoneColumn, {

            {"issues.fields.parent.id", "Issue Parent ID"}, 

            {"issues.fields.parent.key", "Issue Parent Key"}, 

            {"issues.fields.parent.self", "Issue Parent Api Link"}, 

            {"issues.fields.parent.fields.summary", "Issue Parent Summary"}

        }),

        // Expand parent status information

        ExpandedParentStatus = Table.ExpandRecordColumn(RenamedParentColumns, "issues.fields.parent.fields.status", 

            {"self", "description", "iconUrl", "name", "id", "statusCategory"}, 

            {"issues.fields.parent.fields.status.self", "issues.fields.parent.fields.status.description", "issues.fields.parent.fields.status.iconUrl", "issues.fields.parent.fields.status.name", "issues.fields.parent.fields.status.id", "issues.fields.parent.fields.status.statusCategory"}),

        // Expand current issue status information

        ExpandedStatus = Table.ExpandRecordColumn(ExpandedParentStatus, "issues.fields.status", 

            {"self", "description", "iconUrl", "name", "id", "statusCategory"}, 

            {"issues.fields.status.self", "issues.fields.status.description", "issues.fields.status.iconUrl", "issues.fields.status.name", "issues.fields.status.id", "issues.fields.status.statusCategory"}),

        // Final expansion - automated status

        ExpandedAutomatedStatus = Table.ExpandRecordColumn(ExpandedStatus, "Automated Status", 

            {"value"}, 

            {"Automated Status"}),

        // Remove unnecessary columns from final result

        CleanedData = Table.RemoveColumns(ExpandedAutomatedStatus, {

            "issues.fields.assignee.avatarUrls", 

            "issues.fields.votes", 

            "issues.fields.status.statusCategory", 

            "issues.fields.creator", 

            "issues.fields.components", 

            "issues.fields.reporter", 

            "issues.fields.progress", 

            "issues.fields.project.avatarUrls", 

            "issues.fields.watches", 

            "issues.fields.description", 

            "issues.fields.fixVersions", 

            "issues.fields.versions", 

            "issues.fields.parent.fields.status.statusCategory", 

            "issues.fields.parent.fields.priority"

        })

    in

        CleanedData,

    // Call the function with the original parameters

    Result = GetJiraData("OTOM", "ATATT3xFfGF0D3-T9cQuGzvr5jj2WPrh6wjLpybSzp5uu9-7P6zUy53uK9lWhIraZlfv62n2f3t69jUud8b4lxRHYXSD-5QdS3rV99avlCyOHVtTdOfEQ539UKPlK9jesrNT3wkrzyE3TMMDSX4s-OYilIomU20NS_4fDTAi6_1ulZYxhtdT0Ek=4E619766", "Hub in the Box"),

    #"Renamed Columns" = Table.RenameColumns(Result,{{"Issue Key", "Jira Number"}, {"Issue Assignee Name", "Assignee"}, {"issues.fields.assignee.emailAddress", "Assignee EmailAddress"}, {"issues.fields.assignee.timeZone", "Assignee TimeZone"}, {"issues.fields.status.name", "Status"}}),

    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Project ID", "Project Key", "Project Name", "Project Type", "Jira Number", "Assignee", "Issue Type", "Status", "Assignee TimeZone", "Assignee EmailAddress", "issues.expand", "Issue ID", "Issue API Link", "issues.fields.resolution.self", "issues.fields.resolution.id", "issues.fields.resolution.description", "issues.fields.resolution.name", "issues.fields.aggregatetimeoriginalestimate", "issues.fields.assignee.self", "issues.fields.assignee.accountId", "issues.fields.assignee.active", "issues.fields.assignee.accountType", "issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.subtask", "issues.fields.issuetype.avatarId", "issues.fields.issuetype.hierarchyLevel", "issues.fields.timeestimate", "issues.fields.status.self", "issues.fields.status.description", "issues.fields.status.iconUrl", "issues.fields.status.id", "issues.fields.aggregatetimeestimate", "issues.fields.timespent", "issues.fields.aggregatetimespent", "issues.fields.workratio", "Issue Labels", "issues.fields.project.self", "issues.fields.project.simplified", "issues.fields.resolutiondate", "issues.fields.updated", "issues.fields.timeoriginalestimate", "Issue Summary", "issues.fields.statuscategorychangedate", "issues.fields.priority.self", "issues.fields.priority.iconUrl", "Issue Priority", "issues.fields.priority.id", "Issue Created", "issues.fields.security", "Issue Parent ID", "Issue Parent Key", "Issue Parent Api Link", "Issue Parent Summary", "issues.fields.parent.fields.status.self", "issues.fields.parent.fields.status.description", "issues.fields.parent.fields.status.iconUrl", "issues.fields.parent.fields.status.name", "issues.fields.parent.fields.status.id", "issues.fields.parent.fields.issuetype.self", "issues.fields.parent.fields.issuetype.id", "issues.fields.parent.fields.issuetype.description", "issues.fields.parent.fields.issuetype.iconUrl", "issues.fields.parent.fields.issuetype.name", "issues.fields.parent.fields.issuetype.subtask", "issues.fields.parent.fields.issuetype.avatarId", "issues.fields.parent.fields.issuetype.hierarchyLevel", "Automated Status"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Issue Created", "Created"}, {"issues.fields.updated", "Updated"}}),

    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Project ID", "Project Key", "Project Name", "Project Type", "Jira Number", "Assignee", "Issue Type", "Status", "Created", "Updated", "Assignee TimeZone", "Assignee EmailAddress", "issues.expand", "Issue ID", "Issue API Link", "issues.fields.resolution.self", "issues.fields.resolution.id", "issues.fields.resolution.description", "issues.fields.resolution.name", "issues.fields.aggregatetimeoriginalestimate", "issues.fields.assignee.self", "issues.fields.assignee.accountId", "issues.fields.assignee.active", "issues.fields.assignee.accountType", "issues.fields.issuetype.self", "issues.fields.issuetype.id", "issues.fields.issuetype.description", "issues.fields.issuetype.iconUrl", "issues.fields.issuetype.subtask", "issues.fields.issuetype.avatarId", "issues.fields.issuetype.hierarchyLevel", "issues.fields.timeestimate", "issues.fields.status.self", "issues.fields.status.description", "issues.fields.status.iconUrl", "issues.fields.status.id", "issues.fields.aggregatetimeestimate", "issues.fields.timespent", "issues.fields.aggregatetimespent", "issues.fields.workratio", "Issue Labels", "issues.fields.project.self", "issues.fields.project.simplified", "issues.fields.resolutiondate", "issues.fields.timeoriginalestimate", "Issue Summary", "issues.fields.statuscategorychangedate", "issues.fields.priority.self", "issues.fields.priority.iconUrl", "Issue Priority", "issues.fields.priority.id", "issues.fields.security", "Issue Parent ID", "Issue Parent Key", "Issue Parent Api Link", "Issue Parent Summary", "issues.fields.parent.fields.status.self", "issues.fields.parent.fields.status.description", "issues.fields.parent.fields.status.iconUrl", "issues.fields.parent.fields.status.name", "issues.fields.parent.fields.status.id", "issues.fields.parent.fields.issuetype.self", "issues.fields.parent.fields.issuetype.id", "issues.fields.parent.fields.issuetype.description", "issues.fields.parent.fields.issuetype.iconUrl", "issues.fields.parent.fields.issuetype.name", "issues.fields.parent.fields.issuetype.subtask", "issues.fields.parent.fields.issuetype.avatarId", "issues.fields.parent.fields.issuetype.hierarchyLevel", "Automated Status"})

in

    #"Reordered Columns1"

----------------------Ends ----------------------------

[v-menakakota]

Hi @GauravSinghPBI  ,

Thanks for sharing the details and the M script. As you’re connecting to Jira Cloud, you don’t need an On-Premises Data Gateway. The challenge is that your query is being treated as a dynamic data source, which is why refresh in the Service isn’t working.

I’d recommend raising a support ticket with Microsoft so the product team can look directly at your setup and guide you further.

Best Regards, 
Community Support Team.

[v-menakakota · Accepted solution]

Hi @GauravSinghPBI ,

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community.

Thank you.

[GauravSinghPBI]

Yes. I am able to get the data from Jira Project using the code i provided in last comment.

Thanks,

Gaurav

[v-menakakota]

Hi  @GauravSinghPBI  ,

Thank you for the update. Hope everything’s working fine now. Please accept your solution as accepted solution, This would be helpful for other members who may encounter similar issues. 
Continue using Fabric Community Forum .

Best Regards, 
Community Support Team 

[GauravSinghPBI]

@jaineshp - Could you please help.

[jaineshp]

Hey @GauravSinghPBI,

I've been working on similar Jira-PowerBI integrations and here's what worked best for our team:

Quick Setup Steps

1. API Connection Setup

• Get your Jira API token from Atlassian account settings
• Use Web connector in Power BI with this URL format: https://yourcompany.atlassian.net/rest/api/3/search
• Basic auth: your email + API token

2. Dynamic Data Fetching

• Create parameters for assignee, project, date ranges
• Use JQL queries like: assignee = currentUser() AND created >= -365d
• Import mode works better than DirectQuery (trust me on this one)
• Set refresh to run 4-6 times daily

3. Essential Fields to Pull

• Standard: key, summary, status, assignee, created, updated
• Custom: Use /rest/api/3/field to map your custom field IDs
• Parent links: Essential for story-subtask relationships

4. Story-Subtask Hierarchy

• Pull parent.key field from subtasks
• Create a separate relationship table
• Link main stories to their impact assessments using issue keys

5. Team Assignment Details

• Extract assignee.displayName for individual names
• Use components or custom fields for team areas
• Pull project.projectCategory if you have team-based projects

6. Closed Item Analysis

• Include resolution and resolutiondate fields
• Filter: status = Closed AND resolution != Done for incomplete items
• Use changelog for status history if needed

7. Historical Reporting

• Start with full data pull using pagination (startAt parameter)
• Set up incremental refresh for daily updates
• Archive old data quarterly to keep performance smooth

Data Refresh Strategy:

• Full refresh weekly
• Incremental daily for recent changes
• Manual refresh for urgent reporting needs

This approach has been rock solid for our 50+ project portfolio. The key is starting simple and building complexity gradually.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[GauravSinghPBI]

@jaineshp Thanks for the response

I went to Power BI Desktop - Get Data - Web - with Basic option put the below URL and click ok

https://xxxxxxx.atlassian.net/rest/api/3/search

Power Query Editor opens and Query named "search" is appearing but there is no data except one row with below columns and values

startAt     maxResults    total          issues

0               50                  0

Do i need to perform any other action. Kindly elaborate.

Thanks,

Gaurav