并购Power Query:如何在循环中使用 $Skip ODATA 表达式? [英] M & Power Query: How to use the $Skip ODATA expression within a loop?

问题描述

大家下午好，

我正在尝试调用 API 中的所有结果:

• 6640 条记录
• 每页 100 条记录
• 67 页结果(总记录/每页记录)
• 这是一个不断增长的列表，因此我使用变量来创建上述值.

我显然可以使用 $Skip ODATA 表达式来获取 67 个页面中的任何一个，方法是将表达式添加到 URL 的末尾，如下所示(这将跳过前 100 个页面，因此返回第二个页面:

https://psa.pulseway.com/api/servicedesk/tickets/?$Skip=100

我想要做的是创建一个自定义函数，该函数将遍历 67 个调用中的每一个，每次将 $Skip 值更改为 100.

我以为我已经用下面的代码实现了目标:

让令牌=令牌"，BaseURL = "https://psa.pulseway.com/api/",路径 = "servicedesk/tickets/",RecordsPerPage = 100，CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),TotalRecords = CountTickets[TotalRecords],GetJson = (Url) =>let Options = [Headers=[ #"Authorization";=承载者"&令牌]],RawData = Web.Contents(网址，选项)，Json = Json.Document(RawData)在 Json 中，GetPage = (索引) =>让 Skip = "$Skip=";&Text.From(Index * RecordsPerPage),URL = BaseURL &路径与?"&跳过，Json = GetJson(URL)在 Json 中，TotalPages = Number.RoundUp(TotalRecords/RecordsPerPage),PageIndicies = {0.. TotalPages - 1},Pages = List.Transform(PageIndicies，每个 GetPage(_))在页面

当它成功进行 67 次 API 调用并将结果组合成一个列表供我加载到 Power Query 表中时，我感到非常高兴，但我实际看到的是前 100 条记录重复了 67 次.

这告诉我处理 $Skip 值的 GetPage 自定义函数没有改变，而是停留在第一个函数上.为了确保 Skip 索引正确生成它们，我复制了查询并更改了代码以加载 $Skip 值并查看它们是什么，期望它们都是 $Skip=0，但我看到的是正确的 $Skip值如下:

Good afternoon all,

I'm trying to call all of the results within an API that has:

• 6640 total records
• 100 records per page
• 67 pages of results (total records / records per page)
• This is an ever growing list so I've used variables to create the above values.

I can obviously use the $Skip ODATA expression to get any one of the 67 pages by adding the expression to the end of the URL like so (which would skip the first 100, therefore returning the 2nd page:

https://psa.pulseway.com/api/servicedesk/tickets/?$Skip=100

What I'm trying to do though is to create a custom function that will loop through each of the 67 calls, changing the $Skip value by an increment of 100 each time.

I thought I'd accomplished the goal with the below code:

let 

    Token = "Token",
    BaseURL = "https://psa.pulseway.com/api/",
    Path = "servicedesk/tickets/",
    RecordsPerPage = 100,

    CountTickets = Json.Document(Web.Contents(BaseURL,[Headers = [Authorization="Bearer " & Token],RelativePath = Path & "count"])),
    TotalRecords = CountTickets[TotalRecords],


    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json = Json.Document(RawData)
        in  Json,

    GetPage = (Index) =>
        let Skip    = "$Skip=" & Text.From(Index * RecordsPerPage),
            URL     = BaseURL & Path & "?" & Skip,
            Json    = GetJson(URL)
        in  Json,

    TotalPages       = Number.RoundUp(TotalRecords / RecordsPerPage),
    PageIndicies    = {0.. TotalPages - 1},
    Pages           = List.Transform(PageIndicies, each GetPage(_))

in
    Pages

I got all happy when it successfully made the 67 API calls and combined the results into a list for me to load in to a Power Query table, however what I'm actually seeing is the first 100 records repeated 67 times.

That tells me that my GetPage custom function which handles the $Skip value isn't changing and is stuck on the first one. To make sure the Skip index was generating them properly I duplicated the query and changed the code to load in the $Skip values and see what they are, expecting them all to be $Skip=0, what I see though is the correct $Skip values as below:

Image showing correct Skip values

It seems everything is working as it should be, only I'm only getting the first page 67 times.

I've made a couple of posts on other community site around this issue before but I realise the problem I was (poorly) describing was far too broad to get any meaningful assistance. I think now I've gotten to the point where I understand what my own code is doing and have really zoomed in to the problem - I just don't know how to fix it when I'm at the final hurdle...

Any help/advice would be massively appreciated. Thank you.

Edit: Updated following @RicardoDiaz answer.

let

    // Define base parameters

    Filter  = "",
    Path    = "servicedesk/tickets/",
    URL     = "https://psa.pulseway.com/api/",
    Token   = "Token",
    Limit   = "100",

    // Build the table based on record start and any filters

    GetEntityRaw = (Filter as any, RecordStart as text, Path as text) =>

        let 

            Options     = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            URLbase     = URL & Path & "?bearer=" & Token & "&start=" & RecordStart & "&limit=" & Text.From(Limit),
            URLentity   = if Filter <> null then URLbase & Filter else URLbase,
            Source      = Json.Document(Web.Contents(URLentity, Options)),
            Result      = Source[Result],
            toTable     = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

        in
            toTable,

    // Recursively call the build table function

    GetEntity = (optional RecordStart as text) as table =>

        let
            result      = GetEntityRaw(Filter, RecordStart, Path),
            nextStart   = Text.From(Number.From(RecordStart) + Limit),
            nextTable   = Table.Combine({result, @GetEntity(nextStart)}),
            check       = try nextTable otherwise result

        in
            check,
            resultTable = GetEntity("0")
in
    resultTable

解决方案

As I couldn't test your code, it's kind of hard to provide you a concrete answer.

Said that, please review the generic code I use to connect to an api and see if you can find where yours is not working

EDIT: Changed api_record_limit type to number (removed the quotation marks)

let
    // Define base parameters
    api_url_filter = "",
    api_entity = "servicedesk/tickets/",
    api_url = "https://psa.pulseway.com/api/",
    api_token = "Token",
    api_record_limit = 500,
    // Build the table based on record start and any filters
    fx_api_get_entity_raw = (api_url_filter as any, api_record_start as text, api_entity as text) =>
    let
        api_url_base = api_url & api_entity & "?api_token=" & api_token & "&start=" & api_record_start & "&limit=" & Text.From(api_record_limit),
        api_url_entity = if api_url_filter <> null then api_url_base & api_url_filter else api_url_base,
        Source = Json.Document(Web.Contents(api_url_entity)),
        data = Source[data],
        toTable = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        toTable,
    // Recursively call the build table function
    fxGetEntity = (optional api_record_start as text) as table =>
    let
        result = fx_api_get_entity_raw(api_url_filter, api_record_start, api_entity),
        nextStart = Text.From(Number.From(api_record_start) + api_record_limit),
        nextTable = Table.Combine({result, @fxGetEntity(nextStart)}),
        check = try nextTable otherwise result
    in
        check,
        resultTable = fxGetEntity("0"),
        expandColumn = Table.ExpandRecordColumn(
                    resultTable,
                    "Column1",
                    Record.FieldNames(resultTable{0}[Column1]),
                    List.Transform(Record.FieldNames(resultTable{0}[Column1]), each _)
                )
in
    expandColumn

---

QUESTION TO OP:

Regarding this line:

Result      = Source[Result],

Does the json return a field called result instead of data?

这篇关于并购Power Query:如何在循环中使用 $Skip ODATA 表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！