Large Result Sets in PowerQuery

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

Increase the timeout in the Web.Contents (see https://learn.microsoft.com/en-us/powerquery-m/web-contents).
This sample sets timeout to 5 minutes over the default 30 seconds
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:

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"