Introduction
Picture this: your enterprise has seventeen teams building their own version of "Sales by Region." Finance calculates it one way, Operations calculates it slightly differently, and the Regional VPs have their own spreadsheet that nobody trusts anymore. Every month, the reconciliation meeting runs two hours longer than it should because everyone's numbers disagree at the margins. The root cause isn't a skills problem — it's an architecture problem. There's no authoritative transformation layer that everyone draws from.
Power BI Dataflows Gen2, combined with Azure Data Lake Storage Gen2 (ADLS Gen2) and computed entities, gives you the infrastructure to solve this properly. Instead of each report author pulling raw data and reimplementing the same business logic in their own semantic model, you build a centralized, reusable transformation layer. That layer standardizes the data once, persists it in a format the entire organization can consume, and lets individual teams build on top of a trusted foundation without touching the source systems. It's the difference between a city where every building has its own well and one that has a water utility.
By the end of this lesson, you'll be able to design and implement a production-grade Dataflows Gen2 architecture in Power BI, including ADLS Gen2 integration for durable storage, computed entities for layered transformations, and the operational patterns that make the whole system maintainable at enterprise scale.
What you'll learn:
- How Dataflows Gen2 differs architecturally from Gen1 and when each is appropriate
- How to configure ADLS Gen2 integration at the tenant and workspace level, including the storage account structure that Dataflows creates
- How to design a multi-layer transformation architecture using standard entities and computed entities
- How to implement incremental refresh within Dataflows Gen2 for large data volumes
- How to govern, monitor, and operationalize a Dataflows transformation layer as a shared enterprise service
Prerequisites
Before working through this lesson, you should have:
Understanding the Gen2 Architecture: What Actually Changed
Before writing a single line of M code, you need to understand what makes Dataflows Gen2 fundamentally different from its predecessor. This isn't marketing versioning — the internal architecture changed significantly, and those changes affect every design decision you'll make.
The Gen1 Storage Model and Its Limitations
Gen1 Dataflows stored their output in Microsoft-managed Common Data Model (CDM) folders inside a Microsoft-managed Azure Data Lake. You had no direct access to that storage, which created several problems. You couldn't use external tools (Azure Data Factory, Synapse, Databricks) to read the data without going through Power BI APIs. You couldn't control data residency. You couldn't apply your own encryption keys. And when a dataflow was deleted, the data was gone with it — there was no external storage layer you could preserve.
What Gen2 Brings to the Table
Gen2 introduces several architectural improvements that directly address enterprise requirements:
Bring Your Own Storage (BYOS): You can connect your workspace to your own ADLS Gen2 storage account. Dataflows Gen2 will write entity outputs as Parquet files into CDM-formatted folders in your storage, giving you full access, full control, and the ability to integrate with the rest of your data platform.
Mashup Engine on Premium Capacity: Gen2 dataflows execute entirely on your Premium capacity's Mashup Engine rather than on shared Microsoft compute. This means your transformations don't compete with other tenants for CPU and memory — though it does mean your capacity sizing matters more.
Native Staging: Gen2 supports staging queries natively in the UI. In Gen1, you'd achieve similar results with reference queries in Power Query Desktop and then publish. Gen2 makes this a first-class concept.
DirectQuery on Dataflows: Semantic models can connect to Dataflows Gen2 entities via DirectQuery rather than importing the data twice. This reduces refresh coupling — the semantic model can always query the latest state of the entity without its own scheduled import.
Computed Entities: While computed entities existed in Gen1 Premium, Gen2 makes them more reliable and better integrated with the ADLS storage layer. A computed entity reads from another entity's already-materialized Parquet output rather than re-executing the upstream query. This is the key to building a layered architecture without compounding compute costs.
Key insight: The most important architectural principle in Gen2 is that computed entities read from storage, not from the upstream query definition. This means the upstream entity must refresh before the computed entity, and the computed entity's execution cost is proportional to the complexity of its own transformations, not the total pipeline depth.
Configuring ADLS Gen2 Integration
Getting storage integration right is the foundation everything else sits on. Do this wrong and you'll end up with permission errors or data landing in places you didn't expect.
Step 1: Provision the Storage Account
Create an ADLS Gen2 storage account with hierarchical namespace enabled. This is non-negotiable — regular blob storage won't work because Power BI requires the hierarchical namespace for CDM folder management.
In the Azure portal, create a new Storage Account and under the Advanced tab, check Enable hierarchical namespace. Choose a region that matches your Power BI tenant's home region to avoid unnecessary cross-region data transfer costs.
Once the account exists, create a container named powerbi (this is the default container name that Power BI will look for, though you can configure a different one at the tenant level). Enable the Storage Blob Data Owner role assignment for the service principal that Power BI will use.
Warning: The storage account must be in the same Azure Active Directory tenant as your Power BI tenant. Cross-tenant storage connections are not supported, even if you have valid credentials. This is a common source of confusion when enterprises have separate Azure subscriptions under the same AD tenant — those work fine. Separate AD tenants do not.
In the Power BI Admin Portal, navigate to Admin settings > Azure connections. Here you'll connect your ADLS Gen2 account at the tenant level. This establishes the default storage account for all workspaces that don't have their own workspace-level override.
Enter the storage account URL in the format https://yourstorageaccount.dfs.core.windows.net. Power BI will validate the connection and check that it has adequate permissions. If validation fails, the most common culprits are: hierarchical namespace not enabled, wrong role assignment, or firewall rules on the storage account blocking the Power BI service IPs.
Step 3: Workspace-Level Storage Assignment
Navigate to your target workspace settings in the Power BI service. Under Premium, you'll see a Dataflow storage option. You can either inherit from the tenant default or specify a different storage account for this specific workspace.
For enterprise architectures, it's common to have a dedicated storage account for each major domain (Finance, Operations, HR) rather than one monolithic account. This supports data segregation, independent access control policies, and cleaner cost attribution in Azure.
Understanding the Storage Structure Power BI Creates
Once you connect the storage, Power BI creates a folder structure inside your container following the CDM specification:
powerbi/
{workspace-guid}/
{dataflow-guid}/
{entity-name}/
model.json
{entity-name}.snappy.parquet
{partition-files...}
The model.json file at each entity level describes the schema in CDM format. The actual data lives in Parquet files with Snappy compression — which is why any Spark-based tool or Azure Data Factory can read these files directly without going through Power BI APIs.
Tip: After connecting storage and running your first dataflow refresh, open Azure Storage Explorer and navigate to your container. Spend fifteen minutes exploring the folder structure and opening a model.json file in a text editor. Understanding this structure will save you significant debugging time later, especially when you need to connect ADF or Synapse to consume these entities.
For organizations managing encryption requirements, the ADLS Gen2 storage account can use customer-managed keys (CMK) through Azure Key Vault, which complements the Power BI Premium BYOK encryption capabilities. Both layers protect data at rest.
With storage configured, the real design work begins. The most successful enterprise dataflow implementations use a consistent layering pattern that separates concerns cleanly.
The Three-Layer Model
Think of your dataflow architecture in three distinct layers, each implemented as either separate dataflows or separate entity groups within a dataflow:
Layer 1: Ingestion (Standard Entities)
Raw data pulled from source systems with minimal transformation. The goal here is to land data faithfully, apply data type corrections, and handle source-specific quirks (encoding issues, trailing spaces, null representations). No business logic lives here.
Layer 2: Conformation (Computed Entities)
Data cleaning, standardization, and cross-source joining. This is where you apply business rules that are universal — address normalization, date dimension alignment, customer deduplication. These entities read from Layer 1 entities.
Layer 3: Enrichment (Computed Entities)
Domain-specific aggregations, KPI calculations, and ready-to-consume datasets. Individual domain teams can own entities at this layer, building on the conformed foundation. These entities may read from Layer 2 entities or other Layer 3 entities.
Note: You don't have to implement each layer as a separate dataflow. For smaller implementations, a single dataflow with standard entities for Layer 1 and computed entities for Layers 2 and 3 works well. However, separate dataflows give you independent refresh schedules and cleaner dependency management, which becomes important at enterprise scale.
A Realistic Enterprise Scenario
Let's build this concretely. Imagine you're a data architect at a retail company with 200 stores. Your source systems are:
- ERP (SQL Server on-premises): Transaction data, ~50M rows/day
- CRM (Salesforce API): Customer master, loyalty program data
- Store Operations (Azure SQL): Labor hours, inventory counts, store metadata
Your goal is to create a transformation layer that lets any report author in the organization access clean, conformed Sales, Customer, and Store data without connecting to source systems directly.
Building the Ingestion Layer
In the Power BI service, create a new Dataflow Gen2 in your workspace. You'll be in the Power Query Online editor — it's the same M engine as Desktop, but running cloud-side.
Connecting to SQL Server On-Premises
For the ERP connection, you need an On-Premises Data Gateway. If you haven't already set this up properly for high-availability scenarios, it's worth reviewing Implementing Power BI Gateway Clusters for High Availability and Load Balancing of Enterprise On-Premises Data Connections before proceeding.
In the Power Query Online editor, add a new data source and select SQL Server database. Configure the gateway connection and target the ERP database. Navigate to your transactions table and load it.
Your raw ingestion query for transactions might look like this:
let
Source = Sql.Database(
"erp-server.internal.company.com",
"RetailERP",
[
Query = "
SELECT
TransactionID,
StoreID,
CustomerID,
TransactionDate,
ProductSKU,
Quantity,
UnitPrice,
DiscountAmount,
TaxAmount,
TotalAmount,
PaymentMethodCode,
EmployeeID,
CreatedAt,
ModifiedAt
FROM dbo.SalesTransaction
WHERE ModifiedAt >= @StartDate
",
CreateNavigationProperties = false
]
),
TypedSource = Table.TransformColumnTypes(Source, {
{"TransactionID", type text},
{"StoreID", type text},
{"CustomerID", type text},
{"TransactionDate", type date},
{"ProductSKU", type text},
{"Quantity", Int64.Type},
{"UnitPrice", type number},
{"DiscountAmount", type number},
{"TaxAmount", type number},
{"TotalAmount", type number},
{"PaymentMethodCode", type text},
{"EmployeeID", type text},
{"CreatedAt", type datetime},
{"ModifiedAt", type datetime}
})
in
TypedSource
Notice a few intentional choices here:
- We're using a native SQL query rather than table navigation. This pushes filtering to the source, which is critical for performance on 50M+ row tables.
- All IDs are typed as text, not integer, even if the source stores them as integers. This is a deliberate ingestion layer discipline — you'll handle business key standardization in the conformation layer.
- We've referenced
@StartDate — this is the parameter that incremental refresh will inject.
Setting Up Incremental Refresh on the Ingestion Entity
Incremental refresh on Dataflows Gen2 works through a parameter-based approach, similar to semantic model incremental refresh but with some important differences.
In the Power Query Online editor, create two parameters:
RangeStart of type DateTimeRangeEnd of type DateTime
Then modify your query to filter on ModifiedAt using these parameters:
let
Source = Sql.Database(
"erp-server.internal.company.com",
"RetailERP",
[
Query = "
SELECT TransactionID, StoreID, CustomerID, TransactionDate,
ProductSKU, Quantity, UnitPrice, DiscountAmount,
TaxAmount, TotalAmount, PaymentMethodCode, EmployeeID,
CreatedAt, ModifiedAt
FROM dbo.SalesTransaction
WHERE ModifiedAt >= '" & DateTime.ToText(RangeStart, "yyyy-MM-dd HH:mm:ss") & "'
AND ModifiedAt < '" & DateTime.ToText(RangeEnd, "yyyy-MM-dd HH:mm:ss") & "'
",
CreateNavigationProperties = false
]
),
TypedSource = Table.TransformColumnTypes(Source, {
{"TransactionID", type text},
{"TransactionDate", type date},
{"ModifiedAt", type datetime}
// ... rest of column types
})
in
TypedSource
Once the parameters exist and the query references them by name, right-click the entity name in the left panel and select Incremental refresh. Configure a 3-year historical range with a 7-day refresh window, matching the transaction modification patterns in your ERP.
Warning: There's a subtle but important difference between Power BI semantic model incremental refresh and Dataflows incremental refresh. In semantic models, Power BI manages partitions automatically and the RangeStart/RangeEnd parameters are injected at execution time. In Dataflows, Power BI similarly injects these values, but the parameters must be named exactly RangeStart and RangeEnd (case-sensitive) and must be of DateTime type — not Date. Using Date type parameters will silently fail to trigger incremental behavior. For more depth on incremental refresh patterns, see Designing and Implementing Power BI Incremental Refresh for Large-Scale Enterprise Datasets.
This is where the architecture starts delivering real value. Computed entities are the mechanism by which you avoid re-executing expensive upstream queries every time a downstream transformation runs.
How Computed Entities Actually Execute
When you create a computed entity that references another entity, Power BI Online recognizes the reference and, rather than replaying the source query, reads from the Parquet files that the source entity wrote to ADLS Gen2 during its last refresh. This is what makes computed entities "compute on storage" — the Mashup Engine reads compressed columnar Parquet rather than querying the operational database.
The practical implication: the source entity must have refreshed at least once before you can create a computed entity based on it. If you try to preview a computed entity that references a standard entity that hasn't materialized yet, you'll get an error.
In your dataflow, after the raw SalesTransaction_Raw entity has materialized, add a new entity and set its source as a reference to SalesTransaction_Raw. Power BI will automatically detect this is an internal reference and mark the new entity as a computed entity (you'll see a small lightning bolt icon next to it in the entity list).
let
Source = SalesTransaction_Raw,
// Standardize store IDs to always be 4-digit zero-padded strings
NormalizedStoreID = Table.TransformColumns(Source, {
{"StoreID", each Text.PadStart(Text.From(_), 4, "0"), type text}
}),
// Add derived date columns that dimension tables will join to
AddedDateKey = Table.AddColumn(NormalizedStoreID, "DateKey",
each Date.Year([TransactionDate]) * 10000 +
Date.Month([TransactionDate]) * 100 +
Date.Day([TransactionDate]),
Int64.Type),
AddedFiscalYear = Table.AddColumn(AddedDateKey, "FiscalYear",
each if Date.Month([TransactionDate]) >= 7
then Date.Year([TransactionDate]) + 1
else Date.Year([TransactionDate]),
Int64.Type),
// Calculate net sale amount (business rule: net = gross - discount, before tax)
AddedNetSaleAmount = Table.AddColumn(AddedFiscalYear, "NetSaleAmount",
each [UnitPrice] * [Quantity] - [DiscountAmount],
type number),
// Classify transaction size (business definition agreed with Finance)
AddedTransactionTier = Table.AddColumn(AddedNetSaleAmount, "TransactionTier",
each if [TotalAmount] >= 500 then "High Value"
else if [TotalAmount] >= 100 then "Mid Value"
else "Standard",
type text),
// Remove source system internal columns not needed downstream
RemovedInternalColumns = Table.RemoveColumns(AddedTransactionTier,
{"CreatedAt", "ModifiedAt"}),
FinalOutput = Table.ReorderColumns(RemovedInternalColumns, {
"TransactionID", "DateKey", "FiscalYear", "TransactionDate",
"StoreID", "CustomerID", "ProductSKU", "PaymentMethodCode",
"EmployeeID", "Quantity", "UnitPrice", "DiscountAmount",
"TaxAmount", "NetSaleAmount", "TotalAmount", "TransactionTier"
})
in
FinalOutput
This computed entity adds business logic that every downstream consumer should agree on: fiscal year logic, transaction tier classification, net sale amount calculation, and store ID normalization. When Finance and Operations both build from SalesTransaction_Conformed, they're guaranteed to agree on these definitions.
Building the Store Dimension with Cross-Source Joining
Your Store dimension needs to join data from the ERP (which has store financial codes) and the Store Operations system (which has physical store attributes). This cross-source joining is a perfect fit for the conformation layer.
First, create raw ingestion entities for both source systems. Then create a computed entity that joins them:
let
// These are computed entities reading from materialized storage
ERPStores = ERP_StoreFinancial_Raw,
OpsStores = StoreOps_StoreAttributes_Raw,
// Join on the common store identifier
JoinedStores = Table.Join(
ERPStores, {"StoreID"},
OpsStores, {"StoreCode"},
JoinKind.Inner
),
// Rename to resolve column name conflicts from both sources
RenamedColumns = Table.RenameColumns(JoinedStores, {
{"StoreID", "StoreKey"},
{"StoreName.1", "StoreNameOperational"},
{"StoreName", "StoreNameFinancial"}
}),
// Apply the canonical store name (Ops system has the official name)
AddedCanonicalName = Table.AddColumn(RenamedColumns, "StoreName",
each [StoreNameOperational],
type text),
// Add region hierarchy (lookup from the region mapping table)
RegionMapping = RegionCodes_Raw,
WithRegion = Table.Join(
AddedCanonicalName, {"RegionCode"},
RegionMapping, {"RegionCode"},
JoinKind.LeftOuter
),
FinalStore = Table.SelectColumns(WithRegion, {
"StoreKey", "StoreName", "StoreType", "RegionCode",
"RegionName", "DistrictName", "StateCode", "OpenDate",
"SquareFootage", "CostCenterCode", "ProfitCenterCode"
})
in
FinalStore
Key insight: Notice that both ERPStores and OpsStores are references to other entities within the same dataflow. Power BI reads both from their respective materialized Parquet files and performs the join in-memory on the Premium capacity. The source databases are not touched during this computed entity's execution. This is the performance advantage that makes layered dataflows viable for large datasets.
Building the Enrichment Layer
The enrichment layer is where domain teams create the denormalized, aggregated, or otherwise domain-specific datasets that their reports need. These are still computed entities, but they may aggregate or reshape the conformed data.
Creating a Pre-Aggregated Sales Summary
For executive dashboards that always show sales at store-day-product granularity, maintaining a pre-aggregated entity avoids scanning transaction-level data every refresh:
let
ConformedSales = SalesTransaction_Conformed,
StoreDim = Store_Conformed,
// Aggregate to store-date-product granularity
GroupedSales = Table.Group(
ConformedSales,
{"DateKey", "FiscalYear", "StoreID", "ProductSKU", "TransactionTier"},
{
{"TransactionCount", each Table.RowCount(_), Int64.Type},
{"TotalQuantity", each List.Sum([Quantity]), Int64.Type},
{"GrossSaleAmount", each List.Sum([TotalAmount]), type number},
{"NetSaleAmount", each List.Sum([NetSaleAmount]), type number},
{"TotalDiscountAmount", each List.Sum([DiscountAmount]), type number},
{"TotalTaxAmount", each List.Sum([TaxAmount]), type number}
}
),
// Enrich with store attributes for direct use in reports
WithStoreAttributes = Table.Join(
GroupedSales, {"StoreID"},
StoreDim, {"StoreKey"},
JoinKind.LeftOuter
),
FinalSummary = Table.SelectColumns(WithStoreAttributes, {
"DateKey", "FiscalYear", "StoreID", "StoreName", "RegionName",
"DistrictName", "ProductSKU", "TransactionTier",
"TransactionCount", "TotalQuantity", "GrossSaleAmount",
"NetSaleAmount", "TotalDiscountAmount", "TotalTaxAmount"
})
in
FinalSummary
This entity can serve dashboards directly without the semantic model needing to aggregate 50M rows on every query — instead, the model imports a manageable pre-aggregated dataset and handles period comparisons, ranking, and other analytical calculations in DAX.
Refresh Sequencing and Dependency Management
One of the most operationally important aspects of a layered dataflow architecture is getting the refresh order right. If a computed entity refreshes before its source entity, it reads stale Parquet data from the previous cycle.
Separate Dataflow Approach
For serious enterprise deployments, put each layer in a separate dataflow:
- Dataflow A: Ingestion (standard entities, connects to source systems)
- Dataflow B: Conformation (computed entities referencing Dataflow A entities)
- Dataflow C: Enrichment (computed entities referencing Dataflow B entities)
Configure scheduled refresh so that Dataflow A runs first, then B, then C. Power BI's dataflow refresh scheduling doesn't have a built-in dependency chain mechanism — you'll need to chain these via Power Automate or the Power BI REST API.
Here's the Power Automate flow pattern:
- Schedule trigger fires at 2:00 AM
- Call Power BI REST API to trigger Dataflow A refresh:
POST /groups/{workspaceId}/dataflows/{dataflowId}/refreshes
- Poll refresh status until complete:
GET /groups/{workspaceId}/dataflows/{dataflowId}/transactions
- If Dataflow A succeeded, trigger Dataflow B refresh
- If Dataflow B succeeded, trigger Dataflow C refresh
- Send success/failure notification via email or Teams
// Trigger refresh request body
{
"notifyOption": "MailOnFailure"
}
// Status response - check for "Success" or "Failed"
{
"value": [
{
"id": "refresh-guid",
"refreshType": "OnDemand",
"startTime": "2024-01-15T02:00:12.4Z",
"endTime": "2024-01-15T02:47:33.1Z",
"status": "Success"
}
]
}
Tip: Build in a 10-15% time buffer when polling for completion. If Dataflow A's refresh typically takes 45 minutes, don't set a fixed 45-minute wait before triggering B — use a polling loop with a 2-minute interval that checks actual status. This makes your pipeline resilient to variance in source system query performance.
Cross-Workspace Entity References
Gen2 supports referencing entities from dataflows in different workspaces, which is useful when the ingestion layer is owned by a central data engineering team and enrichment layers are owned by domain teams. In the Power Query Online editor, when you add a new data source, select Power Platform dataflows and navigate to the entity in another workspace.
The permissions model here is important: the workspace identity that the consuming dataflow runs under must have at least Member access to the workspace containing the source dataflow. This is worth coordinating with your Power BI governance team to formalize as a policy.
A transformation layer is only as valuable as the trust it earns from its consumers. Governance isn't bureaucracy — it's the mechanism by which that trust is established and maintained.
Endorsement and Certification
Mark your conformation and enrichment layer dataflows as Promoted or Certified using Power BI's endorsement system. Certified dataflows require workspace admin approval and show a certification badge in all consumption experiences. This creates a clear visual signal to report authors that "this entity has been validated by the data team."
For a complete walkthrough of the certification workflow that makes this meaningful rather than ceremonial, see Implementing Power BI Dataset Certification and Endorsement Workflows to Establish a Trusted Enterprise Data Catalog.
Lineage Tracking
In the Power BI service, use the Lineage view to visualize how data flows from dataflows through semantic models to reports. For enterprise deployments where you have dozens of entities feeding multiple semantic models, maintaining this lineage visibility is essential for impact analysis — if you need to change the NetSaleAmount calculation, you need to know which downstream reports will be affected before you make the change. The detailed patterns for managing this are covered in Implementing Power BI Workspace-Level Lineage and Impact Analysis to Manage Dataset Dependencies Across the Enterprise.
Access Control Patterns
For the ingestion layer dataflow, only the central data engineering team should have edit access. The workspace should be configured so that domain teams have Read access to consume entities but cannot modify or add queries.
For enrichment layer dataflows, the owning domain team gets edit access within their workspace. But their dataflow can only reference the conformed entities from the central workspace — they can't reach past the conformation layer to raw data.
Note: Power BI doesn't provide row-level security at the Dataflow entity level — all consumers of an entity see all rows. Row-level security is applied in the semantic model, not in the dataflow. If your source data requires RLS before it even reaches an entity, you'll need to filter it using credentials-based row filtering in the ingestion query rather than a security model. If this is a significant concern in your architecture, review Row-Level Security in Power BI to understand where in the stack security should be applied.
Running a dataflow refresh that takes six hours when it should take forty-five minutes is a common early-stage problem. Here are the patterns that actually move the needle.
Column Pruning Early
Every column you carry through the pipeline consumes memory during transformation. In your ingestion entities, remove columns that no downstream consumer will ever need. Source systems often have dozens of operational columns (audit flags, internal routing codes, legacy deprecated fields) that should be dropped at ingestion.
Predicate Pushdown Awareness
Power Query Online will attempt to push filters to the data source when the connector supports it. However, some M transformations break folding and cause Power BI to pull the entire dataset and filter in-memory. Use the View native query option (right-click on a step in the Applied Steps panel) to verify that your filter steps are folding back to SQL.
Common folding breakers in dataflows:
Table.Buffer() — explicitly prevents folding downstream- Any custom M function that isn't a direct call to a connector-supported operation
List.Contains() with a dynamic list- Joining an in-memory table to a source query
Parallel Execution of Independent Entities
Entities within a dataflow that have no dependencies between them can refresh in parallel. The Mashup Engine will identify these automatically. However, if you unnecessarily reference one entity from another (even just to reuse a configuration value), you create a dependency that forces sequential execution.
Structure your ingestion dataflow so that each source table is its own independent entity with no cross-references. The conformation layer is where the joining happens — don't try to join at ingestion.
Track your dataflow refresh times over time using the Power BI Admin API, which surfaces dataflow refresh history. Unexplained refresh time increases often indicate that a source system query has degraded (missing index, statistics out of date) or that your data volume has crossed a threshold that pushes you into a different performance tier.
For capacity-level performance monitoring patterns, see Monitoring Power BI Performance with Premium Metrics: A Complete Guide to Proactive Optimization.
Consuming Dataflow Entities in Semantic Models
Your beautifully constructed transformation layer is only useful if report authors can consume it efficiently.
Import Mode Connection
In Power BI Desktop, connect to a Dataflow entity via Get Data > Power Platform > Dataflows. Navigate to your workspace and select the conformed or enrichment entities you need. In import mode, Desktop fetches the Parquet data from ADLS Gen2 via the Power BI service — you don't need a gateway for dataflow-to-dataset connections even if the original source required one.
If you're also connecting to on-premises sources alongside dataflow entities, the guidance in Connecting Power BI Desktop to SQL Server, SharePoint, and Azure Data Lake Storage covers the credential management nuances.
DirectQuery Mode on Dataflow Entities
Gen2 enables DirectQuery connections from semantic models to dataflow entities, reading directly from the Parquet files in ADLS Gen2. This is valuable when:
- The entity is very large and import would create an oversized semantic model
- You need the semantic model to always reflect the latest dataflow refresh without its own scheduled import
- You're building a composite model that mixes import and DirectQuery sources
The trade-off is query performance: even though Parquet is columnar and efficient, DirectQuery through Power BI adds overhead compared to querying an in-memory imported table. For most analytical workloads with aggregation-heavy queries, DirectQuery on dataflows will be measurably slower than import mode. Test this with representative query patterns before committing to DirectQuery for a widely-used dataset.
For the broader context of when DirectQuery makes architectural sense, Composite Models and DirectQuery: When to Use Which in Power BI provides the framework for that decision.
Hands-On Exercise
This exercise will take you approximately 90 minutes if you have the prerequisites in place. Work through it in a Premium capacity workspace with ADLS Gen2 connected.
Scenario: Build a two-layer dataflow architecture for a hypothetical retail company using publicly available data.
Part 1: Ingestion Layer (30 minutes)
Create a new Dataflow Gen2 named Ingestion_Retail in your workspace.
Create an entity named Orders_Raw using this OData source (a public sample):
https://services.odata.org/V4/Northwind/Northwind.svc/Orders
Apply only type corrections — no business logic. Remove any navigation properties.
Create a second entity named OrderDetails_Raw from:
https://services.odata.org/V4/Northwind/Northwind.svc/Order_Details
Create a third entity named Products_Raw from the Products endpoint of the same service.
Publish and trigger a manual refresh. Verify in Azure Storage Explorer that three folders appeared in your ADLS container with Parquet files inside.
Part 2: Conformation Layer (45 minutes)
Create a second Dataflow Gen2 named Conformed_Retail.
Add a data source using Power Platform > Dataflows and navigate to your Ingestion_Retail dataflow. Select Orders_Raw — this creates a computed entity.
Add the OrderDetails_Raw entity the same way.
Create a new computed entity named Sales_Conformed that:
- Joins
Orders_Raw and OrderDetails_Raw on OrderID
- Calculates
LineTotal = Quantity * UnitPrice * (1 - Discount)
- Adds a
FiscalYear column (assume fiscal year = calendar year for simplicity)
- Removes internal columns (
ShipRegion, ShipPostalCode, ShipCountry from Orders)
- Applies data type corrections throughout
Publish and refresh. Verify that the computed entity created its own subfolder in ADLS with its own Parquet file.
In Power BI Desktop, connect to Sales_Conformed via Get Data > Power Platform > Dataflows. Load it and verify the row count matches what you'd expect from the source.
Part 3: Validation (15 minutes)
In the Power BI service, navigate to the Lineage view for your workspace. You should see a chain: Ingestion_Retail → Conformed_Retail.
Trigger a manual refresh of Conformed_Retail without first refreshing Ingestion_Retail. Observe that the data is still from the previous Ingestion_Retail refresh — the computed entity read from the stored Parquet, not from the live OData source.
Change a transformation in Sales_Conformed (add a dummy column, for example) and republish. Observe that the change appears in the entity output after refresh without affecting the Ingestion_Retail dataflow.
Common Mistakes & Troubleshooting
Mistake 1: Creating Circular References
If Entity B references Entity A, and Entity A references Entity B, Power BI will detect the circular dependency and refuse to publish. This seems obvious, but it emerges in subtle ways: a computed entity references another computed entity that was refactored to reference back. Always map your entity dependency graph before making structural changes.
Mistake 2: Skipping the Staging Step for Heavy Joins
When joining two large entities in a computed entity, if you haven't materialized both source entities first, the join operation can hit memory limits on your Premium capacity. If a join consistently fails, add a Table.Buffer() call around the smaller table in the join to force it into memory before the join executes. Yes, Table.Buffer() breaks query folding — but you're already in computed entity territory reading from Parquet, so there's nothing to fold to.
Mistake 3: Assuming Storage Access Is Automatic
After connecting ADLS at the tenant level, workspace-level storage assignment doesn't happen automatically. You must explicitly configure each workspace's dataflow storage setting, or it will use the tenant default. If multiple workspaces accidentally share the same container prefix, their entity folders can collide.
Mistake 4: Not Handling Refresh Failures Gracefully
A failed ingestion entity refresh leaves the previous Parquet files in place. The next refresh of the conformation layer will then read stale data and succeed — which is worse than failing visibly, because consumers see yesterday's data without knowing it. Build explicit refresh status monitoring using the Power BI REST API to detect this scenario and alert before downstream consumers notice.
The conformation layer should produce universally agreed-upon, domain-agnostic data. As soon as you start adding Finance-specific calculations to a shared conformation entity, Finance teams are happy but the metric becomes meaningless for Operations. Resist the temptation to add domain-specific logic at the conformation layer. Push it into domain-specific enrichment entities owned by those teams.
Troubleshooting: "We couldn't connect to the dataflow" Error
This error during computed entity preview usually means the source entity hasn't been materialized yet. Publish the source dataflow, trigger a manual refresh, verify the Parquet files exist in ADLS, then return to the computed entity and retry the preview.
Troubleshooting: Refresh Times Increasing Week-Over-Week
If your incremental refresh window is set correctly but refresh times are growing, check whether your source system's modification-tracking column is properly indexed. Without an index on ModifiedAt, the source database performs a full table scan even though your query is selective. Work with the DBA to add a filtered index on the modification column.
Summary & Next Steps
You've now covered the complete architecture of a production Dataflows Gen2 implementation: ADLS Gen2 storage integration, layered transformation design using standard and computed entities, incremental refresh for large volumes, cross-dataflow dependencies, refresh orchestration via REST API, and the governance patterns that make the whole system trustworthy.
The key architectural principles to carry forward:
Layer strictly. Ingestion, conformation, and enrichment serve different masters. Keep them separate, and resist collapsing layers when timelines compress — you'll pay the debt later.
Computed entities read from storage. This is the performance model. Everything upstream must materialize before downstream computed entities refresh.
Governance enables adoption. Certification, endorsement, and lineage visibility are what converts a technically correct solution into one that the organization actually trusts and uses.
Orchestrate refresh explicitly. Don't rely on scheduled refresh alignment across dataflows — use the Power BI REST API to build an actual dependency-aware refresh pipeline.
Where to go next:
