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 will 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:

https://yourjira/rest/api/3/search/jql?jql=project=projectname&fields=*all

NOTE:

• Change “yourjira” and “projectname” by yours
• 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

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 columns by clicking on this icon
  
• 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 (NOTE: in the code, change “yourjira” and “projectname” by yours) then paste it

let
  GetPage = (optional nextPageToken as text) =>
    let
        Url = "https://yourjira/",
        relativePath = if nextPageToken = null or nextPageToken = "" 
            then "rest/api/3/search/jql?jql=project=projectname&maxResults=100&fields=*all" 
            else "rest/api/3/search/jql?jql=project=projectname&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),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"Column1.key", "Column1.fields"})
    // to have the issues.id -> {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"}
in
    #"Expanded Column1"

• Click on the expand icon to select the needed columns

NOTE: to get all columns, just 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: https://yourjira/rest/api/3/project/projectname
• Option 2: https://yourjira/rest/api/3/project/search?keys=projectname&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.