cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 

 

 

1 REPLY 1
Highlighted
Helper II
Helper II

Re: REST API JIRA : worklog

hi,

define a function:

(_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 🙂
 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!