Forum Discussion

mvcsharepointde's avatar
2 years ago

Pagination over my API calls to get all data

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

2 Replies

  • Hello mvcsharepointde,

     

    Can you please try the following:

     

    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({}, {})
            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"
    

    Hope this helps!