dynamic data source - REST API

[suni_v]

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

[lbendlin]

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.

[suni_v]

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

[lbendlin]

You still have a question mark in the relative path 

[suni_v]

I tried without '?' mark. No change in the outcome.

Thanks,

SV