Forum Discussion
Solved
Get data from Jira Rest API
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/r...
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 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
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"