Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Florian89
New Member

REST API JIRA : worklog

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 

 

 

3 REPLIES 3
Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.