Need suggestion on PowerBI-MQuery Looping for calling the API

[Satya_Machari]

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 like  how can i call the API in loop using powerBI Mquery.?

api:https://management.azure.com/subscriptions/{subscriptionId}/providers/Microsoft.Compute/virtualMachi....

here i want to pass {subscriptionId} in looping with subscriptionsList.Any sample example please suggest.

Thanks

Satya

[v-shex-msft]

Hi @Satya_Machari,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Satya_Machari]

Thank you Sreenathv,

I have small confusion at Step:8.Can you please explain in detail.

We are able to run the code as detailed in the above Steps. At Step 8 we are struck and need your intervention here. 

We are getting all the subscriptions, data is comming in a New Columns as "API Response". But we need to expand each and every subscription data, while expanding the rows we are not able to expand all the rows, only one record can be done at that time and the remaining we are not able to do.

Attached the MQuery Code and screenshot Please sugest.

MQuery:

let

    Source = Excel.Workbook(File.Contents("C:\Users\TestUser\Desktop\Looping.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),

    #"NewColumn" = Table.AddColumn(#"Changed Type", "API Response", each [

    Source = Json.Document(Web.Contents(https://management.azure.com/subscriptions/MYSUBSCRIPTIONID-1/providers/Microsoft.Compute/virtualMac...)),

 

 

    #"Converted to Table" = Table.FromRecords({Source}),

    #"Expanded value" = Table.ExpandListColumn(#"Converted to Table", "value"),

    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"name", "id", "type", "location", "tags", "identity", "properties", "resources"}, {"value.name", "value.id", "value.type", "value.location", "value.tags", "value.identity", "value.properties", "value.resources"}),

    #"Expanded value.tags" = Table.ExpandRecordColumn(#"Expanded value1", "value.tags", {"Cost Center", "AVD", "CreatedOnDate", "Financial Owner", "Service Identifier", "Data Classification", "Financial owner", "Hosting Environment", "Service Owner", "Application Name", "HFM Code", "DMZ", "Service identifier", "Service owner", "Technical contact"}, {"value.tags.Cost Center", "value.tags.AVD", "value.tags.CreatedOnDate", "value.tags.Financial Owner", "value.tags.Service Identifier", "value.tags.Data Classification", "value.tags.Financial owner", "value.tags.Hosting Environment", "value.tags.Service Owner", "value.tags.Application Name", "value.tags.HFM Code", "value.tags.DMZ", "value.tags.Service identifier", "value.tags.Service owner", "value.tags.Technical contact"}),

    #"Expanded value.identity" = Table.ExpandRecordColumn(#"Expanded value.tags", "value.identity", {"type", "principalId", "tenantId"}, {"value.identity.type", "value.identity.principalId", "value.identity.tenantId"}),

    #"Expanded value.properties" = Table.ExpandRecordColumn(#"Expanded value.identity", "value.properties", {"vmId", "hardwareProfile", "storageProfile", "osProfile", "networkProfile", "diagnosticsProfile", "licenseType", "provisioningState"}, {"value.properties.vmId", "value.properties.hardwareProfile", "value.properties.storageProfile", "value.properties.osProfile", "value.properties.networkProfile", "value.properties.diagnosticsProfile", "value.properties.licenseType", "value.properties.provisioningState"}),

   #"Expanded value.properties.hardwareProfile" = Table.ExpandRecordColumn(#"Expanded value.properties", "value.properties.hardwareProfile", {"vmSize"}, {"value.properties.hardwareProfile.vmSize"}),

    #"Expanded value.properties.storageProfile" = Table.ExpandRecordColumn(#"Expanded value.properties.hardwareProfile", "value.properties.storageProfile", {"imageReference", "osDisk", "dataDisks"}, {"value.properties.storageProfile.imageReference", "value.properties.storageProfile.osDisk", "value.properties.storageProfile.dataDisks"}),

    #"Expanded value.properties.storageProfile.imageReference" = Table.ExpandRecordColumn(#"Expanded value.properties.storageProfile", "value.properties.storageProfile.imageReference", {"publisher", "offer", "sku", "version", "id", "exactVersion"}, {"value.properties.storageProfile.imageReference.publisher", "value.properties.storageProfile.imageReference.offer", "value.properties.storageProfile.imageReference.sku", "value.properties.storageProfile.imageReference.version", "value.properties.storageProfile.imageReference.id", "value.properties.storageProfile.imageReference.exactVersion"}),

    #"Expanded value.properties.storageProfile.osDisk" = Table.ExpandRecordColumn(#"Expanded value.properties.storageProfile.imageReference", "value.properties.storageProfile.osDisk", {"osType", "name", "createOption", "caching", "managedDisk", "deleteOption", "diskSizeGB"}, {"value.properties.storageProfile.osDisk.osType", "value.properties.storageProfile.osDisk.name", "value.properties.storageProfile.osDisk.createOption", "value.properties.storageProfile.osDisk.caching", "value.properties.storageProfile.osDisk.managedDisk", "value.properties.storageProfile.osDisk.deleteOption", "value.properties.storageProfile.osDisk.diskSizeGB"}),

    #"Expanded value.properties.storageProfile.osDisk.managedDisk" = Table.ExpandRecordColumn(#"Expanded value.properties.storageProfile.osDisk", "value.properties.storageProfile.osDisk.managedDisk", {"storageAccountType", "id"}, {"value.properties.storageProfile.osDisk.managedDisk.storageAccountType", "value.properties.storageProfile.osDisk.managedDisk.id"}),

    #"Expanded value.properties.osProfile" = Table.ExpandRecordColumn(#"Expanded value.properties.storageProfile.osDisk.managedDisk", "value.properties.osProfile", {"computerName", "adminUsername", "windowsConfiguration", "secrets", "allowExtensionOperations", "requireGuestProvisionSignal"}, {"value.properties.osProfile.computerName", "value.properties.osProfile.adminUsername", "value.properties.osProfile.windowsConfiguration", "value.properties.osProfile.secrets", "value.properties.osProfile.allowExtensionOperations", "value.properties.osProfile.requireGuestProvisionSignal"}),

    #"Expanded value.properties.osProfile.windowsConfiguration" = Table.ExpandRecordColumn(#"Expanded value.properties.osProfile", "value.properties.osProfile.windowsConfiguration", {"provisionVMAgent", "enableAutomaticUpdates", "patchSettings"}, {"value.properties.osProfile.windowsConfiguration.provisionVMAgent", "value.properties.osProfile.windowsConfiguration.enableAutomaticUpdates", "value.properties.osProfile.windowsConfiguration.patchSettings"}),

    #"Expanded value.properties.osProfile.windowsConfiguration.patchSettings" = Table.ExpandRecordColumn(#"Expanded value.properties.osProfile.windowsConfiguration", "value.properties.osProfile.windowsConfiguration.patchSettings", {"patchMode", "assessmentMode", "enableHotpatching"}, {"value.properties.osProfile.windowsConfiguration.patchSettings.patchMode", "value.properties.osProfile.windowsConfiguration.patchSettings.assessmentMode", "value.properties.osProfile.windowsConfiguration.patchSettings.enableHotpatching"}),

    #"Expanded value.properties.networkProfile" = Table.ExpandRecordColumn(#"Expanded value.properties.osProfile.windowsConfiguration.patchSettings", "value.properties.networkProfile", {"networkInterfaces"}, {"value.properties.networkProfile.networkInterfaces"}),

    #"Expanded value.properties.diagnosticsProfile" = Table.ExpandRecordColumn(#"Expanded value.properties.networkProfile", "value.properties.diagnosticsProfile", {"bootDiagnostics"}, {"value.properties.diagnosticsProfile.bootDiagnostics"}),

    #"Expanded value.properties.diagnosticsProfile.bootDiagnostics" = Table.ExpandRecordColumn(#"Expanded value.properties.diagnosticsProfile", "value.properties.diagnosticsProfile.bootDiagnostics", {"enabled"}, {"value.properties.diagnosticsProfile.bootDiagnostics.enabled"}),

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded value.properties.diagnosticsProfile.bootDiagnostics",{{"value.name", type text}, {"value.id", type text}, {"value.type", type text}, {"value.location", type text}, {"value.tags.Cost Center", Int64.Type}, {"value.tags.AVD", type text}, {"value.tags.CreatedOnDate", type datetime}, {"value.tags.Financial Owner", type text}, {"value.tags.Service Identifier", type text}, {"value.tags.Data Classification", type text}, {"value.tags.Financial owner", type text}, {"value.tags.Hosting Environment", type text}, {"value.tags.Service Owner", type text}, {"value.tags.Application Name", type text}, {"value.tags.HFM Code", Int64.Type}, {"value.tags.DMZ", type text}, {"value.tags.Service identifier", type text}, {"value.tags.Service owner", type text}, {"value.tags.Technical contact", type text}, {"value.identity.type", type text}, {"value.identity.principalId", type text}, {"value.identity.tenantId", type text}, {"value.properties.vmId", type text}, {"value.properties.hardwareProfile.vmSize", type text}, {"value.properties.storageProfile.imageReference.publisher", type text}, {"value.properties.storageProfile.imageReference.offer", type text}, {"value.properties.storageProfile.imageReference.sku", type text}, {"value.properties.storageProfile.imageReference.version", type text}, {"value.properties.storageProfile.imageReference.id", type text}, {"value.properties.storageProfile.imageReference.exactVersion", type text}, {"value.properties.storageProfile.osDisk.osType", type text}, {"value.properties.storageProfile.osDisk.name", type text}, {"value.properties.storageProfile.osDisk.createOption", type text}, {"value.properties.storageProfile.osDisk.caching", type text}, {"value.properties.storageProfile.osDisk.managedDisk.storageAccountType", type text}, {"value.properties.storageProfile.osDisk.managedDisk.id", type text}, {"value.properties.storageProfile.osDisk.deleteOption", type text}, {"value.properties.storageProfile.osDisk.diskSizeGB", Int64.Type}, {"value.properties.storageProfile.dataDisks", type any}, {"value.properties.osProfile.computerName", type text}, {"value.properties.osProfile.adminUsername", type text}, {"value.properties.osProfile.windowsConfiguration.provisionVMAgent", type logical}, {"value.properties.osProfile.windowsConfiguration.enableAutomaticUpdates", type logical}, {"value.properties.osProfile.windowsConfiguration.patchSettings.patchMode", type text}, {"value.properties.osProfile.windowsConfiguration.patchSettings.assessmentMode", type text}, {"value.properties.osProfile.windowsConfiguration.patchSettings.enableHotpatching", type logical}, {"value.properties.osProfile.secrets", type any}, {"value.properties.osProfile.allowExtensionOperations", type logical}, {"value.properties.osProfile.requireGuestProvisionSignal", type logical}, {"value.properties.networkProfile.networkInterfaces", type any}, {"value.properties.diagnosticsProfile.bootDiagnostics.enabled", type logical}, {"value.properties.licenseType", type text}, {"value.properties.provisioningState", type text}, {"value.resources", type any}, {"nextLink", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"value.tags.Financial owner", "value.tags.Financial owner.1"}, {"value.tags.Service identifier", "value.tags.Service identifier.1"}, {"value.tags.Service owner", "value.tags.Service owner.1"}, {"value.name", "VM Name"}})

], type text)

in

    NewColumn

[Anonymous]

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]

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]

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.

[ImkeF]

Hi there,

I've written an article about this a while ago: How to expand a column that cannot be expanded in Power BI and Power Query in Excel – The BIccountan...

Hope this helps.

If not, please come back.

[Anonymous]

It is possible to explicitly write the entire power query to achieve this. But if you are new to power query, I would suggest the following..

Step 1: Store the subscriptions ids in some file (csv, excel etc..) or in a database table.

Step 2: Import the list of subscription ids into Power BI desktop. Copy the power query and paste it in a text editor and keep it aside for future use.

Step 3: Take any one subscription ID as a sample and create a new power query to call the API using this subscription id and fetch the response.

Step 4: Copy the power query generated in Step 3 and keep it aside for future use.

Step 5: Open a blank query, paste the Power Query generated in Step 2.

Step 6: Open this query in advance editor, add a step to add a new column using Table.AddColumn() function.

Step 7: While adding the column, after giving the new column a name like "API Response", after the "each", paste the second power query generated in Step 5.

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".

Step 9: If you have done correctly till now, upon execution, the query will fetch the API response for each subscription id and put the response of each subscription id in a table cell.

Step 10: Expand the column contents, you will have your output.

If you have any trouble, please reply with two autogenerated power queries (in Step 2 and 3), I will combine them and post the completed query. Unless you give some code, it is difficult to help.