Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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
Proud to be a Super User!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
55 | |
55 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
40 |