Power BI and DAX

Connect Power BI to Jira API for FREE

By Luke Harvey-Jones 5 min read

You want to connect to Jira… but there’s no official connector.

Instead, you’re faced with “solutions” that are essentially a thin wrapper around some M code, built by an agency you’ve never heard of—who then want to charge you a monthly fee for the privilege.

Feels a bit… off, doesn’t it?

If you’re building reports in Power BI, pulling data from Jira is a pretty common requirement—especially for teams working with agile delivery, ticket tracking, or operational analytics. But once you start building your own connector, you quickly run into one of the first real hurdles: pagination.

Jira’s API limits how many results you can retrieve in a single request. So a straightforward connection will only ever return a slice of your data—not exactly ideal when you’re trying to build accurate, reliable reports.

To get the full dataset, you need to handle pagination yourself.

In this post, I’ll walk through a practical approach using Power Query (M), covering authentication, API calls, and a recursive function to pull back all available data—properly.

 

The Problem: API Pagination in Jira

When querying Jira via its REST API, responses are returned in chunks (pages). Even if your query matches thousands of issues, the API will typically return a maximum of 100 records per request.

Without handling pagination:

  • Your reports will be incomplete
  • Metrics will be inaccurate
  • Stakeholders may make decisions based on partial data

To solve this, we need to repeatedly call the API until all pages have been retrieved.

 

The Approach

This solution does three key things:

  1. Authenticates using Basic Auth (email + API key)
  2. Calls the Jira API using a JQL query
  3. Recursively retrieves all pages using nextPageToken

The Full M Code

Below is the complete working solution:

let
    // 1. Enter your credentials here
    MyEmail = "email",
    MyAPIKey = "api_key",
    
    // 2. Encode credentials to Base64 format for Basic Auth
    AuthHeader = "Basic " & Binary.ToText(Text.ToBinary(MyEmail & ":" & MyAPIKey), BinaryEncoding.Base64),

    // 3. Define the recursive function
    GetJiraData = (optional nextPageToken as text) as list =>
    let
        BaseUrl = "https://acme.atlassian.net",
        RelativePath = "/rest/api/3/search/jql",
        
        QueryOptions = [ 
            jql = "project='some project'", 
            maxResults = "100",
            fields="*all"
        ] & (if nextPageToken <> null then [nextPageToken = nextPageToken] else []),

        // 4. Added Headers argument here
        Source = Web.Contents(BaseUrl, [
            RelativePath = RelativePath, 
            Query = QueryOptions,
            Headers = [
                Authorization = AuthHeader,
                #"Accept" = "application/json"
            ]
        ]),
        Json = Json.Document(Source),
        CurrentIssues = Json[issues],
        
        NextToken = if Record.HasFields(Json, "nextPageToken") then Json[nextPageToken] else null,
        
        Result = if NextToken <> null then 
            List.Combine({CurrentIssues, @GetJiraData(NextToken)}) 
        else 
            CurrentIssues
    in
        Result,

    FinalList = GetJiraData(null),
    TableResult = Table.FromRecords(FinalList)
in
    TableResult
M Code

Step-by-Step Breakdown

1. Authentication

The script begins by defining your credentials and encoding them into a Base64 string for Basic Authentication.

This is required because Jira’s API expects an Authorization header in the format:

Basic base64(email:api_key)
M Code

Power Query handles this using built-in binary conversion functions.

 

2. Defining the API Request

The request is built using:

  • A base URL (your Jira instance)
  • A relative API endpoint
  • Query parameters, including your JQL filter

This line is especially important:

jql = "project='some project'"
M Code

This determines which issues are returned, so in practice you’ll tailor this to your reporting needs.

 

3. Handling Pagination with Recursion

This is the core of the solution.

The function:

  • Calls the API
  • Extracts the current page of issues
  • Checks for a nextPageToken
  • Calls itself again if another page exists

This pattern allows Power Query to:

  • Dynamically retrieve all pages
  • Combine them into a single dataset
  • Avoid manual looping or hardcoding page numbers

The key line is:

@GetJiraData(NextToken)
M Code

The @ symbol enables recursive self-reference in M.

 

4. Converting to a Table

Once all pages are retrieved, the result is a list of records. This is converted into a table using:

Table.FromRecords(FinalList)
M Code

From here, you can expand fields, model relationships, and build reports as usual in Power BI.

 

Practical Considerations

While this solution is robust, there are a few things to keep in mind:

  • Performance: Requesting all fields (*all) can slow things down, consult the Atlassian Jira API documentation for how to narrow this down
  • Security: Avoid hardcoding credentials in shared environments!
  • Rate Limits: Jira APIs may throttle excessive requests – this is something to be mindful of
  • Filtering Early: Use JQL to reduce unnecessary data retrieval. It is very easy to learn an useful for building connectors like this

 

Final Thought

This is not a secure solution! Hardcoding your API key directly in your query (as shown here for demonstration purposes) introduces risk, especially if the report is shared or published.

In practice, you should look to store credentials securely—such as using Power BI parameters, Azure Key Vault, or managed credentials in the Power BI Service—so sensitive information is not exposed in your code. The M code above should only be used for testing!