Forum Discussion

Vitaliy's avatar
Vitaliy
Helper I
7 years ago
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:

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!

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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

     

    • Vitaliy's avatar
      Vitaliy
      Helper I
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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

    • antonstorozhuk's avatar
      antonstorozhuk
      Helper V
      3 years ago

      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=overview 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 [email protected]. 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.