cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
berta2b Regular Visitor
Regular Visitor

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("https:// MY API CALL").

in

 Source

 

This dataset will then be imported into Power BI.

 

However in order to get my complete dataset I need to call the API a number of times by changing values in the name/value pairs of the Querystring of the API's UR so the source should behave like this,

 

let

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

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

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

in

 Source

Has anybody been able to get this working please?

 

The vendor has explained it's not possible to import all the data in one API request so I'm having to call it multiple times!

 

Any help or advice would be much appreciated.

 

Thanks.

4 REPLIES 4
Highlighted
bidgeir
Advisor

Re: Call a REST API multiple times using Power Query

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 Regular Visitor
Regular Visitor

Re: Call a REST API multiple times using Power Query

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!

jan_k123 Frequent Visitor
Frequent Visitor

Re: Call a REST API multiple times using Power Query

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! 

Jayendran Member
Member

Re: Call a REST API multiple times using Power Query

Hi @jan_k123 

 

Not sure this help. I've faced the similar issue, and got worked using Gateway and Skip connection

 

I published the entier blog, you can read it

 

https://community.powerbi.com/t5/Community-Blog/Power-BI-Gateway-Monitoring-amp-Administrating-Part-...

 

https://community.powerbi.com/t5/Community-Blog/Power-BI-Gateway-Monitoring-amp-Administrating-Part-...

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 30 members 709 guests
Please welcome our newest community members: