I'm trying to load large datasets (over 3 million rows) from a Web API into Power BI.
- I'm currently testing with Dataflow Gen1.
- The workspace is Premium, with Fabric capacity (SKU: F64) and Small semantic model storage format.
Current Setup:
For entities with less than 1 million rows:
- Each Dataflow Gen1 contains one query.
- The M code includes:
- Token refresh
- Pagination using $filter and $skip (10,000 rows per page)
- List combine
- Conversion to table
- Each Dataflow refreshes once daily, with different refresh times.
- For queries close to 1 million rows, refresh occasionally fails. To mitigate this, I increased the page size to 100,000 rows and am monitoring the results.
For entities with more than 3 million rows:
- The structure is the same, but refresh fails consistently.
- The error message is: "We reached the end of the buffer", which seems to be a memory or capacity issue.
- As a workaround, I'm splitting the query to keep each under ~1 million rows and attempting incremental refresh in Dataflow Gen1.
I'm looking for guidance on how to structure and implement large-scale Web API ingestion (3M+ rows) using OData in Power BI, especially with Gen1. Any best practices, architectural suggestions, or technical tips would be greatly appreciated.
example of M code of dataflow gen1
let
// 1. Post to get Access Token
TokenResponse = Json.Document(Web.Contents("https://..oauth2",
[
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
VariableHeader = Text.From(Time.Hour(DateTime.LocalNow())) // ignore token cash, I think it's not neccessary.
],
Content = Text.ToBinary("grant_type=...&client_id=...&client_secret=...&scope=...")
]
)),
AccessToken = TokenResponse[access_token],
// 2. API get call
BaseUrl = "https://...",
GetPagedData = (skip as number) =>
let
RelativePath = "...",
Query = [
#"APIKey" = "...",
#"$top" = "100000",
#"$skip" = Number.ToText(skip),
#"$filter" = "...",
#"$select" = "..."
],
Response = Json.Document(Web.Contents(BaseUrl,
[
RelativePath = RelativePath,
Query = Query,
Headers = [
#"Authorization" = "Bearer " & AccessToken,
...
]
]
)),
Page = if Record.HasFields(Response, "value") and List.Count(Response[value]) > 0 then
Response[value]
else
{}
in
Page,
// 3. Fetch pages
PageSize = 100000,
FetchPages =
List.Generate(
() => [i = 0, result = GetPagedData(0)],
each List.Count([result]) > 0,
each [i = [i] + 1, result = GetPagedData(([i] + 1) * PageSize)],
each [result]
),
Combined = List.Combine(FetchPages),
PageCount = List.Count(FetchPages), // for check the pages
RowCount = List.Count(Combined), // for check the rows
// 4. convert to table
#"Converted to Table" = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"..."}),
#"Changed column type" = Table.TransformColumnTypes(#"Expanded Column1", {{"", type datetime}})
in
#"Changed column type"
