How to Connect Power BI to JIRA Using REST API Without Third-Party Apps

 After years of delivering successful system integration projects, I'm excited to share this write-up that continues to drive value for many users! Two years ago, I proposed a solution for Microsoft Power BI and Atlassian JIRA integration using the REST API—without relying on any third-party apps. I found no such simple solution available on the internet, so I got a hint from a simple curl command and developed an integration solution from it. This solution is still actively helping professionals to fetch JIRA data into PowerBI. The original post was shared in Atlassian Community here https://community.atlassian.com/t5/Marketplace-Apps-Integrations/How-to-connect-Power-BI-to-JIRA-using-Rest-API-and-without-third/qaq-p/2255960?utm_source=atlcomm&utm_medium=email&utm_campaign=mentions_reply&utm_content=topic#U2804427

Integrating JIRA with Power BI can help organizations access real-time project data, create insightful reports, and manage workflows more efficiently. While many third-party apps offer this functionality, you can bypass them using JIRA’s REST API to establish a direct connection with Power BI. This solution is simple and scalable.

I found no such simple solution available on internet so I got a hint from simple curl command and tried to have integration solution out of it:

curl -D- \
-X GET \
-H "Authorization: Basic YOUR_BASE64_TOKEN" \
-H "Content-Type: application/json" \
"https://DOMAIN.atlassian.net/rest/api/2/issue/ISSUE"

Step 1: Generate Base64-Encoded Authentication Token

First, you need a Base64-encoded token that JIRA will use for authentication.

  1. Open a terminal (or use an online tool like Base64 Encode).

  2. Encode the string: EMAIL:API_TOKENExample: myemail@company.com:your_api_token_hereYou can generate this with a curl command:

echo -n "myemail@company.com:your_api_token_here" | base64

Step 2: Connect Power BI to JIRA

  1. Open Power BI.

  2. Go to Get Data > Web.

  3. Enter the JIRA REST API URL (as used in step 2).

  4. In the headers:

  • Add Authorization: Basic [Space] YOUR_BASE64_TOKEN

  • Add Content-Type: application/json.

![Screenshot of Power BI connection screen with headers]



Click OK to establish the connection.

Step 3: Create a Power BI Function to Fetch Data

You’ll now create a Power BI function to retrieve data from JIRA in batches, as JIRA limits the number of records retrieved in a single query. Name it "GetJIRAData"

  • In Power BI, create a new query and enter the following code:

let
    GetJiraData = (offset) =>
    let    
        myURL = "https://your_domain.atlassian.net/rest/api/3/",
        Jql = "project=WPM",  // Modify this with your actual project key
        Fields = "summary,key,issuetype,status,assignee",  // Add fields as needed
        StartAt = Number.ToText(offset),
        MaxR = "100",
        Source = Json.Document(Web.Contents(myURL, 
            [RelativePath="search?",
                Query = [ jql=Jql, fields=Fields, maxResults=MaxR, startAt=StartAt ],
                Headers = [ Authorization="Basic YOUR_BASE64_TOKEN", #"Content-Type"="application/json" ]
            ]
        ))
    in
        Source
in
    GetJiraData

  • Replace the placeholder values with actual data (e.g., your domain, API token).

  • You can modify the Fields variable to fetch additional data from JIRA.

Step 4: Query Iteration for Large Data Sets

Since JIRA limits the number of records fetched per call (usually 1000), you can create an iterative query in Power BI to retrieve all records in batches.

  • Create another query, naming it JiraData:

let
    JiraData = List.Generate(
        () => [offset = 0, data = GetJiraData(0)],
        each not List.IsEmpty([data][issues]),
        each [offset = [offset] + 100, data = GetJiraData([offset])],
        each [data]
    )
in
    JiraData

Power BI will now iterate through the data in batches of 100 records until all issues are retrieved.

Step 6: Transform and Load Data into Power BI

Once the data is imported:

  • Transform the data "JIRAData" as needed within Power BI to fit your reporting needs.

Here are few transformation I applied. Start with "Converted to table" and then expand it to get columns.



  • Create visualizations and reports based on the JIRA data.

#PowerBI #JIRA #SystemIntegration #RESTAPI #DataIntegration #TechInnovation #ProjectManagement

Email Post

Comments

Post a Comment