Forum Discussion
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 like how can i call the API in loop using powerBI Mquery.?
here i want to pass {subscriptionId} in looping with subscriptionsList.Any sample example please suggest.
Thanks
Satya
7 Replies
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.
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.
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
