Power BI - Call Azure API with nextLink (next page)

Question

Apologies, 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 on this would be greatly appreciated.

Garald

Answer 1

For anyone interested, here is what I ended up doing thanks to this link:

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

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

1 whole day of Googling headache :S

Garald

The only thing I could suggest is to state that your query returns a list of lists and that you would need to convert to table and then click on the column heading to expand out the rows until you get to the data values. Just so a complete Power BI novice doesn't have to keep Googling. – Bumper