cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vitaliy
Helper I
Helper I

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:

URLIndex
jira.test.com/rest/api/2/issue/MYPROJECTKEYNAME-10/worklog0
jira.test.com/rest/api/2/issue/MYPROJECTKEYNAME-11/worklog1
etc1999

 

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!

1 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Microsoft
Microsoft

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

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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"

Hi @v-jiascu-msft

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

Why I did not guess on my own.. 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors