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 (e.g. set 5 minutes): https://learn.microsoft.com/en-us/powerquery-m/web-contents

   Web.Contents("http://bing.com/", [Timeout=#duration(0, 0, 5, 0)])

2. Do a paginated query. This is a sample on how to do this:
   

   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&$filter=id%20gt%200",
       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"