Hi to all,
Since it took me a very long time to solve my problem and I finally managed it with this community, I didn't want to withhold the solution from you and show you how I managed to get data into Power BI via the Jira Rest API. I hope it helps many others who have the same problem and feel like they are searching the whole www and can't find a suitable solution, like me. About the initial situation. I wanted to get data from my company's Jira into Power BI via the Rest API. This presented me with several challenges:
- The custom filter from Jira was outputting >5000 rows of data. A call in Jira is limited to a maximum of 1000 results. The m-code therefore had to contain a pagination
- I had to define certain fields that were relevant to me and not extract all the data from Jira
- The header had to contain an API token as authorization, as it is a Jira of my organization
- The data source should be a static one, otherwise I got the error message of the dynamic data source in Power BI Services when the report should be published.
The biggest problem turned out to be the constant error message from a dynamic data source in Power BI Services. To solve this I use the commands Web.Contents, RelativePath and Query. These articles have helped me a lot as a basis for understanding:
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...
To get the API token from Jira, follow this article:
https://support.atlassian.com/atlassian-account/docs/manage-api-tokens-for-your-atlassian-account/
Despite the blogs I found on the internet, a basic authorisation did not work for me. I used the bearer authorisation type with my unmodified API token.
To get only the data of my custom filter from Jira, the filter ID was important. You can find this out in the Jira url when you call up the filter.
Long story short, here is the final query that worked well for me and solved all my challenges. I have tried to explain the individual steps in the comments:
let
BaseUrl =
Web.Contents(
"https://XXX.XXX.net/jira/rest/api",
[
RelativePath="2/search",
Query=
[
jql="filter=525545",
fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
],
Headers=[Authorization="Bearer **Your Token here**"]]
) ,
// JiraIDPerPage: Number of Jira tickets per page.
JiraIDPerPage = 1000,
// GetJson: Function to retrieve data from the Jira API and return it as JSON.
GetJson = (Url) =>
let
RawData = Web.Contents(
"https://XXX.XXX.net/jira/rest/api",
[
RelativePath="2/search",
Query=
[
jql="filter=525545",
fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
],
Headers=[Authorization="Bearer **Your Token here**"]]
) ,
Json = Json.Document(RawData)
in Json,
// GetJiraIDCount: Function to determine the total number of available Jira tickets for a filter ID.
GetJiraIDCount = () =>
let Url = Web.Contents(
"https://XXX.XXX.net/jira/rest/api",
[
RelativePath="2/search",
Query=
[
jql="filter=525545",
fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
],
Headers=[Authorization="Bearer **Your Token here**"]]
) ,
Json = GetJson(Url),
Count = Json[#"total"]
in Count,
//GetPage function: Retrieves a page of Jira tickets based on the specified index.
// - Index: The page number, starting at 0.
// - Skip: Skips a certain number of Jira tickets based on the page number.
// - Top: Specifies the maximum number of tickets to return per page.
// - Url: The full URL for the API request based on BaseUrl, Skip and Top.
// - Json: Retrieves data from the Jira API and returns it as JSON.
// - Value: Extracts the relevant information (under 'issues') from the JSON.
GetPage = (Index) =>
let
Skip = Text.From(Index * JiraIDPerPage),
Top = Text.From(JiraIDPerPage),
PageData = Web.Contents(
"https://XXX.XXX.net/jira/rest/api",
[
RelativePath="2/search",
Query=
[
jql="filter=525545",
startAt=Skip,
maxResults=Top,
fields="issuetype,key,summary,customfield_10401,labels,customfield_10000,status,resolution,customfield_11200"
],
Headers=[Authorization="Bearer **Your Token here**"]
]
),
Json = Json.Document(PageData),
Value = Json[#"issues"]
in Value,
// JiraIDCount: Calculates the total number of Jira tickets and selects the maximum number per page.
JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
// PageCount: Calculates the total number of pages based on the number of tickets per page.
PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
// PageIndices: List of page indices.
PageIndices = { 0 .. PageCount - 1 },
// Pages: Retrieves the corresponding page of Jira tickets for each page index.
Pages = List.Transform(PageIndices, each GetPage(_)),
// JiraID: Combines the pages into a complete list of Jira tickets.
JiraID = List.Union(Pages),
// Table: Creates a Power Query table from the list of Jira tickets.
Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//From here data transformation
....
Hope this post helps you!
Alina
