If you are still using the API v2, read Power BI: connect to the Jira API v2 database. I will explain first the default way to get the data then how to get all tickets for a project. First at all, to connect to the database, I have to be “admin” or “owner” of the Jira project and I need my email and my token (to create the token, read Manage API tokens for your Atlassian account). Take note that there is no need to convert your API token into BASE64 encode.

Open Power BI and click on “home -> get data -> web”:

Option 1

Put the URL then click “OK:

URL/rest/api/3/search/jql?jql=project=yourprojectkey&fields=*all

• URL for API token with scopes:
  https://api.atlassian.com/ex/jira/yourcloudid
  
• URL for API token without scopes:
  https://yourjira
  

NOTE:

• To not repeat everything in double, for this article, I will use the second URL
• Change yourjira, yourcloudid and yourprojectkey by yours. To get the cloudid, put this in a browser: https://yourjira/_edge/tenant_info
• To get only the ticket ID column, replace “fields=*all” by “expand=insight”

In the authentication window, select “basic” and I put my credentials:

NOTE: this window will not appear anymore once I will be connected to Jira but if it doesn’t appear for your first connection, go to the “edit credentials” section to edit it if needed

It will give me the last 50 tickets, to get 100 (maximum tickets), I will add this parameter: &maxResults=100

To get the next tickets, I will add this parameter: &nextPageToken=XXX

Once I will reach the last tickets, I should see this:

Still have tickets	No more tickets

To avoid a huge data, what I like to do, it is to load only the columns I want. For that:

• Delete all steps until here
  
• Expand the column by clicking on this icon
  
• Click on “load more” (if I don’t do that, some columns won’t appear) then select the needed columns
  

Once done, to not repeat the same steps with the 2nd URL, I will click on “advanced editor”:

Copy everything (green section) except the first 2 lines

Now I will create the 2nd URL then click on “advanced editor”, delete everything after the 2nd line then paste the green section:

If you don’t know which columns you want, unfortunately, in Atlassian, there is no page with the information so you will have to play with the columns before to be able to personalize your data.

For “record” columns, to get the result, you will have to expand it then select the value:

For “list” columns, click on the expand icon then there will have 2 choices:

• Extract values: it will extract all values in a single row, just select how you want to separate each value
  
  
  If you get an error, values can’t be concatenated so just use “expand to new rows”
  
• Expand to new rows: it will extract each value in a new row so if there are 4 values, you will get 3 new rows
  

Option 2

The option 1 is not suitable if my project has a lot of tickets because I will have to create muliple URLs so to avoid this situation and to get all tickets, I will use those following steps:

• Use the URL of the option 1
• Click on “advanced editor” then delete everything
• Copy below then paste it and follow the instructions in the code

let
    GetPage = (optional nextPageToken as text) =>
        let
            // URL for API token with scopes: https://api.atlassian.com/ex/jira/yourcloudid
            // URL for API token without scopes: https://yourjira
            // change yourjira, yourcloudid and yourprojectkey by yours
            Url = "URL",
            relativePath = if nextPageToken = null or nextPageToken = "" 
                then "/rest/api/3/search/jql?jql=project=yourprojectkey&maxResults=100&fields=*all" 
                else "/rest/api/3/search/jql?jql=project=yourprojectkey&maxResults=100&fields=*all&nextPageToken=" & nextPageToken,
            Response = Json.Document(Web.Contents(Url, [RelativePath = relativePath])),
            Issues = try Response[issues] otherwise {},
            NextToken = try Response[nextPageToken] otherwise null,
            Result = [Issues = Issues, NextToken = NextToken]
        in
            Result,
    GetAllPages = (nextPageToken as nullable text) as list =>
        let
            CurrentPage = GetPage(nextPageToken),
            CurrentIssues = CurrentPage[Issues],
            NextToken = CurrentPage[NextToken],
            MoreIssues = if NextToken <> null 
                then @GetAllPages(NextToken) 
                else {},
            Combined = List.Combine({CurrentIssues, MoreIssues})
        in
            Combined,
    AllIssues = GetAllPages(null),
    #"Converted to Table" = Table.FromList(AllIssues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // to have the issues.id -> {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"}
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"Column1.key", "Column1.fields"})
in
    #"Expanded Column1"

• Click on the expand icon then click on “load more” (if I don’t do that, some columns won’t appear) before to select the needed columns

NOTE: to get all columns, click on “OK”

To get more information about my project, I will use another API called “project”, the ones above are using the “search” API:

• Option 1: URL/rest/api/3/project/yourprojectkey
• Option 2: URL/rest/api/3/project/search?keys=yourprojectkey&expand=insight

NOTE: the option 2 will give me some extra information like the total number of tickets and the last update date

For more API, go to the official webpage of Atlassian Rest API v3.

Power BI Desktop: edit credentials

To edit my credentials, I will go to “global permissions” in “data source settings” then right click on the first “atlassian.net” and click on “edit permissions”

Click on “edit”

In the “user name”, I put my account and in the “password”, I put my API token

Once done, repeat the same things for the other “atlassian.net”.

Power BI Service: edit credentials

Once published in Power BI Service, in order to configure the automatic refresh, in “data source credentials”, click on this option:

Configure like in the picture according to your token (in the “user name”, I put my account and in the “password”, I put my API token) then click on “sign in”:

For API token with scopes	For API token without scopes