Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi everyone,
I am working for the first time with the JIRA API and PowerBI.
My goal : I want to extract all detailed worklogs from JIRA to build a dashboard for the team.
My issue : the query I am currently using just extract the consolidated time on a ticket. However I would need the detail by user.
The query I am currently using :
Json.Document(Web.Contents("https://XXX.atlassian.net/rest/api/2/searchstartAt=0&maxResults=100&jql=project=""YYYY""&expand=,nam...")),
Anyone knows which parameters should I insert to extract the complete worklog table?
Moreover, do you know how to extract more than 100 rows at the same time? For now I am using a main query which combined sereal query with hundred results rows.
Thank you
hi,
define a function named getJIRADCPage:
(_jql as text, _fields as text, _startAt as text) =>
let
Source = Json.Document(
Web.Contents(
"you company jira url",
[
RelativePath="/rest/api/2/search",
Query=[
maxResults="1000",
jql=_jql,
fields=_fields,
startAt=_startAt
]
]
)),
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", "Column1.key", "Column1.fields"})
in
#"Expanded Column1"
query body:
let
Source = Json.Document(Web.Contents( "your company jira url",
[
RelativePath="/rest/api/2/search",
Query=[
maxResults="1000",
jql="author=xxx",
fields="key",
startAt="0"
]
])),
total1 = Number.Round(Source[total]/1000 + 0.5-1, 0, RoundingMode.Up),
List = List.Transform({0..total1}, each _ * 1000),
#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each GetJIRADCPage("author=xxx", "field1, field2", [Column1])), #till here, all issues belong to autor:xxx are gotten
#"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Column1.key"}, {"Column1.key"}),
#"Add worklog url" = Table.AddColumn(#"Expanded Data", "worklog url", each "https://your company jira url/rest/api/latest/issue/"&[Column1.key]&"/worklog"),
#"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url])))
in
#"Invoked Custom Function1"
then shape data as you want 🙂
Hi.
Can you explain idea abot this first funkcion?
Whi this is neeade and how I call it later?
regards
purpose of this first function is we can tell JIRA get data starting at any index which specifed by _startAt as third parameter of this function.
total1 = Number.Round(Source[total]/1000 + 0.5-1, 0, RoundingMode.Up), //source[total] is the numbe of rows gottent from JIRA. so this line make for example total1=0 when total==600, total1=1 when total==1001
List = List.Transform({0..total1}, each _ * 1000), //we will have list{0,1} for example when total=1001.
#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error), //convert list{0, 1} to table with only one column but 2 rows with row0=0, row1=1.
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), //change int to type since we define _startAt as text in function.
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each GetJIRADCPage("author=xxx", "field1, field2", [Column1])), //then we call function we defined for each row of table we generated and specify _startAt by value of Column1 of this table.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
4 | |
3 | |
3 | |
3 | |
3 |