Forum Discussion
mvcsharepointde
1 year agoHelper II
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
- PhilipTreacySuper User
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