Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm using M query mentioned below to fetch VM's in an Azure Subscription using REST API from Virtual Machines - List All - REST API (Azure Compute) | Microsoft Docs.
Learnt that this is a dynamic data source and does not support automatic refresh after publishing it to power bi service.
let iterations = 10, url = "https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?api-version=2021-03-01", FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents(url)), data = try Source[value] otherwise null, next = try Source[nextLink] otherwise null, res = [Data=data, Next=next] in res, GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [i]<iterations and [res][Data]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [res][Data]) in GeneratedList
Tried to use 'RelativePath' and 'Query'. This url does not return all results from the subscription. Not sure what I'm missing here.
let iterations = 10, url = "https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?api-version=2017-12-01", FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/",
[RelativePath="virtualMachines?",
Query=[#"api-version"="2021-03-01"]])), data = try Source[value] otherwise null, next = try Source[nextLink] otherwise null, res = [Data=data, Next=next] in res, GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [i]<iterations and [res][Data]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [res][Data]) in GeneratedList
Still trying to find my feet in Power BI. Hence seeking help.
Thanks,
SV
base URL(*)
https://management.azure.com
Relative path
subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines
Query
api-version=2017-12-01
* - must be accessible for authentication.
Hi @lbendlin ,
Thanks for your response. I have updated the url as suggested. VM's from some of the resource groups are still missing. This does not seem to be a permission issue as I can see them when I log into Azure portal.
Could you help me with adding 'RelativePath' and 'Query' in the value assigned to 'url' variable.
Is there any thing else that I can check or other part of the query that requires a update to get all the VM's from the subscription.
let iterations = 10, url = "https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?api-version=2017-12-01", FnGetOnePage = (url) as record => let Source = Json.Document(Web.Contents("https://management.azure.com/",
[RelativePath="subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?",
Query=[#"api-version"="2021-03-01"]])), data = try Source[value] otherwise null, next = try Source[nextLink] otherwise null, res = [Data=data, Next=next] in res, GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [i]<iterations and [res][Data]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [res][Data]) in GeneratedList
Thanks,
SV
You still have a question mark in the relative path
I tried without '?' mark. No change in the outcome.
Thanks,
SV
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.