How to dynamically get data

[Nefertari]

Hello all,

I am seeking your kind help for GET data from a web site.

Currently I've make a GET request to have a table data from this web( see snapshot), now I want to get more info from this URL (https://xxx.com/Record/90b30903-1ea1-43c7-ba2c-ea2b73453839 ) , the problem is how do I write this query to dynamically GET data per each ID ?

Appreciate the help!

[Vijay_Chethan · Accepted solution]

Hello Nefertari,
you could create a function in power query having id as arguments and write the api call in function
then in the table, create a custom column and call this function

also in this method and also when using api's , this is considered as dynamic data to power bi to you might not be able to refresh the dataset once you wish to push it to power bi service

if this helps, please mark as solution

[v-kpoloju-msft · Accepted solution]

Hi @Nefertari,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @Vijay_Chethan, for your inputs on this issue.

Please take the sample data and import it into Power BI Desktop. Then, click on 'Transform Data' to move to Power Query. Next, select 'Get Data' and choose 'Blank Query'.

To proceed, open the blank query and input your URL details along with the provided power query. After entering the information, click 'Done'. Ensure you replace the placeholder URL with your specific URL in the power query below.

(recordId as text) as record =>

let

    url = "https://xxx.com/Record/" & recordId,

    response = Web.Contents(url),

    json = Json.Document(response)

in

    json

Invoke the function in your table by returning to your table with the IDs. Add a custom column named RecordData with the formula:  fnGetRecordByID([Id]).
This will make a dynamic API call for each row. Click on the expand icon next to RecordData to extract fields into columns.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[Nefertari]

Appreciate your great and detailed solution. That's really do me a favor. 

[Vijay_Chethan · Accepted solution]

Hello Nefertari,
you could create a function in power query having id as arguments and write the api call in function
then in the table, create a custom column and call this function

also in this method and also when using api's , this is considered as dynamic data to power bi to you might not be able to refresh the dataset once you wish to push it to power bi service

if this helps, please mark as solution

[Nefertari]

Hello Vijay_Chethan,

Thank so much for your solution, that's useful to me.