Page Comparison

If the result sets are to large you query might time out. There are two solution to that problem:

...

結果セットが大きすぎると、クエリがタイムアウトする可能性があります。この問題には、2つの解決策があります：

Web.Contentsのタイムアウトを増やす。(参照 Webコンテンツ - パワークエリM).
このサンプルでは、タイムアウトをデフォルトの、30秒から5分に設定しています。
Code Block
Web.Contents("https://XXXXX.drofus.com/api/[database]/[project]/rooms?$select=room_func_no,name", [Timeout=#duration(0, 0, 5, 0)])

Do a paginated query. This is a sample on how to do this:ページ分割のクエリを実行します。これは、その方法のサンプルです：

Code Block

let

    // The getNextPage function takes a single argument and is expected to return a nullable table
    Table.GenerateByPage = (getNextPage as function) as table =>
    let        
        listOfPages = List.Generate(
            () => getNextPage(null),            // get the first page of data
            (lastPage) => lastPage <> null,     // stop when the function returns null
            (lastPage) => getNextPage(lastPage) // pass the previous page to the next function call
        ),
        // concatenate the pages together
        tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
        firstRow = tableOfPages{0}?
    in
        // if we didn't get back any pages of data, return an empty table
        // otherwise set the table type based on the columns of the first page
        if (firstRow = null) then
            Table.FromRows({})
        else        
            Value.ReplaceType(
                Table.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(firstRow[Column1])),
                Value.Type(firstRow[Column1])
            ),

    // Read all pages of data.
    // After every page, we check the "NextLink" record on the metadata of the previous request.
    // Table.GenerateByPage will keep asking for more pages until we return null.
    GetAllPagesByNextLink = (url as text, optional pageSize as nullable number) as table =>
    let
        pageSize = if pageSize = null then 10000 else pageSize
    in
        Table.GenerateByPage((previous) => 
            let
                // if previous is null, then this is our first page of data
                nextOffset = if (previous = null) then 0 else Value.Metadata(previous)[NextOffset]?,
                // if NextLink was set to null by the previous call, we know we have no more data
                page = if (nextOffset <> null) then GetPage(url, pageSize, nextOffset) else null
            in
                page
        ),

    GetPage = (url as text, pageSize as number, offset as number) as table =>
    let
        urlToGet = url & "&$top=" & Number.ToText(pageSize) & "&$skip=" & Number.ToText(offset),
        response = Web.Contents(urlToGet),        
        body = Json.Document(response),
        data2 = Table.FromList(body, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        nextOffset = if Table.RowCount(data2) = 0 then null else pageSize + offset,
        data = #table({"First Column"}, {{urlToGet & " - " & Text.From(nextOffset) & " - " &  Text.From(Table.RowCount(data2))}})
    in
        data2 meta [NextOffset = nextOffset],

    url = "https://XXXXX.drofus.com/api/[database]/[project]/rooms?$select=room_func_no,name",
    Source = GetAllPagesByNextLink(url,100),
	#"expand" = Table.ExpandRecordColumn(Source, "Column1", {"room_func_no","name"}, {"Name and Numbers: Room Function #","Name and Numbers: Room Name"}),
	#"change type" = Table.TransformColumnTypes(expand,{{"Name and Numbers: Room Function #",Text.Type},{"Name and Numbers: Room Name",Text.Type}})
in
	#"change type"

...

Versions Compared

Old Version 1

New Version Current

Key