Power BI Power Query: Enterprise Guide 2026

Power BI Power Query Enterprise Data Transformation Guide (2026)

Power Query (M language) is the data ingestion and transformation engine in Power BI, Microsoft Fabric, Excel, Power Apps, Power Automate, and Microsoft Dataverse. This is the working enterprise Power Query playbook EPC Group uses for Fortune 500 deployments — connector strategy, M-language patterns, query folding, dataflow architecture, governance, and Microsoft Fabric integration.

EPC Group has delivered Power Query / M / dataflow engagements for Fortune 500 healthcare, financial services, government, manufacturing, and technology since the Power BI Project Crescent beta (2010-2013).

TL;DR — Power Query Architecture Decisions

Decision	Default
Where to transform	Push to source (T-SQL) > Power Query > DAX measure
Ingestion topology	Microsoft Fabric Dataflow Gen2 for shared, semantic-model Power Query for solo
Query folding	Always preferred — verify with Native Query view
Storage mode	Import for small (<5GB), DirectQuery for live, DirectLake for OneLake-anchored
Refresh strategy	Incremental refresh for large fact tables, full for dimensions
Composite models	Mix Import + DirectQuery for performance + freshness balance

Phase 1: Connector Strategy

Microsoft First-Party Connectors

200+ first-party connectors across:

• Microsoft 365 (SharePoint, OneDrive, Exchange, Microsoft Graph)
• Microsoft Dynamics 365
• Microsoft Dataverse
• Azure (SQL, Synapse, Data Lake, Cosmos, Blob, Cognitive Services)
• Microsoft Fabric (Lakehouse, Warehouse, OneLake, KQL)
• SQL Server (on-premises, with gateway)
• Microsoft Power Platform

Third-Party Connectors

500+ third-party connectors:

• Salesforce, ServiceNow, Workday, SAP HANA / S/4HANA / BW
• Oracle, Teradata, IBM Db2, MySQL, PostgreSQL
• Snowflake, Databricks, BigQuery, Redshift
• Amazon S3, Azure Data Lake, Google Cloud Storage
• Marketing tools (HubSpot, Marketo, Mailchimp)
• Finance tools (QuickBooks, Xero, NetSuite, Sage)

Custom Connectors

For systems without built-in connectors:

• Power Query Custom Connector (M language extension)
• Microsoft Power Platform Custom Connector (REST/SOAP)
• ODBC custom driver
• Web API via Web.Contents in M

Phase 2: M Language Patterns

Query Folding

Query folding is the M language compiler pushing transformations to the source as native query. Without folding:

• Source queries inefficient (full table scan)
• Refresh time scales with data volume
• DirectQuery experience degrades

With folding:

• Source executes optimized native query
• Refresh time scales with result size
• DirectQuery transparent

Always verify folding via "View Native Query" in Power Query Editor.

Common Folding-Compatible Operations

Most filtering, joining, aggregating, and column operations fold to T-SQL:

• Table.SelectRows (WHERE)
• Table.NestedJoin / Table.Join (JOIN)
• Table.Group (GROUP BY)
• Table.AddColumn with simple expressions
• Table.Sort (ORDER BY)
• Table.Distinct (DISTINCT)
• Table.RenameColumns
• Table.SelectColumns (column projection)

Folding-Breaking Operations

Avoid these in early steps (they break folding):

• Table.AddColumn with custom M function
• Table.Buffer (forces local execution)
• Table.PromoteHeaders on certain sources
• Custom M functions
• Iterative operations

If you must use these, apply them last in the query (after folding-compatible operations).

Performance Patterns

• Filter early (push WHERE clauses to source)
• Project columns early (push SELECT to source)
• Aggregate at source (push GROUP BY to source)
• Avoid wide tables (drop unused columns)
• Use Table.Buffer only for small lookup tables

Phase 3: Dataflow Architecture

Microsoft Fabric Dataflow Gen2

Modern Power Query at enterprise scale:

• Microsoft Fabric capacity-anchored compute
• Output to OneLake Lakehouse / Warehouse
• Shared across multiple consumers
• CI/CD via Microsoft Fabric Git integration
• Governance via Microsoft Purview

Power BI Dataflow (Gen1)

Legacy pattern for shared Power Query at workspace scale:

• Power BI Premium / PPU capacity-anchored
• Output to Common Data Model (CDM) folder in ADLS Gen2
• Shared across Power BI workspaces
• Source for semantic models via Dataflow connector

EPC Group recommends Dataflow Gen2 (Microsoft Fabric) over Gen1 for new deployments.

Dataflow Topology

Standard hub-and-spoke:

Source Systems (SQL, Snowflake, Salesforce)
  → Dataflow Layer 1: Raw Ingestion (minimal transformation)
     → OneLake Bronze
  → Dataflow Layer 2: Cleaned / Conformed
     → OneLake Silver
  → Dataflow Layer 3: Business-Aligned Marts
     → OneLake Gold
  → Power BI Semantic Models (Import / DirectLake)
  → Power BI Reports

Medallion Architecture in OneLake

• Bronze (raw) — preserved source-fidelity, minimal transformation
• Silver (conformed) — cleaned, joined, deduplicated, sensitivity-labeled
• Gold (business) — star-schema, business-aligned, ready for Power BI

Phase 4: Storage Mode Selection

Import Mode

Best for: Small to medium datasets (<5GB compressed), low-latency queries, predictable data freshness via scheduled refresh.

Pattern: Power Query → Power BI compressed in-memory model → fast queries.

DirectQuery Mode

Best for: Large datasets, real-time freshness needs, security pushed to source.

Pattern: Power Query → DirectQuery (no import) → live queries to source on each interaction.

Trade-off: Slower visual queries, but real-time data and no refresh cycles.

DirectLake Mode (Microsoft Fabric)

Best for: Microsoft Fabric Lakehouse / Warehouse data, Power BI Copilot use cases, low-latency over large data.

Pattern: Power Query writes to OneLake Delta tables → Power BI semantic model in DirectLake mode reads Delta directly.

Performance: 60-80% faster than Import for typical Fortune 500 workloads.

Composite Models

Mix Import + DirectQuery + DirectLake in same semantic model:

• Import for dimensions (small, predictable)
• DirectQuery for fact tables (real-time, large)
• DirectLake for OneLake-anchored facts (low-latency)

Phase 5: Incremental Refresh

When to Use

For fact tables larger than 1GB or refresh windows under 30 minutes.

Configuration

RangeStart and RangeEnd parameters
Filter on date column: [Date] >= RangeStart AND [Date] < RangeEnd
Configure incremental refresh policy:
  - Archive period: 5 years (data retained)
  - Refresh period: 7 days (data refreshed)
  - Detect data changes: optional, requires "modified" column
  - Enable real-time refresh with DirectQuery on incremental partitions: optional

Common Patterns

• Healthcare encounter table — archive 7 years, refresh last 30 days
• Financial transaction table — archive 7 years, refresh last 90 days
• Manufacturing sensor data — archive 1 year, refresh last 7 days
• Web log data — archive 90 days, refresh last 24 hours

Phase 6: Governance

Sensitivity Labels

• Inherit from source (when source has Microsoft Purview labels)
• Apply at Power BI semantic model
• Apply at dataflow level
• Microsoft Copilot grounding scope respects labels

Data Lineage

Microsoft Purview Data Map captures Power Query lineage:

• Source tables → dataflows → semantic models → reports
• M-language transformations documented
• Refresh history logged

Workspace Organization

• Department workspaces for ownership
• Shared dataflow workspace for cross-department dataflows
• Capacity assignment per workspace
• Microsoft Entra security group-based access

Phase 7: Microsoft Fabric Integration

Microsoft Fabric Lakehouse

Power Query writes to Microsoft Fabric Lakehouse in Delta Lake format:

• Open table format (Apache Iceberg / Delta)
• Cross-engine readable (Power BI, Synapse Spark, Microsoft Fabric Warehouse)
• Microsoft Purview lineage captured
• Microsoft Sentinel audit log

Microsoft Fabric Warehouse

Power Query writes to Microsoft Fabric Warehouse:

• T-SQL accessible (compatible with traditional warehouse tooling)
• DirectLake-mode Power BI semantic models
• Microsoft Purview lineage
• T-SQL row-level security and column-level security

Microsoft Copilot in Power Query

Power Query Copilot (preview):

• Natural-language-to-M-code generation
• Transformation suggestions based on data patterns
• Error explanation and correction
• Documentation generation for queries

Frequently Asked Questions

Should I use Power Query in Power BI Desktop or in dataflows?

For shared transformations across multiple semantic models or workspaces, use Microsoft Fabric Dataflow Gen2. For semantic-model-specific transformations, use Power BI Desktop. EPC Group standard pattern: shared dimensions and base facts in dataflows; model-specific transformations in semantic model.

How do I optimize refresh times?

1. Verify query folding (View Native Query)
2. Push filtering and aggregation to source
3. Drop unused columns early
4. Use incremental refresh for large fact tables
5. Pre-aggregate at source via stored procedure or view
6. Move complex transformations to Microsoft Fabric Dataflow Gen2 with capacity-anchored compute

What about M language vs DAX vs T-SQL?

• T-SQL at source: best for everything that can fold to source
• M (Power Query): for transformations that don't fold or for shared dataflow logic
• DAX: for measures, calculated columns based on relationships, KPIs

How do we govern dataflows at scale?

Microsoft Fabric workspaces with Microsoft Entra security group access. CI/CD via Microsoft Fabric Git integration. Microsoft Purview lineage and sensitivity labels. Microsoft Sentinel audit ingestion.

Does Power Query work for regulated industries?

Yes. Healthcare (HIPAA), financial services (FINRA, SEC), government (FedRAMP), and pharma (GxP) deploy Power Query / dataflows successfully. The combination with Microsoft Purview (lineage, sensitivity labels, audit) makes Power Query a foundational tool for regulated analytics.

What about Microsoft Copilot for Power Query?

Microsoft Power Query Copilot (preview) provides natural-language-to-M-code generation, transformation suggestions, and error correction. Available in Power BI Desktop and Microsoft Fabric Dataflow Gen2 with appropriate capacity tier.

Who delivers Power Query engagements?

EPC Group senior architects with Power BI / Power Query experience since the Project Crescent beta (2010-2013). Errin O'Connor was on the original Microsoft Power BI beta team and is a 4-time Microsoft Press author including a Power BI book.

Next Steps

Schedule a 30-minute Power Query / dataflow discovery call at /schedule or call (888) 381-9725. Senior architects (not sales) take discovery calls.

Related reading: Microsoft Fabric Quickstart Assessment, Microsoft Fabric Consulting Services Guide, Power BI Row-Level Security Enterprise Guide, Power BI Premium Pricing Licensing Guide, and Audit-Ready Analytics Compliance Framework Guide.