Forum Discussion

mvcsharepointde's avatar
1 year ago

Getting duplicate items from external API after i applied paging

I have this formula inside my Power BI desktop to get data from external API using Pagination:-

 

let
   
    PageSize = 10000, // The number of rows you want to retrieve per page
    GetData = (Offset as number) =>
        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", // REST API endpoint
                        Query = [
                            Offset = Text.From(Offset),
                            Limit = Text.From(PageSize)
                        ], // Apply pagination
                        Headers = [Accept="application/json", #"Content-Type"="application/json",#"API-Key"=Token, UID=GetUID, DeviceID=ApiKey] // Headers
                    ]
                )
            ),
            // Convert the list to a table
            TableData = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

            // Expand all records and lists in the table
            ExpandedData = if Table.HasColumns(TableData, {"Column1"}) then
                               Table.ExpandRecordColumn(TableData, "Column1", Record.FieldNames(TableData{0}[Column1]))
                           else
                               TableData // No need to expand if there is no "Column1"
        in
            ExpandedData,
    
    // Function to iterate over pages
    GetAllData = List.Generate(
        () => [Offset = 0, PageData = GetData(0)], 
        each Table.RowCount([PageData]) > 0, 
        each [Offset = [Offset] + PageSize, PageData = GetData([Offset])], 
        each [PageData]
    ),

    // Combine all the paged results into a single table
    CombinedData = Table.Combine(GetAllData),
    #"Sorted Rows" = Table.Sort(CombinedData,{{"JobCode", Order.Ascending}})
    
in
    #"Sorted Rows"

 

 but the problem i am facing is that i am getting the same item twice, as follow:-

 

 

 

 

so what could be the reason?Thanks

 

1 Reply

  • mvcsharepointde 

     

    Can't see anything obviously wrong in the code.

     

    If you make a single call to the API do you get the records returned twice?

     

    Whilst you work on finding the cause of this, you can remove duplicates from the ID column to get a single row of data per ID.

     

    Regards

     

    Phil