Power BI dataflows handle cloud-based ETL (extract, transform, load) processes inside the Power BI service using Power Query Online. Instead of writing duplicate transformation logic across multiple desktop reports, dataflows store prepared data in your organization's Azure Data Lake Storage Gen2. This architecture decouples data preparation from report authoring, which eliminates redundant data refreshes and standardizes business logic across teams.
Understanding Dataflow Architecture
A dataflow is a collection of tables created and managed inside workspaces in the Power BI service. When you build a dataflow, you connect to raw sources, apply transformation steps using Power Query Online, and store the resulting tables as entities.
- Staging Entities: Used to stage raw data quickly without heavy transformations.
- Standard Entities: Contain structured, cleaned data ready for consumption.
- Analytical Entities: Optimized for querying and reporting performance.
When downstream semantic models connect to a dataflow rather than original database servers, query load on production operational systems drops significantly. Multiple developers can pull identical, pre-cleansed tables into their individual Power BI Desktop files, ensuring that sales calculations or customer categorizations match across every department.
Building Your First Dataflow
Creating a dataflow requires a Power BI Pro or Premium Per User license, along with workspace contributor or admin permissions. Follow these steps to configure a functional dataflow from scratch:
- Navigate to the Power BI service, open your target workspace, and select New followed by Dataflow.
- Choose Add new tables to launch the Power Query Online interface.
- Select your data source connector—such as Microsoft SQL Server, Salesforce, or an enterprise REST API—and provide your authentication credentials.
- Apply necessary transformation steps using the Power Query ribbon, including removing unnecessary columns, changing data types, merging tables, and filtering rows.
- Rename your queries with clear, standardized naming conventions before saving and naming the dataflow.
- Configure the automatic refresh schedule under workspace settings to keep your cloud entities up to date.
Managing Incremental Refresh
Handling millions of rows requires incremental refresh to avoid long gateway timeouts and high memory consumption. Instead of reloading the entire dataset every night, incremental refresh partitions your data so that only new or modified rows are processed.
To set up incremental refresh for a dataflow entity, define RangeStart and RangeEnd date parameters in Power Query. Configure the table filtering to use these parameters against your date column. Once published to a workspace with Power BI Premium or Fabric capacity, you can enable incremental refresh settings. Specify the archive period (for example, store historical data for five years) and the refresh period (update data for the past ten days). This setup ensures nightly refreshes finish in minutes instead of hours.
Computed Entities and Linked Entities
Dataflows support advanced entity types that help organize large-scale data pipelines.
A Linked Entity points to an entity created in another dataflow within the same tenant. This allows you to reuse clean dimension tables—like a standard date table or employee hierarchy—across multiple workspaces without duplicating upstream ingestion logic.
A Computed Entity takes data from an existing entity within the same dataflow and performs downstream calculations, such as aggregations, joins, or conditional columns. Computed entities require Power BI Premium or Fabric capacity to execute, but they streamline multi-step data warehousing architectures entirely within the cloud service.
Common Implementation Mistakes
Teams transitioning from desktop Power Query to service-based dataflows often run into recurring performance and architectural pitfalls:
- Over-transforming in staging tables: Performing complex merges or heavy pivoting on raw sources before staging can slow down refresh times. Keep initial extraction lightweight.
- Ignoring gateway configuration: On-premises data sources require an enterprise data gateway. Failing to map credentials correctly or keeping gateway machines underpowered leads to silent refresh failures.
- Poor naming conventions: Leaving tables with default names like
Query1orTable_final_v2creates confusion when downstream report builders try to map fields. - Neglecting row-level security (RLS): Dataflows store data globally for the workspace. If sensitive employee or financial metrics are ingested into a shared dataflow without proper masking, unauthorized users with workspace access might view restricted figures.
Frequently Asked Questions
Do Power BI dataflows replace data warehouses?
Dataflows serve as an agile staging and ETL layer, but they don't fully replace enterprise data warehouses for heavy historical storage, complex indexing, or multi-system ACID transactions. They work best as an agile semantic preparation layer sitting on top of a warehouse or operational database.
What is the difference between standard and analytical entities?
Analytical entities are stored in Common Data Model (CDM) format inside Azure Data Lake Storage Gen2 in Parquet files. This format accelerates query performance and enables direct querying via external tools like Azure Synapse Analytics. Standard entities store data in internal storage without these advanced parquet optimizations.
Can I use Power BI Desktop to edit dataflows?
You cannot directly edit a dataflow's Power Query steps inside Power BI Desktop. You must edit dataflows through the web-based Power Query Online interface in the Power BI service. However, you can easily connect Power BI Desktop models to published dataflows to build reports.
How do dataflows handle credentials for secure APIs?
You configure authentication credentials per data source directly in the Power BI service settings. Dataflows support OAuth2, API keys, basic authentication, and organizational account logins, encrypting credentials securely at rest.
