Imagine you've just joined a mid-sized financial services company where five different BI developers have independently connected Power BI Desktop to the same Salesforce CRM. Each one has written their own version of the revenue calculation logic. Two of them round to the nearest dollar. One rounds to two decimal places. One doesn't round at all. The fifth developer left six months ago, and his reports are still in production — nobody is quite sure what his rounding logic does, but the CFO's dashboard depends on it. Every month, someone discovers a discrepancy and the finger-pointing begins.
This is the problem Power BI Dataflows were built to solve. Dataflows are Power BI's answer to centralized, governed data preparation — a way to define your transformation logic once, in the cloud, and reuse it across every report, dataset, and workspace in your organization. When the CFO asks why the revenue numbers don't match, you shouldn't have to audit five separate Power Query editors in five different .pbix files. You should be able to point to a single, version-controlled entity in Power BI Service and say: that is the source of truth.
By the end of this lesson, you will understand not just how to create a Dataflow, but why the architecture is designed the way it is, how to make design decisions that scale across large teams, and how to avoid the performance and governance traps that catch even experienced Power BI developers. This is a deep lesson — we will cover the full stack from storage mechanics to incremental refresh to computed entities and dataflow lineage.
What you'll learn:
Before diving in, you should be comfortable with:
Before you write a single transformation, you need to understand what a Dataflow actually is at a technical level. Most tutorials skip this and then developers are surprised when performance doesn't behave as expected.
A Dataflow is, at its core, a collection of Power Query entities that execute in the cloud via a service called Power Query Online. When you define a Dataflow, you are writing M code that gets stored as metadata in Power BI Service. When a refresh is triggered — either manually, on a schedule, or via an API call — Power Query Online spins up compute resources, executes your M code against your source systems, and writes the results to storage.
That storage is the critical piece most people miss: Dataflows write to Azure Data Lake Storage Gen2 in Common Data Model (CDM) format. Each entity in your Dataflow becomes a folder in ADLS Gen2 containing Parquet files (for the data) and a model.json or manifest.cdm.json file (for the schema metadata). Whether you use Microsoft-managed storage or bring your own ADLS Gen2 account, the mechanics are identical — the difference is ownership and downstream access.
This means:
Power BI has two kinds of Dataflows, and confusing them is a common source of frustration:
Standard Dataflows (available in all workspaces with a Dataflow license) use Microsoft-managed storage. You get the full ETL pipeline capabilities, but you cannot access the underlying storage directly, and you cannot use linked entities across workspaces in some configurations.
Analytical Dataflows (requires Premium or PPU workspace, and bring-your-own ADLS Gen2) unlock:
Architecture Decision: If your organization has Power BI Premium, always use Premium workspaces for production Dataflows. The performance and governance benefits of the enhanced compute engine alone justify it. For prototyping or small teams without Premium, Standard Dataflows still deliver the core reusability value.
The most powerful way to use Dataflows is not as a single flat transformation — it's as a layered pipeline. Think of it like a medallion architecture (a concept you'll recognize from Databricks/Lakehouse patterns), adapted for the Power BI world.
Layer 1: Staging (Bronze/Raw) These Dataflows connect directly to source systems. Their only job is to extract data as-is, with minimal or no transformation. You rename columns, cast obvious types, and that's it. This layer is cheap to rebuild and easy to audit.
Layer 2: Transformation (Silver/Conformed) These Dataflows use linked or computed entities to reference Layer 1 data. Here you apply business logic: joins, aggregations, calculated columns, deduplication, data quality rules. These are the entities your data model builders and report developers will eventually consume.
Layer 3: Serving (Gold/Dimensional) These Dataflows (or, often, Power BI Datasets that connect to Layer 2 Dataflows) represent the final dimensional model: fact tables, dimension tables, slowly changing dimensions. These are optimized for query performance, not transformation flexibility.
Let's build this out with a concrete scenario. You're the lead BI developer at a retail chain. Data lives in three places:
Your goal: a unified Sales Analytics Dataflow that any report developer in any workspace can consume.
In Power BI Service, navigate to your designated Dataflow workspace (create a dedicated workspace for shared Dataflows — never mix report workspaces with Dataflow workspaces in production). Click New → Dataflow → Add new entities.
For the SQL source, you'll connect via the Power Query Online connector. The connection experience is nearly identical to Power BI Desktop, but it executes remotely. For SQL Server, if you're connecting to a private network, you'll need a Data Gateway (On-Premises Data Gateway in standard mode, not personal mode — personal mode doesn't work with Dataflows).
Here's your staging M code for the Orders entity. Notice the deliberate minimalism — we are not doing business logic here:
let
Source = Sql.Database(
"retaildb.database.windows.net",
"SalesDB",
[
Query = "
SELECT
order_id,
customer_id,
store_id,
product_id,
order_date,
quantity,
unit_price,
discount_pct,
created_at,
updated_at
FROM dbo.orders
WHERE order_date >= '2020-01-01'
",
CommandTimeout = #duration(0, 2, 0, 0)
]
),
// Enforce types explicitly - never trust inferred types from SQL
TypedTable = Table.TransformColumnTypes(
Source,
{
{"order_id", Int64.Type},
{"customer_id", Int64.Type},
{"store_id", Int32.Type},
{"product_id", Int32.Type},
{"order_date", type date},
{"quantity", Int32.Type},
{"unit_price", Currency.Type},
{"discount_pct", type number},
{"created_at", type datetimezone},
{"updated_at", type datetimezone}
}
)
in
TypedTable
Notice a few deliberate choices:
CommandTimeout — the default is often too short for large tables during initial loads.unit_price to Currency.Type, not type number. This matters downstream when you're accumulating decimal arithmetic errors across millions of rows.Warning: In Dataflows, query folding behavior in Power Query Online can differ from Power BI Desktop against the same source. Always check folding indicators in the Applied Steps panel. An unfolded step in a Dataflow means M is pulling the full dataset into memory before filtering — a performance disaster on large tables.
Do the same for Products, Stores, and any other SQL entities. Create separate entities for each logical table. Do not join them here.
For the Salesforce connector in your staging Dataflow:
let
Source = Salesforce.Data(
"https://yourorg.salesforce.com",
[ApiVersion = "55.0"]
),
AccountTable = Source{[Name="Account"]}[Data],
// Select only the fields you need - Salesforce returns 100+ columns by default
SelectedColumns = Table.SelectColumns(
AccountTable,
{
"Id",
"Name",
"ParentId",
"BillingCity",
"BillingState",
"BillingCountry",
"Industry",
"AnnualRevenue",
"CustomerTier__c",
"CreatedDate",
"LastModifiedDate"
}
),
TypedTable = Table.TransformColumnTypes(
SelectedColumns,
{
{"Id", type text},
{"ParentId", type text},
{"AnnualRevenue", Currency.Type},
{"CreatedDate", type datetimezone},
{"LastModifiedDate", type datetimezone}
}
)
in
TypedTable
Tip: Salesforce's Power Query connector fetches ALL columns by default unless you explicitly select. On a large org with many custom fields, this can mean transferring 10x the data you actually need. Always column-prune at the source in staging.
Now create a second Dataflow in the same Premium workspace. This is where you use linked entities to reference your staging Dataflow without re-querying the source.
In the Dataflow editor, when adding a new entity, choose Link entities from other dataflows. Select your staging Dataflow, and choose the Orders, Products, Stores, and Accounts entities. These linked entities are references — they point to the CDM storage written by the staging Dataflow. No additional API calls to Salesforce or SQL Server occur.
Now create a computed entity for your core transformation. A computed entity uses linked entities as its source, and the computation happens in the enhanced compute engine (columnar, in-memory) rather than by querying the original source:
let
// Reference linked entities - these are already in CDM storage
Orders = Dataflows.Entities(
"https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/dataflows/{dataflow-id}",
"Orders"
),
Products = Dataflows.Entities(
"https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/dataflows/{dataflow-id}",
"Products"
),
Stores = Dataflows.Entities(
"https://api.powerbi.com/v1.0/myorg/groups/{workspace-id}/dataflows/{dataflow-id}",
"Stores"
),
// Calculate line-level revenue with the agreed business logic
OrdersWithRevenue = Table.AddColumn(
Orders,
"GrossRevenue",
each [quantity] * [unit_price],
Currency.Type
),
OrdersWithNetRevenue = Table.AddColumn(
OrdersWithRevenue,
"NetRevenue",
each [GrossRevenue] * (1 - [discount_pct]),
Currency.Type
),
// Enrich with product and store dimensions
JoinedProducts = Table.NestedJoin(
OrdersWithNetRevenue,
{"product_id"},
Products,
{"product_id"},
"ProductDetail",
JoinKind.Left
),
ExpandedProducts = Table.ExpandTableColumn(
JoinedProducts,
"ProductDetail",
{"product_name", "category", "subcategory", "cost_price"},
{"product_name", "category", "subcategory", "cost_price"}
),
JoinedStores = Table.NestedJoin(
ExpandedProducts,
{"store_id"},
Stores,
{"store_id"},
"StoreDetail",
JoinKind.Left
),
ExpandedStores = Table.ExpandTableColumn(
JoinedStores,
"StoreDetail",
{"store_name", "region", "district"},
{"store_name", "region", "district"}
),
// Calculate margin
WithMargin = Table.AddColumn(
ExpandedStores,
"GrossMargin",
each if [cost_price] = null or [cost_price] = 0
then null
else ([NetRevenue] - ([quantity] * [cost_price])) / [NetRevenue],
type number
),
// Final column selection and ordering
FinalTable = Table.SelectColumns(
WithMargin,
{
"order_id", "order_date", "customer_id",
"store_id", "store_name", "region", "district",
"product_id", "product_name", "category", "subcategory",
"quantity", "unit_price", "discount_pct",
"GrossRevenue", "NetRevenue", "GrossMargin"
}
)
in
FinalTable
Critical: For this entity to qualify as a "computed entity" (and thus run in the enhanced compute engine without touching source systems), every upstream step must reference a linked entity or another computed entity in the same Dataflow. The moment you add a direct source connection into this Dataflow, the entire entity loses its computed status. Power BI will warn you with a yellow indicator in the entity list.
For large source systems, refreshing the entire history on every scheduled refresh is wasteful and often impossible within refresh time limits. Dataflows support incremental refresh using the same Range/Period parameter pattern as Power BI Datasets, but with some important differences.
Incremental refresh in Dataflows requires a Premium workspace. The mechanism works by partitioning the data in CDM storage by date ranges, then only refreshing the recent partitions on each scheduled run.
Step 1: Create the RangeStart and RangeEnd parameters
In your staging Dataflow's Orders entity, before your source query, create two parameters:
RangeStart of type Date/Time — set a default value like 1/1/2020 12:00:00 AMRangeEnd of type Date/Time — set a default value like 1/1/2020 12:00:00 AMThese parameter names are case-sensitive and magic — Power BI Service looks for exactly RangeStart and RangeEnd to configure partitioning. Do not name them anything else.
Step 2: Filter your data using these parameters
let
Source = Sql.Database(
"retaildb.database.windows.net",
"SalesDB",
[
Query = "
SELECT
order_id,
customer_id,
store_id,
product_id,
order_date,
quantity,
unit_price,
discount_pct,
created_at,
updated_at
FROM dbo.orders
",
CommandTimeout = #duration(0, 2, 0, 0)
]
),
// Apply the incremental refresh window filter
// This filter MUST fold back to the source for performance
FilteredByDate = Table.SelectRows(
Source,
each [order_date] >= Date.From(RangeStart)
and [order_date] < Date.From(RangeEnd)
),
TypedTable = Table.TransformColumnTypes(
FilteredByDate,
{
{"order_id", Int64.Type},
{"order_date", type date},
{"quantity", Int32.Type},
{"unit_price", Currency.Type},
{"discount_pct", type number},
{"created_at", type datetimezone},
{"updated_at", type datetimezone}
}
)
in
TypedTable
Step 3: Configure the incremental refresh policy
In the Dataflow editor, click the three dots next to your entity name and select Incremental refresh. You'll see a configuration panel:
Warning: The overlap between the history window and refresh window is not for auditing — it's for late-arriving data. If a sale from 3 days ago gets corrected or arrived late in your ERP, the 10-day refresh window catches it. The overlap should match your source system's SLA for data completeness. For financial data where month-end adjustments can arrive weeks late, 45-day overlap windows are not uncommon.
When you save the incremental refresh policy, Power BI Service creates multiple partitions in the CDM storage — one per month (or day, depending on your configuration). On the first full refresh, all partitions are populated. On subsequent refreshes, only the partitions that fall within the refresh window are re-queried from the source. Older partitions remain untouched in storage.
This is why query folding on the date filter is non-negotiable. If the filter doesn't fold, Power BI pulls the entire table from SQL Server into memory, then filters it — which is exactly what incremental refresh is supposed to prevent.
To verify folding in a Dataflow, you have limited options compared to Desktop (there's no "View Native Query" option in the cloud editor). The reliable approach is to ensure your filter column is indexed at the source and monitor your SQL Server execution plans during test refreshes using SQL Server Profiler or Query Store.
As your Dataflow ecosystem grows, you'll have staging Dataflows feeding transformation Dataflows feeding serving Dataflows. The natural question becomes: when you refresh staging, does transformation refresh automatically?
No. Dataflow refresh is not automatically cascading. You must orchestrate it.
The right tool for orchestration depends on your infrastructure:
POST /groups/{groupId}/dataflows/{dataflowId}/refreshes to trigger, GET /groups/{groupId}/dataflows/{dataflowId}/transactions to poll statusHere's what a cascading refresh looks like in Power Automate logic:
Trigger: Recurrence (daily at 2:00 AM)
→ Action: Refresh dataflow (Staging - SQL Sources)
→ Action: Wait for completion (poll every 5 minutes)
→ Condition: Did it succeed?
Yes → Action: Refresh dataflow (Transform - Sales Analytics)
→ Action: Wait for completion
→ Action: Refresh dataset (Sales Dashboard Dataset)
No → Action: Send email to data-team@company.com with failure details
Tip: Don't use Power Automate's built-in "wait for completion" naively — it polls, and on long-running refreshes you'll hit Power Automate's 30-day run limit, which sounds absurd but is a real problem when flows get stuck on gateway failures. Implement a timeout with a parallel branch that terminates the flow if it runs more than 4 hours.
The built-in Salesforce connector is fine for standard objects, but for high-volume or custom API sources, you'll sometimes need the REST API connector with OAuth. Here's how that looks for our promotional campaign API:
let
// Parameterized API call with pagination handling
BaseUrl = "https://api.campaignplatform.internal/v2/",
GetPage = (pageNum as number) =>
let
Response = Web.Contents(
BaseUrl & "campaigns",
[
Headers = [
#"Authorization" = "Bearer " & Text.FromBinary(
Lines.FromBinary(
File.Contents("C:\secrets\api_token.txt") // Use gateway credentials in prod
){0}
),
#"Content-Type" = "application/json"
],
Query = [
page = Number.ToText(pageNum),
page_size = "1000",
start_date = "2020-01-01",
end_date = Date.ToText(Date.From(DateTime.LocalNow()), "yyyy-MM-dd")
],
ManualStatusHandling = {429, 500, 503}
]
),
StatusCode = Value.Metadata(Response)[Response.Status],
ParsedJson = if StatusCode = 200
then Json.Document(Response)
else error Error.Record(
"ApiError",
"API returned status " & Number.ToText(StatusCode),
[StatusCode = StatusCode]
),
Data = ParsedJson[data]
in
Data,
// Get total page count first
FirstResponse = Json.Document(
Web.Contents(BaseUrl & "campaigns", [
Query = [page = "1", page_size = "1000"]
])
),
TotalPages = FirstResponse[total_pages],
// Generate all pages
PageList = List.Numbers(1, TotalPages),
AllPages = List.Transform(PageList, each GetPage(_)),
CombinedData = List.Combine(AllPages),
AsTable = Table.FromList(
CombinedData,
Splitter.SplitByNothing(),
{"Record"}
),
ExpandedRecords = Table.ExpandRecordColumn(
AsTable,
"Record",
{"campaign_id", "campaign_name", "start_date", "end_date",
"impressions", "clicks", "conversions", "spend"},
{"campaign_id", "campaign_name", "start_date", "end_date",
"impressions", "clicks", "conversions", "spend"}
)
in
ExpandedRecords
Warning: Paginated API calls in Dataflows do not support query folding — M must execute the pagination logic in memory. For APIs returning more than ~100,000 rows, consider loading to ADLS Gen2 via Azure Data Factory first, then pointing your Dataflow at ADLS Gen2. Dataflows are not optimal as primary ingestion tools for high-volume streaming or near-real-time APIs.
One of the more sophisticated patterns is managing SCD Type 2 in Dataflows — maintaining a history of dimension changes. The Dataflow itself doesn't have native SCD support like SSIS or dbt, but you can approximate it using a combination of incremental refresh and M logic.
The key insight is: with bring-your-own ADLS Gen2, you can read previous Dataflow data back in and compare it to current source data. This creates a self-referential pattern:
let
// Current source data
CurrentAccounts = /* linked entity from staging */,
// Previous snapshot from ADLS Gen2
// This requires your own ADLS Gen2 configured with the workspace
PreviousSnapshot = AzureStorage.DataLake(
"https://yourstorageaccount.dfs.core.windows.net",
[HierarchicalNavigation = true]
),
PreviousAccountsFile = PreviousSnapshot
{[Name = "powerbi"]}[Data]
{[Name = "workspaceid"]}[Data]
{[Name = "dataflowid"]}[Data]
{[Name = "Accounts"]}[Data],
// Parse the CDM Parquet files
PreviousAccounts = Parquet.Document(PreviousAccountsFile),
// Detect changed records
Joined = Table.NestedJoin(
CurrentAccounts,
{"Id"},
PreviousAccounts,
{"Id"},
"Previous",
JoinKind.FullOuter
),
// ... SCD Type 2 logic follows
ChangedRecords = Table.SelectRows(
Joined,
each [CustomerTier__c] <> Record.Field([Previous], "CustomerTier__c")
)
in
ChangedRecords
This pattern is genuinely complex and has edge cases — particularly around what happens on the first load when there's no previous snapshot. It requires careful error handling and is an area where many teams decide to use a proper data warehouse (Synapse, Databricks) for SCD logic and have the Dataflow simply read from it. Know your tool's limits.
The governance model for Dataflows requires thinking carefully about your workspace structure. Here is a production-grade pattern:
Workspace: Shared Data Platform (Dataflows only)
Workspace: Sales Analytics Reports
The key point: report developers should have no edit access to the Dataflow workspace. They consume Dataflows as a service. If they want to request a new transformation or column, that goes through a change management process, not a direct edit.
Use Power BI's Endorsement feature for Dataflows that are approved for production use:
Certified Dataflows appear with a blue badge in the Power BI Service UI and surface preferentially in search results. This is your primary mechanism for steering report developers toward approved data sources rather than raw connections.
If your organization uses Microsoft Information Protection (MIP), sensitivity labels cascade from Dataflows to any Dataset or report that consumes them. If your staging Dataflow pulls PII from Salesforce and you mark it as Confidential - PII, that label automatically propagates downstream. This is especially important for GDPR compliance — you need to know which reports are displaying personal data.
Power BI Service provides basic refresh history in the Dataflow settings page. For production monitoring, you need more:
Option 1: Power BI REST API
Poll GET /groups/{groupId}/dataflows/{dataflowId}/transactions after each refresh. The response includes start time, end time, status, and error messages per entity. Feed this into a monitoring Dataflow (yes, a Dataflow that monitors other Dataflows) or a Log Analytics workspace.
Option 2: Azure Monitor + Diagnostic Settings If your tenant admin has configured Power BI diagnostic settings to route to a Log Analytics workspace, you can query Dataflow refresh events via KQL:
PowerBIActivity
| where Activity == "RefreshDataflow"
| where TimeGenerated > ago(7d)
| project TimeGenerated, DataflowName, WorkspaceName,
Status, DurationMs = todouble(DurationMs),
ErrorCode
| where Status != "Succeeded"
| order by TimeGenerated desc
Option 3: Premium Capacity Metrics App If you're on Premium capacity (not PPU), the Capacity Metrics app shows Dataflow CPU and memory consumption, which is invaluable for identifying which entities are consuming disproportionate compute resources.
In Premium workspaces, you can enable the enhanced compute engine for a Dataflow. This changes how computed entities are processed — instead of standard Power Query evaluation, computed entities run against a columnar SQL engine that can handle billions of rows with dramatically better performance for aggregations and joins.
To enable it: Dataflow Settings → Enhanced compute engine → Optimized.
The "On" setting enables the engine but uses lazy evaluation. "Optimized" pre-materializes computed entities into the columnar store. For most production scenarios, "Optimized" is what you want, but it consumes more Premium capacity CUs (capacity units).
Benchmark context: In Microsoft's own documentation, they cite up to 25x performance improvement for computed entities with the enhanced compute engine enabled on large datasets. In practice, I've seen 8-15x improvement on 50M+ row tables doing multi-column aggregations. Your results will vary, but the improvement is genuine and substantial.
If your staging Dataflows connect to on-premises SQL Server or other internal sources, the On-Premises Data Gateway is in your critical path. Gateway performance anti-patterns:
Anti-pattern: Single gateway for all Dataflows One gateway machine handling 10 Dataflows that all refresh simultaneously at 6 AM. The gateway becomes the bottleneck, all refreshes queue, and your data isn't ready until 8 AM.
Better pattern: Gateway cluster with load balancing Install the gateway on 3-4 machines and configure them as a cluster. Power BI automatically distributes queries across cluster members. For very high-throughput scenarios, put the gateway machines in the same Azure region as your Power BI tenant and use Azure ExpressRoute to your on-premises SQL Server.
Anti-pattern: Pulling 50 columns when you need 8 The gateway transmits full rows across the network before M can drop columns. Column pruning in the native SQL query (as shown in our staging examples) is essential.
Before promoting any Dataflow to production, audit every entity for query folding. The process in Dataflows is less transparent than Desktop, but you can:
This JSON-based workflow also gives you a primitive form of version control. Store your Dataflow JSON files in Git.
Let's put it all together. In this exercise you will build a three-layer Dataflow pipeline.
Setup Requirements:
Exercise Part 1: Create the Staging Dataflow
[YourName] - Data Platform with a PPU or Premium license.STG - AdventureWorks SQL.SalesLT.SalesOrderHeader and use a native SQL query to select: SalesOrderID, OrderDate, CustomerID, SubTotal, TaxAmt, Freight, TotalDue, ModifiedDate.SalesLT.SalesOrderDetail: SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, UnitPriceDiscount, LineTotal.SalesLT.Product: ProductID, Name, ProductNumber, Color, StandardCost, ListPrice, ProductCategoryID.Exercise Part 2: Create the Transformation Dataflow
TRF - Sales Analytics.FactSalesLines that:SalesOrderDetail to SalesOrderHeader on SalesOrderIDProduct on ProductIDDiscountedUnitPrice = UnitPrice * (1 - UnitPriceDiscount)GrossMargin = (DiscountedUnitPrice - StandardCost) / DiscountedUnitPriceFactSalesLines shows the computed entity icon (it should look different from a standard entity).Exercise Part 3: Connect a Dataset
FactSalesLines entity from TRF - Sales Analytics.Total Net Revenue = SUM(FactSalesLines[LineTotal]).Validation checkpoint: Your report should show revenue by category without any direct connection to AdventureWorks SQL. All the transformation logic lives in the Dataflow. If you or a colleague wanted to use the same FactSalesLines entity in a different report, they would connect to the same Dataflow entity — same logic, same numbers, guaranteed.
This is the most common anti-pattern. A developer creates a Dataflow with some transformations, then adds more transformations in Power Query inside Power BI Desktop when connecting to the Dataflow. The result is split logic — some business rules in the Dataflow, some in the dataset. When a discrepancy emerges, nobody can find all the places where calculations happen.
Rule: In a Dataflow-based architecture, the Dataflow is the transformation layer. Your dataset should do nothing in Power Query except select columns and import tables. All M code lives in the Dataflow.
Report developers sometimes expect Dataflow-backed datasets to reflect real-time source data. They don't. A Dataflow is a materialized snapshot as of its last refresh. If your staging Dataflow refreshed at 6 AM and a sales order came in at 7 AM, the dataset won't know about it until the next refresh cycle.
Mitigation: Set clear refresh SLAs and document them. If you need data fresher than hourly, Dataflows may not be the right tool for that entity — consider DirectQuery against the source for near-real-time requirements and Dataflows for the historical/enriched data.
If you reference a linked entity from another Dataflow and add transformations, but you're in a non-Premium workspace, the "computed entity" concept doesn't apply — Power Query will go back to the source system. This is often discovered during load testing when developers see Salesforce API call counts spiking unexpectedly.
Fix: Always verify your workspace license mode in workspace settings. The enhanced compute engine option in Dataflow settings is a reliable indicator — if it's grayed out, you're not in a Premium workspace.
The promotional campaign API example above uses ManualStatusHandling = {429} to catch rate limit responses, but doesn't include exponential backoff. In production, if your API returns 429, your Dataflow will error out. Implement retry logic:
GetPageWithRetry = (pageNum as number, attempt as number) =>
let
Response = Web.Contents(/* ... */),
StatusCode = Value.Metadata(Response)[Response.Status],
Result = if StatusCode = 429
then if attempt >= 3
then error Error.Record("RateLimited", "Exceeded retry attempts")
else Function.InvokeAfter(
() => GetPageWithRetry(pageNum, attempt + 1),
#duration(0, 0, 0, 30 * attempt) // 30s, 60s, 90s backoff
)
else Json.Document(Response)
in
Result
Teams set up three Dataflows with schedules but don't account for the fact that the staging Dataflow sometimes runs long. If staging is scheduled at 2 AM and transformation at 3 AM, and staging runs 90 minutes, transformation reads stale data. Orchestration via Power Automate or ADF is not optional in production — it's essential.
This generic error has several causes. Work through them in order:
You now have a complete picture of how Power BI Dataflows work — from the CDM storage mechanics, through the multi-layer ETL design pattern, to incremental refresh, computed entities, and enterprise governance.
The key insight to carry forward: Dataflows are not just a convenience feature. They are a platform capability that enables a separation of concerns between the people who understand the source systems (data engineers, ETL developers) and the people who build reports and dashboards (BI analysts). When this separation works well, a change in business logic — like a new revenue calculation — happens in one place, propagates to every report instantly on the next refresh, and can be audited, certified, and governed centrally.
Architectural principle to remember: Complexity belongs in the Dataflow, simplicity belongs in the dataset. The more logic you push into Dataflows, the more reusable, testable, and governable your BI estate becomes.
Once you're comfortable with this foundation, explore these adjacent topics:
The path from here to a fully governed, scalable BI data platform runs directly through mastering the patterns in this lesson. Build the staging/transformation/serving layers, enforce the governance disciplines, and you'll have a platform that scales from 5 report developers to 500.
Learning Path: Getting Started with Power BI