Call Azure API with nextLink next page in Power BI

0 votes

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.

Nov 12, 2018 in Power BI by Shubham
• 13,490 points
1,687 views

1 answer to this question.

0 votes

Here is something you could try .

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
answered Nov 12, 2018 by Upasana
• 8,620 points

Related Questions In Power BI

0 votes
1 answer

I have a web API with basic authorisation and username and password. How do i connect to it in Power BI?

Hey, @There, I found your query is quite ...READ MORE

answered Oct 29, 2020 in Power BI by Gitika
• 65,770 points
3,554 views
0 votes
1 answer

how can I connect my web api with Power BI Reports?

Go to Home > Edit Queries > ...READ MORE

answered Oct 18, 2018 in Power BI by Annie97
• 2,160 points
9,596 views
+1 vote
1 answer

Have column with multiple values in powerBI, for use with a slicer in power bi

It can be achieved depending on the result you ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,520 points
7,422 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,520 points
1,608 views
0 votes
1 answer

Calling Azure API using Power BI with Next Link

Try this let iterations = ...READ MORE

answered Apr 9, 2019 in Power BI by Shubham
• 13,490 points
1,888 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
1,290 views
0 votes
1 answer

How to assign Power BI license to a new user

The assign license method of MS Graph API might ...READ MORE

answered Sep 25, 2018 in Power BI by Kalgi
• 52,350 points
863 views
0 votes
1 answer

Where is Power Bi being hosted?

Power BI is currently cloud hosted by ...READ MORE

answered Sep 28, 2018 in Power BI by Kalgi
• 52,350 points
1,044 views
0 votes
1 answer

Error:connecting to Azure SSAS server in Power-BI through Import mode

If I understand correctly, you're connecting to ...READ MORE

answered Nov 9, 2018 in Power BI by Upasana
• 8,620 points
2,658 views
0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 8,620 points
1,406 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP