When building reports in Power BI, most of the real work happens before the visual even loads. Data must be cleaned, joined, aggregated, and shaped. In Microsoft Fabric, you have three powerful options for this transformation layer: Power Query Online, Dataflows Gen2, and Spark Notebooks. But which one should you use? 

The answer depends on your data volume, complexity, team skills, and performance requirements. Let me give you a simple decision framework. 

1. Power Query Online – Best for Simple, Self-Service Transformations 

Power Query Online is the familiar Excel/Power BI transformation engine, now running natively in Fabric. Use it when: 

• Your data fits in memory (typically under 1-2 GB after compression) 

• You’re a business analyst or power user without coding experience 

• You need quick column splits, merges, filters, or unpivots 

• Your source is a single Excel file, CSV, or small database table 

Example: A marketing manager cleaning a weekly campaign CSV file before loading into a Lakehouse. 

Limitations: Power Query Online does not scale well to billions of rows. It also struggles with complex joins or iterative logic. 

2. Dataflows Gen2 – Best for Reusable, Low-Code ETL at Scale 

Dataflows Gen2 is the evolution of Power BI Dataflows, now backed by Fabric’s high-performance engine. Use it when: 

• You need to reuse the same transformation logic across multiple reports 

• Your data is large (millions to billions of rows) but transformations are declarative 

• You want automatic query folding to push work back to the source 

• Your team prefers a visual, no-code interface but needs enterprise scalability 

Dataflows Gen2 automatically uses Spark behind the scenes. You can still use Power Query Online’s interface, but execution happens in a distributed environment. 

Example: An ETL developer building a reusable “customer master” dataflow that merges CRM and ERP data, then feeds five different Power BI reports. 

Limitations: Complex business logic (loops, custom scoring, advanced statistics) is difficult or impossible without code. 

3. Notebooks – Best for Complex, Code-First Data Engineering 

Notebooks (PySpark, Spark SQL, Scala, or C#) give you full programmatic control. Use them when: 

• You need advanced logic: window functions, machine learning, custom aggregations, or iterative algorithms 

• Your data volume exceeds 10+ billion rows (Petabyte scale) 

• Your team includes data engineers or data scientists comfortable with Python/SQL 

• You require version control (Git) and CI/CD pipelines for transformations 

Notebooks also give you direct access to OneLake, meaning you can read and write Delta tables efficiently. 

Example: A data engineer building a slowly changing dimension (SCD Type 2) for a 500-million-row sales fact table, with custom error handling and logging. 

Limitations: Steeper learning curve; overkill for simple CSV cleaning. 

The Decision Matrix – Quick Reference 

A Practical Rule of Thumb 

Start with Dataflows Gen2 as your default. If you hit a limitation (complex logic, performance, or fine-grained control), drop down to Notebooks. Use Power Query Online only for personal or one-off work that doesn’t need to be productionized. 

In our Fabric practice at [Your Company Name] , we’ve seen teams waste weeks trying to force complex Python logic into Power Query. Conversely, we’ve seen data engineers write notebooks to pivot a 10-row Excel file. Both are mistakes. 

Choose wisely. Your Power BI reports – and your sanity – will thank you.