Forum Discussion
Need suggestion on PowerBI-MQuery Looping for calling the API
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/virtualMachines?api-version=2021-07-01)),
#"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
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?
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 BIccountant
Hope this helps.
If not, please come back.
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.
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.
