I have this query to get data from external api, currently the api only allow to get the first 10,000 items:-
let
body=Text.Combine({"sAPIKey=",ApiKey}),
SourceAPILogin=Json.Document(
Web.Contents(
SmartAPI,
[
RelativePath="apilogin",
Headers=[Accept="application/json", #"Content-Type"="application/x-www-form-urlencoded",#"API-Key"=Token],
Content=Text.ToBinary(body)
]
)
),
tblGetUID = Table.FromList(SourceAPILogin, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expGetUID = Table.ExpandRecordColumn(tblGetUID, "Column1", {"UID", "LanguageCode", "DatabaseVersion"}, {"Column1.UID", "Column1.LanguageCode", "Column1.DatabaseVersion"}),
GetUID = expGetUID{0}[Column1.UID],
Source=Json.Document(
Web.Contents(
SmartAPI,
[
RelativePath = "timeledgerentry", //RESTAPI endpoint
//Query = [#"Filter" ="WorkTypeCode LIKE 'Normal%'"], // QueryParameters
Headers=[Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey] //Headers
]
)
),
tblAll = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
tableHasRecords = Table.RowCount(tblAll) > 0,
tblTimeLedgerEntries=
if tableHasRecords then
let
Result=Table.ExpandRecordColumn(tblAll,"Column1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))),Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null)))),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"StartTime", type datetime}, {"EndTime", type datetime}, {"LogStart", type datetime}, {"LogEnd", type datetime}})
in
#"Changed Type"
else
let
strColumnNames={
"EntryID","EmployeeCode","Name","Date","LogStart","LogEnd","WorkTypeCode","BreakTime","StartTime","EndTime","TotalTime","WorkCalendarCode","Type","Remark","DepartmentCode",
"WorkFunctionCode","Relative","PeriodName","JournalCode","EmployeeGroupCode","DistrictCode","TeamLeaderCode","Canceled","PayrollTypeCode","JournalLineEntryID",
"OverTimeCode","DimensionCode1","DimensionCode2","DimensionCode3","DimensionCode4","DimensionCode5","DimensionCode7","DimensionCode6","DimensionCode8","DimensionCode9","DimensionCode10",
"PayrollReference","JournalLineID"
},
// Create an empty table with the defined column names
emptyTable = #table(strColumnNames, {})
in
emptyTable
in
tblTimeLedgerEntries
so to get all items i can do pagination using the offset and limit paramters, so i modified my above query as follow:-
let
// Function to fetch data with pagination
FetchTimeLedgerEntries = (Offset as number, Limit as number) as table =>
let
body = Text.Combine({"sAPIKey=", ApiKey}),
SourceAPILogin = Json.Document(
Web.Contents(
SmartAPI,
[
RelativePath="apilogin",
Headers=[Accept="application/json", #"Content-Type"="application/x-www-form-urlencoded",#"API-Key"=Token],
Content=Text.ToBinary(body)
]
)
),
tblGetUID = Table.FromList(SourceAPILogin, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expGetUID = Table.ExpandRecordColumn(tblGetUID, "Column1", {"UID", "LanguageCode", "DatabaseVersion"}, {"Column1.UID", "Column1.LanguageCode", "Column1.DatabaseVersion"}),
GetUID = expGetUID{0}[Column1.UID],
Source = Json.Document(
Web.Contents(
SmartAPI,
[
RelativePath = "timeledgerentry",
Query = [#"offset"=Text.From(Offset), #"limit"=Text.From(Limit)], // Adding Offset and Limit parameters
Headers=[Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey]
]
)
),
tblAll = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
tableHasRecords = Table.RowCount(tblAll) > 0,
Result = if tableHasRecords then
Table.ExpandRecordColumn(tblAll,"Column1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))),Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(tblAll,"Column1"), each _ <> "" and _ <> null))))
else
#table(Table.ColumnNames(tblAll), {})
in
Result,
// Loop through pages to get all records
Offset = 0,
Limit = 10000,
PageSize = 10000,
AllPages = List.Generate(
() => [Result = FetchTimeLedgerEntries(Offset, Limit), Offset = Offset + PageSize],
each Table.RowCount([Result]) > 0,
each [Result = FetchTimeLedgerEntries([Offset], Limit), Offset = [Offset] + PageSize],
each [Result]
),
tblTimeLedgerEntries = Table.Combine(AllPages),
#"Changed Type" = Table.TransformColumnTypes(tblTimeLedgerEntries,{{"StartTime", type datetime}, {"EndTime", type datetime}, {"LogStart", type datetime}, {"LogEnd", type datetime}})
in
#"Changed Type"
so is my modification valid for doing the pagination?
Thanks
