Connecting Jira to Power BI using the Web API

Hi all,

I've recently been using Jira to manage my workload, utilising user stories, epics and sprints. I've been looking for a method to extract my user story and task data from a specific project in Jira and was looking at using Jira's web API to do the connection. I read another thread where someone has posted a M script code which utilises Jira's web API and I'm having some issues connecting with it (link to thread below).

https://community.powerbi.com/t5/Desktop/Jira-and-Power-BI/td-p/393785/page/2

The above code required authenticating, after reading Jira's Authentication document I created another table that encoded the API token from Jira in Base 64.

Figure 1. Created a table which held the Base 64 encoded username and API token

I then copied the below M script into a new table. Note I added the authorization in the header. Note that in the header I've referenced the encoded Credentials table.

let 
    BaseUrl = Web.Contents("https://companyname.atlassian.net/rest/api/3/search?jql=project in ('TEST')", [Headers=[Authorization="Basic " & Credentials]]),

    JiraIDPerPage = 1000,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetJiraIDCount = () =>
        let Url   = BaseUrl & "&maxResults=0",
            Json  = GetJson(Url),
            Count = Json[#"total"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            Value = Json[#"issues"]
        in  Value,
 
    JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
    PageCount   = Number.RoundUp(JiraIDCount / JiraIDPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    JiraID    = List.Union(Pages),
    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})
in
    #"Expanded Column1"

When I loaded the query I'm getting this error:

Figure 2. Error that I'm getting

Not sure what I'm doing wrong here. I quite like this script as it seems to account for the fact that the API only seems to retrieve a max of 50-100 issues and relies on pagination to retrieve additional records.

Now I've then tried using the web data source and adding in the credentials in the header like the below script:

let
    Source = Json.Document(Web.Contents("https://companyname.atlassian.net/rest/api/3/search?jql=project in ('TEST')", [Headers=[Authorization="Basic " & Credentials]])),
    issues = Source[issues],
    #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", 
in
    #"Expanded Column1"

This seems to work but the returend value in a nested JSON format seems only allow a Maxresult of 50 records.

Figure 3. Returned a max of 50. I've got 26 records so I'm getting close

I prefer to use the first code provided in the other thread but not sure how to get it to work. Any help would be greatly appreciated.