Forum Discussion
gotmike
10 years agoFrequent Visitor
how to create a query that paginates?
I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
Larstc75
6 years agoFrequent Visitor
I have a function that is queried in a table query.
This is the function:
let AccountData = (Timestamp as text, UnitId as number) =>
let
Source = Json.Document(Web.Contents("https://qa.api.remoni.com/v1/Data?orderbydesc=Timestamp&Timestamp=le(3000-01-01)&UnitId=eq(1)&AggregateType=eq(Hour)&top=10000",
[Query=[orderbydesc="Timestamp",Timestamp="le("&Text.From(Timestamp)&")",UnitId="eq("&Text.From(UnitId)&")",AggregateType="eq(Hour)",top="10000"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"AccountId", "UnitId", "SensorId", "DataType", "AggregateType", "Timestamp", "Value", "Base"}, {"AccountId", "UnitId", "SensorId", "DataType", "AggregateType", "Timestamp", "Value", "Base"})
in
#"Expanded Column1"
in AccountData
It is being queried for Timestamp and UnitId in another query.
My problem is that we have much more than 10.000 rows which is the limit in an API call.
To get around it the API offers a Top function: Returns the maximum number of entities.
and a Skip-function: Skips this number of entities
How can I do pagination in this scenario? It seems simple but after a few days I haven't resolved it...
The table-query (it is the query that is loaded to the model) looks like this and the function is called here:
#"Added Custom1" = Table.AddColumn(#"Added Custom", "UnitData", each AccountData([Today], [UnitId])),
The total Table-Query:
let
Source = Json.Document(Web.Contents("https://qa.api.xxxxx.com/v1/Units?orderby=UnitId&Account.AccountId=eq("&AccountId&")&top=10000")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UnitId", "Name", "Account", "UnitType", "Tags"}, {"UnitId", "Name", "Account", "UnitType", "Tags"}),
#"Expanded Account" = Table.ExpandRecordColumn(#"Expanded Column", "Account", {"AccountId"}, {"AccountId"}),
#"Expanded UnitType" = Table.ExpandRecordColumn(#"Expanded Account", "UnitType", {"Name"}, {"Name.1"}),
#"Expanded Tags" = Table.ExpandListColumn(#"Expanded UnitType", "Tags"),
#"Expanded Tags1" = Table.ExpandRecordColumn(#"Expanded Tags", "Tags", {"Text"}, {"Text"}),
#"Grouped Rows" = Table.Group(#"Expanded Tags1", {"UnitId"}, {{"Tag", each _, type table}}),
#"Added Custom4" = Table.AddColumn(#"Grouped Rows", "Tags", each Table.Column([Tag], "Text")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom4", {"Tags", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Expanded Tag" = Table.ExpandTableColumn(#"Extracted Values", "Tag", {"Name", "AccountId", "Name.1"}, {"Name", "AccountId", "Name.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Tag",{{"Name.1", "SensorType"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"UnitId"}),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Today", each Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "UnitData", each AccountData([Today], [UnitId])),
#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Added Custom1", {"UnitData"}),
#"Expanded UnitData" = Table.ExpandTableColumn(#"Removed Errors1", "UnitData", {"DataType", "AggregateType", "Timestamp", "Value", "Base"}, {"DataType", "AggregateType", "Timestamp", "Value", "Base"}),
#"Filtered Rows1 - Keep external-temperature-1" = Table.SelectRows(#"Expanded UnitData", each Text.Contains([DataType], "external-temperature-1")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1 - Keep external-temperature-1",{{"Timestamp", type datetime}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[DataType]), "DataType", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each Text.EndsWith([Base], "C")),
#"Removed Duplicates1" = Table.Distinct(#"Filtered Rows", {"UnitId", "Timestamp"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Duplicates1",{{"external-temperature-1", "Value"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Value", type number}, {"AccountId", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Name", "Name - Copy"),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Duplicated Column", "Text Before Delimiter", each Text.BeforeDelimiter([#"Name - Copy"], "_"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Before Delimiter",{"Name - Copy"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Text Before Delimiter", "Anlæg"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns2", "Value rounded3", each Number.Round ([Value],3, RoundingMode.AwayFromZero))
in
#"Added Custom2"