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/

jan_k123's avatar
jan_k123
Frequent Visitor
7 years ago

Hi bidgeir berta2b 

Thanks for this solution.

I got it working feeding off a REST API service. Then I wanted it to be scheduled on Power BI Service. But got this error:

Query contains unsupported function. Function name: Web.Contents

 

I have used 2 parameters:  (start as text, count as text) => 

Then I applied this against a list:  = List.Generate(()=>0, each _ < 67000, each _ + 1000) 

In a new query i start a custom function with Column1 = the List function and Column2 = 1000. After that I add the web-query which has the start and count params. 

The result should be it gets 1000 records at a time, otherwise it fails/time-out. 

 

Can you help me with the web-query refresh on PBI Service? (I am using basic auth with user and pass)

Thanks!