Forum Discussion

Satya_Machari's avatar
Satya_Machari
Helper I
4 years ago

Need suggestion on PowerBI-MQuery Looping for calling the API

Hi All, I am working on one requirement like :Get the total virtual mechines data by calling one API in loop passing subscriptionid to the api. can you some one please suggest/provide any sampe lik...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

I was anticipating that you will get a "table" as response. No probs, after the stage you have shown in the screenshot, add one more column using Table.AddColumns and use the function Record.ToTable() after "each" to covert the "Records" to "Tables". Refer to the following link

https://docs.microsoft.com/en-us/powerquery-m/record-totable 

This will convert the records to a table in your new column, which you will be able to expand.

After converting, you could remove the "API Response" column that you got as "records". You no longer need it. I hope it will solve the problem. 

 

Also, take a look at Table.FromRecords. I am not sure which one to use - Table.FromRecords() or Records.ToTable().

 

Hi ImkeF , Could you please help?

Sudharshan1919's avatar
Sudharshan1919
Helper III
4 years ago

Hello Sreenath,

 

This is regarding the subjected forum 

Re: Need suggestion on PowerBI-MQuery Looping for calling the API

 

Here i tried as the way you defined, but at the Step8, i didnt understand how do we give the loop. When i tried it is throwing error. (Step 8: Instead of the subscription ID that was hardcoded in the power query in Step 7's API call, generate the API call for each subscription ID using string concatenation. For example, "API Prefix part" + Record.Field(_,[subsriptionid]) + "API suffix part".).

Can you please explain me on the step 8. I have attached and Highlighted API Part. 

+Record.Field(_,[Column1])+ - Also please explain how do i define i this part 

 

 

I am getting the error as above image when expanding the cells individually.

Kindly please help me as this is very urgent for me.

 

 

Here when i am clicking on the "Record", i am getting the data as error.

When i am giving only the particular subscription in the M Query Code.. i am getting only that particular subscription data in all the cells.

I need the data for all the subscriptions and to expand them as a table at once, please provide me the way of how i can achieve it.

 

Kindly please respond at the earliest.

 

Thanks, 

Sudharshan.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Assume that the following are your subscription IDs.

    I am adding another column to this table to show how to generate the API URL. 

    = Table.AddColumn(#"Changed Type1","APIURL",each "abc" & Record.Field(_,"SubscriptionID") & "def")

    Are you able to generate this API URL for each Subscription ID? If yes, then loop through this table use this Subscription URL field to call the API. By the way, these are not exact steps or codes. Just showing some examples or patterns that you could explore to get the result.