Contrary to ServiceNow, in Jira, there is no history table to get the history of all tickets but there is a workaround. Before to begin, 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.

In their official webpage, using the changelog API, it allows to get it for 1 ticket:

https://yourjira/rest/api/3/issue/XXX/changelog

NOTE: replace yourjira by yours and XXX by your ticket key or ID

Open Power BI and click on “home -> get data -> web” then put the url:

This method is feasible for very few tickets. For a small/big database, I will use the same API by modifying it a little. I will explain 2 ways to do it, manually or automatically. The first steps are for both.

Click on “home -> get data -> blank query”:

It will open Power Query Editor then click on “advanced editor”:

Delete everything:

Option 1 with a manual ticket list

Put the following code and follow the instructions in the code:

let
    // change yourjira, youremail and yourtoken by yours
    AuthResponse = (k as text) as record => Json.Document(Web.Contents("https://yourjira/", [RelativePath = "rest/api/3/issue/" & k & "/changelog",Headers = [Accept = "application/json",
        Referer = "Basic " & Binary.ToText(Text.ToBinary("youremail" &":"& "yourtoken"),BinaryEncoding.Base64)]])),
    // change XXX by your ticket list -> "TEST-1", "TEST-2"
    Source = List.Transform({"XXX"},
        each [issues.key = _, Column1 = AuthResponse(_)]),
    #"Converted to Table" = Table.FromRecords(Source)
in
    #"Converted to Table"
              

Expand the column by clicking on this icon:

Click on “load more” then “OK”:

Option 2 with an automatic ticket list

This code will give me the last 50 (default) or 100 (maximum) tickets for the project:

let
    // change yourjira and yourproject by yours
    // to get 100 tickets (maximum) instead of 50 (default) -> https://yourjira/rest/api/3/search/jql?jql=project=yourproject&fields=*all&maxResults=100
    Source = Table.FromRecords({Json.Document(Web.Contents("https://yourjira/rest/api/3/search/jql?jql=project=yourproject&fields=*all"))}){0}[issues],
    expanded = Table.ExpandRecordColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"key", "fields"}, {"issues.key", "fields"}),
    issues = Table.ExpandRecordColumn(expanded, "fields", {"resolutiondate", "updated", "created"}, {"issues.key.resolutiondate", "issues.key.updated", "issues.key.created"}),
    // change yourjira, youremail and yourtoken by yours
    AuthResponse = (k as text) as record => Json.Document(Web.Contents("https://yourjira/", [RelativePath = "rest/api/3/issue/" & k & "/changelog",Headers = [Accept = "application/json",
        Referer = "Basic " & Binary.ToText(Text.ToBinary("youremail" &":"& "yourtoken"), BinaryEncoding.Base64)]])),
    #"Converted to Table" = Table.AddColumn(issues, "Column1", each AuthResponse(Text.From([issues.key])))
in
    #"Converted to Table"             
              

This code will give me all tickets for the project:

let
    // change yourjira and yourproject by yours
    GetPage = (optional nextPageToken as text) => let Url = "https://yourjira/", relativePath = if nextPageToken = null or nextPageToken = "" 
        then "rest/api/3/search/jql?jql=project=yourproject&maxResults=100&fields=*all" 
        else "rest/api/3/search/jql?jql=project=yourproject&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,
    expanded = Table.ExpandRecordColumn(Table.FromList(GetAllPages(null), Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"key", "fields"}, {"issues.key", "fields"}),
    issues = Table.ExpandRecordColumn(expanded, "fields", {"resolutiondate", "updated", "created"}, {"issues.key.resolutiondate", "issues.key.updated", "issues.key.created"}),
    // change yourjira, youremail and yourtoken by yours
    AuthResponse = (k as text) as record => Json.Document(Web.Contents("https://yourjira/", [RelativePath = "rest/api/3/issue/" & k & "/changelog",Headers = [Accept = "application/json",
        Referer = "Basic " & Binary.ToText(Text.ToBinary("youremail" &":"& "yourtoken"), BinaryEncoding.Base64)]])),
    #"Converted to Table" = Table.AddColumn(issues, "Column1", each AuthResponse(Text.From([issues.key])))
in
    #"Converted to Table"             
              

As for option 1, I will expand the column then click on “load more” and “OK”.

About the 2 types of columns:

• List: select “expand to new rows” if I want to have each value per row or select “extract values” if I want to have all values in 1 single row
  
  NOTE: if “extract values” gives error values, it means that there are sub-columns so to see them, select “expand to new rows”.
• Record: to display sub-columns of the main column