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