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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.