Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 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
Hello
Thank you very much for your help, it now gives me a message in the power query that says
"Expression.Error: Access to the resource is prohibited."
Do you know what this could be due to?
Hi @Maur0s
First of all, I would assume that your authorisation was incorrect. Perhaps the Bearer authentication type is not the correct one for accessing your Jira. An alternative would be basic authentication.
But can you perhaps provide your code?
HI @Alina12,
Thanks for your sharing, I think these sample code will help other who have the similar requirement.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |