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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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