Power BI - 使用 nextLink 调用 Azure API(下一页) [英] Power BI - Call Azure API with nextLink (next page)

问题描述

抱歉，我是 Power BI 的新手.我正在使用 Power BI 调用 Azure API，该 API 将列出我订阅中的所有 VM，但它只会显示前 50 个，然后才会有 nextLink.

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.

这是我正在调用的 API；

Here is the API I'm calling;

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

我看到其他页面和论坛有类似的问题(例如 Microsoft API)，但不适用于 Azure API.我搞砸了他们的修复，但不知道如何将其应用于我的.

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.

他们的代码；

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.

推荐答案

对于任何感兴趣的人，感谢这个链接，这是我最终所做的:

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

一整天的谷歌搜索头痛:S

1 whole day of Googling headache :S

这篇关于Power BI - 使用 nextLink 调用 Azure API(下一页)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！