Forum Discussion

berta2b's avatar
berta2b
Helper I
10 years ago

Call a REST API multiple times using Power Query

  I am loading a Data Model using Power Query via a RESTful API call returning JSON data so when you look at the code generated it's something like   let  Source = Json.Document(Web.Contents("htt...
bidgeir's avatar
bidgeir
Most Valuable Professional
10 years ago

You can create a function from your query using this syntax:

let functionName = (param1 as datatype) =>

let

 Source = Json.Document(Web.Contents("https:// MY API CALL" & param1).

in

 Source

in functionName

 

You can then create a table with your parameters and then add a custom column to your table that calls/invokes your function

 

You can see an example here http://datachix.com/2014/05/22/power-query-functions-some-scenarios/ and here http://satalyst.com/power-query-creating-a-parameterized-function-in-m/

Vitaliy's avatar
Vitaliy
Helper I
7 years ago

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

..................................................                                                                   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!