Forum Discussion
Get data from Jira Rest API
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
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.
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
Thanks for your help, it's really great (and simple) solution.
Why I did not guess on my own.. :)
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"