Get data from Jira Rest API

[Vitaliy]

Hi 2 all and sorry for my English!

I have a question that has been torturing for 2 days already. I have a table MYTABLE with 2 columns (URL and Index)of the form:

URL	Index
jira.test.com/rest/api/2/issue/MYPROJECTKEYNAME-10/worklog	0
jira.test.com/rest/api/2/issue/MYPROJECTKEYNAME-11/worklog	1
etc	1999

In this table I have 2000 records. My goal is to get the data stored in these URLs.

For this I use the following command:

#"MyNewTable"= Table.AddColumn(#"MYTABLE", "DataFromURLsColumn", each Json.Document(Web.Contents(#"MYTABLE"{[Index]}[URL])))

, but this request is very difficult and takes a very long time. So I must wait approximately 20-30 minutes or more.

My questions is next:

1. Is there a faster way to get this data stored in the URL?
2. Perhaps there are some global or other settings in the Power BI?
3. Does this command work so slowly due to the number of queries to jira rest api, or maybe because I'm adding a new calculated column to the table?
I will be very grateful for any help, thank you in advance!

[v-jiascu-msft]

Hi @Vitaliy,

I tested like your scenario. It took only several minutes for 10 thousand rows. You can check it out in the attachment. Maybe you can try Python or R scripts. If you need more support, please provide the complete Power Query code. 

Best Regards,

Dale

[antonstorozhuk]

Hello, 

I’d like to propose and alternative solution for you, you might want to try out our app - Power BI Connector for Jira: https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=cloud&tab=overvie... it's available for Jira Cloud / Server / Data Center.

We have a handy User/Admin Guide https://aserve.atlassian.net/wiki/spaces/PBCFJC/overview and fast/responsive support team at support@alpha-serve.com. Please let us know if you need any assistance.

Cheers!

Anton

===
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vitaliy]

Hi!

thanks for the time spent and your response.

hmm..due to this fact, I don't understand what is the matter ((
perhaps, part of my code will help clarify the situation:

I have query:

let
S1 = P1_epics_only,
    #"Added Custom" = Table.AddColumn(#"S1", "first_part_of_url", each "https://jira.mycompany.com/rest/api/2/issue/"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "third_part_of_url", each "/worklog"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each [first_part_of_url]&[key_main]&[third_part_of_url]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each true),
    #"AddIndex" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
#"Source"= Table.AddColumn(#"AddIndex", "Custom1", each Json.Document(Web.Contents(#"AddIndex"{[Index]}[Custom]))),
#"ExpandWorklogs"= Table.ExpandRecordColumn(#"Source", "Custom1", {"worklogs"}),
#"ExpandWorklogs1"= Table.ExpandListColumn(#"ExpandWorklogs", "worklogs"),
#"Expand" = Table.ExpandRecordColumn(#"ExpandWorklogs1", "worklogs", {"self", "author", "updateAuthor", "comment", "created","updated","started","timeSpent","timeSpentSeconds","id"}, {"self", "author", "updateAuthor", "comment", "created","updated","started","timeSpent","timeSpentSeconds","id"}),
#"ExpandWorklogsAuthor"= Table.ExpandRecordColumn(#"Expand", "author", {"name"}),
#"Renamed Columns1" = Table.RenameColumns(ExpandWorklogsAuthor,{{"name", "name_author"}}),
#"ExpandWorklogsUpdateAuthor"= Table.ExpandRecordColumn(#"Renamed Columns1", "updateAuthor", {"name"}),
    #"Filtered Rows1" = Table.SelectRows(ExpandWorklogsUpdateAuthor, each true)
in
    #"Filtered Rows1"

P1_epics_only - this is a table with one column (with "key_name" name), where stored the list of epic from jira (something like "variable")

So, on the 8th line of the code begins the same problem that I wrote about - lines are loaded at a rate of "one line per second" ((

__________________
I know Python a little, but he never used it for Power BI, which I started using 2 weeks ago)

Maybe I'll turn to this solution if I do not solve my question more understandable variant at the moment.

[v-jiascu-msft · Accepted solution]

Hi @Vitaliy,

I have found a performance issue in your code. Though I don't know if other parts like Jira API could influence the performance. It could run faster. I tested with 100 thousand rows this time. It even finished in seconds. Please give it a try.

let   
    //other parts are good without cahnges. 
    ... ...
    #"Source"= Table.AddColumn(#"AddIndex", "Custom1", each Json.Document(Web.Contents([Custom]))),
    ... ...
in
    #"Filtered Rows1"

Why? The old code invoked a table which should be avoided in Power Query. 

Best Regards,

Dale

[Anonymous]

I met this same problem, but can not solved by this solution.
@v-jiascu-msft, @Vitaliy , did you test with JIRA-RESET-API. 
My code:

let
    Source = Json.Document(Web.Contents( #"my company JIRA",
        [
            RelativePath="/rest/api/2/search",
            Query=[
                maxResults="1000", 
                jql=#"JQL EPICS",
                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(#"JQL EPICS", "key", [Column1])),
    #"Expanded Data" = Table.ExpandTableColumn(#"Invoked Custom Function", "Data", {"Column1.key"}, {"Column1.key"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Data", {"Column1.key"}),
    parent = Table.RenameColumns(#"Removed Duplicates",{{"Column1.key", "ParentKey"}}),
    nbr = List.Count(parent[ParentKey])-1,
    total = Number.Round(nbr/#"EPICS COUNT PER PAGE" + 0.5 - 1, 0,  RoundingMode.Up), //Using #"EPICS COUNT PER PAGE"(currently 30) in case url too long 
    List99 = List.Transform({0..total}, each _ * #"EPICS COUNT PER PAGE"),
    #"Converted to Table99" = Table.FromList(List99, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type99" = Table.TransformColumnTypes(#"Converted to Table99",{{"Column1", type number}}),
    #"Invoked Custom Function2" = Table.AddColumn(#"Changed Type99", "Data", each GetJIRADCChildren(Text.Combine(List.Range(parent[ParentKey], [Column1], #"EPICS COUNT PER PAGE"), ","), #"FIELDS TASK")),
    #"Removed Columns2" = Table.RemoveColumns(#"Invoked Custom Function2",{"Column1"}),
    #"Expanded Data99" = Table.ExpandTableColumn(#"Removed Columns2", "Data", {"Column1"}, {"Column1"}),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Data99", "Column1", {"key", "fields"}, {"key", "fields"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Column1",{{"key", "Issue key"}}),
    #"Removed Duplicates99" = Table.Distinct(#"Renamed Columns2", {"Issue key"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Removed Duplicates99", "fields", {"summary", "customfield_12790", "status"}, {"summary", "customfield_12790", "status"}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Expanded fields", "status", {"name"}, {"status"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded status",{{"customfield_12790", "ParentKey"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    //#"Invoked Custom Function1" = Table.AddColumn(#"Added Index", "worklog", each GetWorklog([Issue key]))
    #"Add worklog url" = Table.AddColumn(#"Added Index", "worklog url", each #"my company JIRA"&"/rest/api/latest/issue/"&[Issue key]&"/worklog"),
    #"Invoked Custom Function1" = Table.AddColumn(#"Add worklog url", "worklog", each Json.Document(Web.Contents([worklog url])))
in
    #"Invoked Custom Function1"

[Vitaliy]

Hi @v-jiascu-msft

Thanks for your help, it's really great (and simple) solution.

Why I did not guess on my own.. 🙂