After applying Paging using Offset & Limit i got that a colum does not exsists

[mvcsharepointde]

I have this formual inside power bi to get data from external api:-

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 = "jobs", //RESTAPI endpoint
//Query = [#"sQueryKey" = QueryKey,#"FilterLocation"="Database"], // QueryParameters
Headers=[Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey] //Headers
]
)
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
//display all the columns from query
Result=Table.ExpandRecordColumn( #"Converted to Table","Column1", Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Converted to Table","Column1"), each _ <> "" and _ <> null))),Table.ColumnNames(Table.FromRecords(List.Select(Table.Column(#"Converted to Table","Column1"), each _ <> "" and _ <> null)))),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"StartedDate", type datetime}})

in
#"Changed Type"

currently i am only getting 10,000 items, so i wanted to apply paging using offiset & limit, so i modifed the code as follow:-

let
    // Function to fetch data with pagination
    Fetchjobs = (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 = "jobs",
                                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,

    // Initial pagination parameters
    Offset = 0,
    Limit = 10000,
    PageSize = 10000,

    // Loop through pages to get all records
    AllPages = List.Generate(
                    () => [Result = Fetchjobs(Offset, Limit), Offset = Offset + PageSize],
                    each Table.RowCount([Result]) > 0,
                    each [Result = Fetchjobs([Offset], Limit), Offset = [Offset] + PageSize],
                    each [Result]
                ),

    // Combine all the pages into a single table
    tbljobs = Table.Combine(AllPages),

    // Change column types as needed
    #"Changed Type" = Table.TransformColumnTypes(tbljobs,{{"StartedDate", type datetime}})
    
in
    #"Changed Type"

but after doing so i got this error:-

Expression.Error: The column 'StartedDate' of the table wasn't found.
Details:
StartedDate

here is the API i am trying to connect to:-

https://developer.smarttid.dk/smartapi/jobs

Also if i remove the column transfomation, as follow:-

let
    // Function to fetch data with pagination
    Fetchjobs = (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 = "jobs",
                                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,

    // Initial pagination parameters
    Offset = 0,
    Limit = 10000,
    PageSize = 10000,

    // Loop through pages to get all records
    AllPages = List.Generate(
                    () => [Result = Fetchjobs(Offset, Limit), Offset = Offset + PageSize],
                    each Table.RowCount([Result]) > 0,
                    each [Result = Fetchjobs([Offset], Limit), Offset = [Offset] + PageSize],
                    each [Result]
                ),

    // Combine all the pages into a single table
    tbljobs = Table.Combine(AllPages)//,

    // Change column types as needed
    //#"Changed Type" = Table.TransformColumnTypes(tbljobs,{{"StartedDate", type datetime}})
    tbljobs
in
    tbljobs//#"Changed Type"

i will not get any result from the table.

so what could be the reason ?

Thanks

[mvcsharepointde]

@AnonymousOk but if i remove the columnTransform i will not get any result inside the result... so i think the problem is within the paging code?

[Anonymous]

Hi @mvcsharepointde 

This error appears to be due to the fact that the StartedDate column cannot be found in the data returned by the API.

There may be several reasons for this:

Column names do not match. The column name StartedDate may differ in the API response. Double-check the exact column names in the API response.

Data structure problem. The data structure returned by the API may not match your expectations. Make sure the data contains the StartedDate field.

The data is empty. If the API returns empty results for some pages, the StartedDate column may not exist in those pages.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.