Script for Power Query

[Junaid11]

Hello,

I am looking to have a script written in power query in Power BI which reads an excel sheet, makes a call to an API and populates the table. Is this possible to achieve? Help would be appreciated.

Thank you

[v-jingzhang]

Hi @Junaid11 

What do you read from an Excel sheet? To get data from Excel, you can use Excel Workbook connector. To get data from an API, you can use Web connector. You can also write M codes to provide more custom information to Web.Contents() function to call an API. 

If you store variables (to be used in API) in a column in Excel, you can first connect to this Excel file to get data. Then add a column by invoking a custom function to call an API. This new column will have all data returned by API. Expand it to see all data in the table. 

Here are some tutorial videos for your reference. 

Use Power BI to loop through multiple pages of an API - YouTube

Scrape Data from Multiple Web Pages with Power Query - YouTube

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[KNP]

Yes

[Junaid11]

Hello @KNP ,

May I know any source from where I can see that practically been done. It woukd be quite useful.

Thank you

[KNP]

Hi @Junaid11,

Would you be able to provide more detail about your specific issue so I can better answer your question?

This post may be useful in helping you structure a question to better get the support you need.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...

[Junaid11]

Hello @KNP ,

May I know any source from where I can see that practically been done. It woukd be quite useful.

Thank you