Skip to main content
Skip table of contents

Large Result Sets in PowerQuery

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

  1. Increase the timeout in the Web.Contents (see
    This sample sets timeout to 5 minutes over the default 30 seconds

    Web.Contents("[database]/[project]/rooms?$select=room_func_no,name", [Timeout=#duration(0, 0, 5, 0)])
  2. Do a paginated query. This is a sample on how to do this:

        // The getNextPage function takes a single argument and is expected to return a nullable table
        Table.GenerateByPage = (getNextPage as function) as table =>
            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}?
            // 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.ExpandTableColumn(tableOfPages, "Column1", Table.ColumnNames(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 =>
            pageSize = if pageSize = null then 10000 else pageSize
            Table.GenerateByPage((previous) => 
                    // 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
        GetPage = (url as text, pageSize as number, offset as number) as table =>
            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))}})
            data2 meta [NextOffset = nextOffset],
        url = "[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}})
    	#"change type"

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.