Call Azure API with nextLink next page in Power BI

Question

I'm new to Power BI. I'm using Power BI to call an Azure API that will list all the VMs in my subscription, however, it will only show the first 50 before having a nextLink.

Here is the API I'm calling;

https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?api-version=2017-12-01

I've seen other pages and forums with a similar issue (such as Microsoft API), but not for Azure API. I messed about with their fix, but could not work out how to apply it to mine.

Their code;

let
GetUserInfo = (Path)=>
let
     Source = Json.Document(Web.Contents(Path)),
     LL= @Source[value], 
     result = try @LL & @GetUserInfo(Source[#"@odata.nextLink"]) otherwise @LL

in
result,
    Fullset = GetUserInfo("https://graph.microsoft.com/beta/users?$select=manager&$expand=manager"),
    #"Converted to Table" = Table.FromList(Fullset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "displayName", "manager"}, {"Column1.id", "Column1.displayName", "Column1.manager"}),
    #"Expanded Column1.manager" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.manager", {"id", "displayName"}, {"id", "displayName"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.manager",{{"Column1.displayName", "Employee Full Name"}, {"Column1.id", "Employee Id"}, {"id", "Manager Id"}, {"displayName", "Manager Full name"}})
in
    #"Renamed Columns"

Compared to the start of mine once I've connected the source by the simple web link;

let
    Source = Json.Document(Web.Contents("https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?api-version=2017-12-01")),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

If I were to adjust it, I suspected it would look something like this;

let
GetUserInfo = (Path)=>
let
     Source = Json.Document(Web.Contents(Path)),
     LL= @Source[value], 
     result = try @LL & @GetUserInfo(Source[#"@odata.nextLink"]) otherwise @LL

in
result,
    Fullset = GetUserInfo("https://management.azure.com/subscriptions/< subscription >/providers/Microsoft.Compute/virtualMachines?api-version=2017-12-01"),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

However, I am prompted with the following error once clicking OK;

Expression.Error: The name 'Source' wasn't recognized.  Make sure it's spelled correctly.

Any help is appreciated.

Answer 1

Here is something you could try or you could simply follow this link.

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(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