Power BI Power Query

EPC Group provides enterprise Microsoft consulting on Power BI Power Query Enterprise Data Transformation Guide 2026. Senior-architect-led engagements since 1997 for Fortune 500 healthcare, finance, and government clients. Fixed-fee accelerators + compliance-native delivery across HIPAA, SOC 2, FedRAMP, StateRAMP, CJIS.

Key Facts

6,500+ SharePoint implementations delivered by EPC Group
1,500+ Power BI enterprise deployments
11,000+ total enterprise engagements since 1997
Microsoft Solutions Partner with all six core designations
Founder Errin O'Connor: 4x bestselling author (Microsoft Press / Sams)
Regulated-industry experience: HIPAA, SOC 2, FedRAMP, StateRAMP, CJIS, ITAR

Last updated October 27, 2026 by Errin O'Connor, Founder & Chief AI Architect, EPC Group

What Is Power Query in Power BI?

Quick Answer: Power Query is the data transformation engine built into Power BI that connects to hundreds of data sources, cleans and reshapes data, and loads it into your data model. It uses the M language to create repeatable, auditable data pipelines that re-execute on every refresh. Think of Power Query as your ETL (Extract, Transform, Load) layer — it handles everything between your raw data sources and your clean, analysis-ready data model.

Every Power BI report starts with data, and every enterprise dataset requires transformation. Raw data from ERP systems, databases, APIs, and spreadsheets is never in the shape you need for analysis. Column names are inconsistent, data types are wrong, tables need joining, duplicates need removing, and business logic needs applying. Power Query solves all of this.

Power Query is not just a feature of Power BI — it is the foundation of every enterprise data pipeline. In Power BI consulting work since 1997, EPC Group has found that data preparation takes a large share of project time. Organizations that master Power Query shorten that work and build data models that are dramatically easier to maintain.

The Power Query engine is shared across Power BI Desktop, Power BI Service (Dataflows), Excel, Azure Data Factory, and Microsoft Fabric. Skills you build in Power Query transfer across the entire Microsoft data stack — making it one of the highest-ROI skills for any data professional.

The Power Query Editor Interface

Access the Power Query Editor in Power BI Desktop via Home > Transform Data. Understanding its five key areas accelerates enterprise development.

Queries Pane (Left)

Lists all queries organized in groups. Use folders to separate staging queries, dimension tables, fact tables, and function libraries. Right-click to enable/disable loading, create references, and manage dependencies.

Pro Tip: Create groups: "1-Staging", "2-Dimensions", "3-Facts", "4-Functions" for clean organization.

Data Preview (Center)

Shows a preview of the current query results. Column headers display data types (ABC for text, 123 for number, calendar for date). Green bars under headers indicate data quality — gaps mean nulls or errors.

Pro Tip: Column profiling (View > Column Quality/Distribution/Profile) reveals data quality issues immediately.

Applied Steps (Right)

Every transformation is recorded as a named step. Steps execute sequentially — each one transforms the output of the previous step. You can click any step to see intermediate results, insert steps, or delete steps.

Pro Tip: Rename every step descriptively. "Filtered to active customers" beats "Filtered Rows" when debugging.

Formula Bar (Top)

Displays the M code for the currently selected step. Edit M directly here for simple changes. For complex edits, use the Advanced Editor (Home > Advanced Editor) which shows the full query as a let/in expression.

Pro Tip: Toggle the formula bar via View > Formula Bar. Always keep it visible during development.

Ribbon (Top)

Contains transformation commands organized by tab: Home (common operations), Transform (column-level changes), Add Column (new calculated columns), View (display settings). Most GUI actions generate M code automatically.

Pro Tip: Right-click column headers for the most common transformations — faster than navigating the ribbon.

Query Settings (Right Panel)

Shows the query name, description, and "All Properties" including whether the query loads to the data model. Set "Enable load" to false for staging queries that other queries reference but that should not appear in the model.

Pro Tip: Add descriptions to every query explaining its purpose, source, and refresh behavior.

Common Power Query Transformations

Data Cleaning

Operation	M Code	Use Case	Folds?
Remove Duplicates	Table.Distinct(source, {"Column1"})	Deduplication of customer records, transaction IDs	Yes (SQL DISTINCT)
Replace Values	Table.ReplaceValue(source, "old", "new", Replacer.ReplaceText, {"Column1"})	Standardize status codes, fix known data quality issues	Yes (SQL REPLACE)
Remove Errors	Table.RemoveRowsWithErrors(source, {"Column1"})	Clean failed type conversions, null lookups	No
Trim & Clean	Table.TransformColumns(source, {{"Name", Text.Trim}})	Remove whitespace from imported text fields	Partial (LTRIM/RTRIM)

Data Shaping

Operation	M Code	Use Case	Folds?
Unpivot Columns	Table.UnpivotOtherColumns(source, {"ID"}, "Attribute", "Value")	Convert crosstab/matrix layouts to normalized rows	No
Pivot Column	Table.Pivot(source, List.Distinct(source[Attribute]), "Attribute", "Value")	Convert row data to column headers (denormalize)	No
Group By	Table.Group(source, {"Category"}, {{"Total", each List.Sum([Amount])}})	Pre-aggregate data to reduce model size	Yes (SQL GROUP BY)
Split Column	Table.SplitColumn(source, "FullName", Splitter.SplitTextByDelimiter(" "))	Parse combined fields (name, address, codes)	No

Data Combination

Operation	M Code	Use Case	Folds?
Merge Queries (Join)	Table.NestedJoin(source, {"Key"}, other, {"Key"}, "Joined", JoinKind.LeftOuter)	Lookup values from reference tables, enrich records	Yes (SQL JOIN)
Append Queries (Union)	Table.Combine({table1, table2, table3})	Stack data from multiple sources with same schema	Yes (SQL UNION ALL)
Expand Nested Tables	Table.ExpandTableColumn(source, "Joined", {"Column1", "Column2"})	Flatten results from merge operations, JSON arrays	Yes (with foldable merge)
Combine Files from Folder	Folder.Files("path") then invoke custom function	Process hundreds of CSVs/Excel files automatically	N/A (file system)

Type & Schema

Operation	M Code	Use Case	Folds?
Change Type	Table.TransformColumnTypes(source, {{"Date", type date}, {"Amount", type number}})	Enforce data types for model correctness	Yes (SQL CAST)
Add Custom Column	Table.AddColumn(source, "NewCol", each [Price] * [Qty], type number)	Calculated fields, business logic, derived values	Sometimes (depends on expression)
Rename Columns	Table.RenameColumns(source, {{"OldName", "NewName"}})	Standardize column names across sources	Yes (SQL AS alias)
Reorder Columns	Table.ReorderColumns(source, {"Col1", "Col2", "Col3"})	Organize columns for readability and consistency	Yes (SQL SELECT order)

M Language: The Power Behind Power Query

Every Power Query transformation generates M code. For enterprise use, understanding and writing M directly is essential — GUI-only transformations hit limitations quickly when you need custom functions, API pagination, dynamic logic, or advanced error handling.

M Language Fundamentals

Expression-based: Every query is a single expression using let/in syntax. The let block defines steps, the in clause specifies which step to return as output.
Case-sensitive: Table.AddColumn is valid; table.addcolumn is not. Column references must match exact casing from the source.
Lazy evaluation: Steps only execute when their output is needed. Unused steps in a let block do not consume resources.
Immutable values: Variables assigned in let cannot be reassigned. Each step creates a new value by transforming the previous one.
Rich type system: Primitive types (text, number, date, datetime, logical, null) plus structured types (record, list, table, function, binary).

Custom Functions in M

Custom functions are the most powerful enterprise feature in Power Query. They enable reuse, consistency, and advanced patterns that the GUI cannot achieve.

// Custom function: Standardize text columns
// Usage: invoke on any text column for consistent cleaning
(inputText as text) as text =>
let
    Trimmed = Text.Trim(inputText),
    Cleaned = Text.Clean(Trimmed),
    ProperCase = Text.Proper(Cleaned),
    Result = Text.Replace(ProperCase, "  ", " ")
in
    Result

This function trims whitespace, removes non-printable characters, applies proper casing, and collapses double spaces. Invoke it across any text column for standardized data quality.

API Pagination Pattern

Most REST APIs return paginated results. This M pattern handles pagination automatically, critical for enterprise integrations.

// Recursive pagination function
let
    GetPage = (url as text) as record =>
    let
        Response = Json.Document(Web.Contents(url)),
        Data = Response[results],
        NextUrl = try Response[next] otherwise null,
        Result = [Data = Data, Next = NextUrl]
    in
        Result,

    GetAllPages = (url as text) as list =>
    let
        FirstPage = GetPage(url),
        AllData = List.Generate(
            () => FirstPage,
            each [Next] <> null,
            each GetPage([Next]),
            each [Data]
        ),
        Combined = List.Combine(AllData)
    in
        Combined
in
    GetAllPages

List.Generate recursively fetches pages until the "next" URL is null. EPC Group uses this pattern for CRM, EHR, and SaaS API integrations where datasets span thousands of pages.

Query Folding: The #1 Performance Factor

Query folding is the single most important concept for Power Query performance at scale. When transformations fold, the source database does the heavy lifting. When they do not fold, Power Query loads raw data into memory and processes it locally — which is catastrophically slow for large datasets.

Steps That Fold

Filter rows (translates to SQL WHERE)
Remove columns (translates to SQL SELECT)
Rename columns (translates to SQL AS)
Sort rows (translates to SQL ORDER BY)
Group by / aggregate (translates to SQL GROUP BY)
Merge queries (translates to SQL JOIN)
Change data type (translates to SQL CAST)
Append queries (translates to SQL UNION ALL)
Keep / remove top N rows (translates to SQL TOP)
Remove duplicates (translates to SQL DISTINCT)

Steps That Break Folding

Add custom column with M expressions
Pivot / unpivot columns
Merge with non-foldable query
Table.Buffer (forces full evaluation)
Complex conditional logic (if/then/else)
Text transformations (Proper, Clean, Trim)
Split column operations
Fill down / fill up
Transpose table
Custom M functions on rows

EPC Group Rule: Structure every query so that all foldable operations (filter, select, rename, join, group) execute first, followed by non-foldable operations (custom columns, pivoting, text transformations) at the end. This maximizes the work done by the database and minimizes the data loaded into Power Query memory.

Power Query vs DAX: When to Use Each

One of the most common enterprise mistakes is using DAX for work that belongs in Power Query, or vice versa. Here is the definitive decision framework.

Task	Use Power Query (M)	Use DAX	Why
Data type conversion		—	Power Query handles type casting during load, reducing model processing
Text cleanup (trim, proper case)		—	Cleanse once at load time, not on every query execution
Joining/merging tables		—	Power Query merges fold to SQL JOINs; DAX LOOKUPVALUE is row-by-row
Removing duplicates		—	Deduplication should happen before data enters the model
Date table creation		—	Power Query date tables are more flexible and load faster than CALENDAR()
Running totals / YTD / MTD	—		Time intelligence requires filter context that only DAX provides
Dynamic calculations	—		Measures respond to slicer selections; Power Query is static after refresh
Percentage of total	—		Requires CALCULATE to modify filter context for denominator
Conditional aggregation	—		CALCULATE + FILTER enables context-dependent aggregation
Row-level security logic	—		RLS is defined in DAX and evaluated at query time per user

For a deeper dive into data model architecture, see our Power BI Data Modeling Best Practices Guide.

Power Query Data Sources for Enterprise

Power Query connects to hundreds of data sources, and connector availability varies by host (Microsoft Learn, accessed 2026-10-27). Understanding connector capabilities — especially which support query folding — is critical for architecture decisions.

Relational Databases

SQL Server, Azure SQL, PostgreSQL, Oracle, MySQL, Snowflake, Amazon Redshift, Google BigQuery

Folding: Full query folding support

Best performance. EPC Group default recommendation for enterprise data warehouses.

Cloud Storage & Files

Azure Blob, ADLS Gen2, SharePoint, OneDrive, S3, local files (CSV, Excel, JSON, Parquet)

Folding: No query folding (file-based)

Prefer Parquet over CSV for faster loading. Filter files by folder path and date.

APIs & Web

REST APIs (Web.Contents), OData feeds, Web pages, GraphQL (via custom M)

Folding: OData supports partial folding

Requires custom M for pagination, authentication, and error handling. Rate limiting is critical.

Business Applications

Dataverse, Dynamics 365, Salesforce, SAP HANA, SAP BW, ServiceNow, Jira

Folding: Varies by connector

Use application-specific connectors over generic ODBC for better folding and schema support.

Microsoft Fabric

Lakehouse, Warehouse, KQL Database, Dataflow Gen2, Semantic Model

Folding: Full folding (Lakehouse/Warehouse)

Fabric connectors are optimized for DirectLake mode. EPC Group recommends Fabric as the primary data platform for new deployments.

Custom Connectors

Power Query SDK custom connectors for proprietary systems

Folding: Depends on implementation

EPC Group has built custom connectors for EHR systems (Epic, Cerner), trading platforms, and government databases.

Enterprise Error Handling in Power Query

In production environments, data pipelines fail. APIs return 500 errors, schemas drift, source systems change column names, and type conversions hit unexpected values. Enterprise Power Query pipelines must handle every failure gracefully — never silently dropping data or halting refreshes without notification.

try/otherwise Pattern

The fundamental error handling construct in M. Wrap any expression that can fail and provide a fallback value.

// Safe type conversion with error capture
Table.AddColumn(source, "ParsedDate", each
    try Date.FromText([RawDate])
    otherwise null,
    type nullable date
)

Error Capture Table Pattern

Instead of dropping errors, capture them in a separate table for monitoring and investigation.

// Separate clean rows from error rows
let
    Source = YourDataQuery,
    ErrorRows = Table.SelectRowsWithErrors(Source, {"Amount"}),
    CleanRows = Table.RemoveRowsWithErrors(Source, {"Amount"}),
    ErrorLog = Table.AddColumn(ErrorRows, "ErrorTimestamp",
        each DateTime.LocalNow(), type datetime)
in
    CleanRows  // Load clean data; ErrorLog feeds monitoring dashboard

Schema Drift Detection

Detect when source systems change column names or types before they break downstream queries.

// Validate expected columns exist before transforming
let
    Source = Sql.Database("server", "db"),
    ExpectedColumns = {"CustomerID", "Name", "Email", "Amount"},
    ActualColumns = Table.ColumnNames(Source),
    Missing = List.Difference(ExpectedColumns, ActualColumns),
    Result = if List.Count(Missing) > 0
        then error "Schema drift detected. Missing: "
            & Text.Combine(Missing, ", ")
        else Source
in
    Result

Power Query Best Practices for Enterprise

Maximize Query Folding

Structure transformations so foldable steps (filter, select, rename, join, group) come first. Check folding with query folding indicators and the query plan in Power Query Online, or with View Native Query where the connector offers it (Microsoft Learn, accessed 2026-10-27). Move non-foldable steps to the end of the pipeline.

Filter Early, Filter Often

Apply row filters and column removal as the first steps after connecting to a source. Every row and column you carry through subsequent transformations costs memory and time.

Use Parameters for Everything

Server names, database names, file paths, API endpoints, date ranges, environment flags — all should be parameters. This enables one-click environment switching, automated deployment pipelines, and incremental refresh. Never hardcode connection strings or paths in M code.

Create Staging Queries

Build a staging layer: queries that connect to sources, apply basic cleaning, and are marked "Enable load = false." Then reference these staging queries in downstream transformations. This prevents duplicate source connections, enables reuse, and keeps the dependency tree clean.

Build Custom Function Libraries

Extract repeating logic into custom functions stored in a dedicated "Functions" group. Common examples: date parsing, text standardization, API pagination, file processing. A shared function library standardizes logic across queries and reduces maintenance.

Implement Error Handling on Every Pipeline

Wrap API calls and type conversions in try/otherwise blocks. Create error capture tables that log failed rows with metadata. Set up Power Automate alerts for refresh failures. Silent data quality degradation is worse than a visible error — make failures loud and traceable.

Document with Comments and Step Names

Rename every Applied Step to describe what it does (not "Changed Type" but "Enforce date and currency types"). Add M code comments (// single line or /* block */). Future developers (including yourself in 6 months) will thank you. Enterprise queries without documentation become unmaintainable.

Use Native SQL When Folding Fails

When complex transformations cannot fold through the GUI, use Value.NativeQuery to pass raw SQL directly to the database. This gives you full SQL optimization (CTEs, window functions, temp tables) while keeping the pipeline within Power Query governance. Always parameterize SQL to prevent injection.

Power Query Parameters for Enterprise Deployment

Parameters transform Power Query from a desktop tool into an enterprise deployment pipeline. Instead of hardcoded server names, file paths, and date ranges, parameters centralize configuration — enabling environment promotion (dev to staging to production) without editing a single line of M code.

Environment Switching

Create a "ServerName" parameter with values for Dev, Staging, and Production SQL Servers. All queries reference the parameter instead of hardcoded server names. Promoting from dev to production is a single parameter change.

ServerName = "prod-sql-server.database.windows.net"

Incremental Refresh

Power BI requires RangeStart and RangeEnd datetime parameters for incremental refresh. Power Query filters the date column using these parameters, and Power BI automatically manages the date ranges during scheduled refresh.

RangeStart = #datetime(2026, 1, 1, 0, 0, 0)

Dynamic File Paths

Store folder paths as parameters to switch between local development folders and production Azure Blob Storage or SharePoint paths. Combine with Folder.Files for batch file processing across environments.

DataFolder = "https://storage.blob.core.windows.net/data/"

API Configuration

Centralize API base URLs, version numbers, and non-sensitive configuration as parameters. Combine with Web.Contents for clean, maintainable API integration code. Never store API keys as parameters — use data source credentials instead.

ApiBaseUrl = "https://api.example.com/v2"

Related Resources

Power BI Consulting Services

Enterprise Power BI implementation, optimization, and managed services from EPC Group.

Power BI Data Modeling Best Practices

Star schema design, relationship optimization, and data model architecture for enterprise Power BI.

Power BI Performance Optimization

DAX optimization, incremental refresh, composite models, and Premium capacity tuning.

What is Power Query and how do you use it in Power BI?

Power Query is the data transformation and preparation engine built into Power BI (and Excel). It connects to hundreds of data sources (Microsoft Learn, accessed 2026-10-27), lets you clean, reshape, merge, and enrich data — all before it reaches your data model. You access it via the Power Query Editor in Power BI Desktop (Home > Transform Data). Every transformation you apply is recorded as an M language step, creating a repeatable, auditable pipeline that re-executes on every dataset refresh. EPC Group uses Power Query as the foundation of every enterprise Power BI deployment because it separates data preparation from data modeling, which is critical for maintainability at scale.

What is the M language in Power Query?

M (officially "Power Query Formula Language") is the functional programming language behind every Power Query transformation. When you click buttons in the Power Query Editor, it generates M code behind the scenes. For enterprise use, writing M directly is essential — it enables custom functions, parameterized queries, conditional logic, API pagination, and advanced error handling that the GUI cannot achieve. M is a case-sensitive, expression-based language where each query is a series of let/in steps. EPC Group engineers write custom M code when GUI-only transformations hit their limits in complex environments.

What is query folding in Power Query and why does it matter?

Query folding is when Power Query translates your transformation steps into native source queries (SQL, OData, etc.) that execute on the source database rather than in the Power Query engine. This often makes queries faster than extracting all the data and running every transformation in the Power Query engine (Microsoft Learn, accessed 2026-10-27). Folding support depends on the connector, the data source, and the transformations; file sources such as CSV and Excel don’t fold (Microsoft Learn, accessed 2026-10-27). In Power Query Online, query folding indicators and the query plan show which steps fold, and View Native Query or View data source query shows the request sent to the source where the connector offers it (Microsoft Learn, accessed 2026-10-27). EPC Group designs every Power Query pipeline to maximize query folding, especially for DirectQuery and incremental refresh scenarios where folding is mandatory.

How do Power Query parameters work in enterprise environments?

Power Query parameters are named values that can be referenced across multiple queries and changed without editing M code. Enterprise uses include: 1) Environment switching (dev/staging/production database connections via a single parameter change), 2) Incremental refresh (RangeStart/RangeEnd date parameters are required by Power BI), 3) Dynamic file paths (point to different folders per environment), 4) API keys and endpoints (centralized configuration), 5) Threshold values for business logic. EPC Group deploys parameterized Power Query pipelines for every enterprise client because they enable deployment automation and eliminate manual editing during environment promotion.

What is the difference between Power Query and DAX?

Power Query and DAX serve different purposes in the Power BI stack. Power Query (M language) runs BEFORE data reaches the model — it extracts, transforms, and loads data. DAX runs AFTER data is in the model — it creates calculated measures, columns, and tables for analysis. Think of Power Query as your ETL layer and DAX as your analytics layer. A common anti-pattern is using DAX calculated columns for transformations that should be in Power Query (e.g., text cleanup, date parsing, data type conversion). EPC Group rule: if a transformation can be done in Power Query, it should be — this keeps the data model clean and leverages query folding for performance.

How do you handle errors in Power Query at enterprise scale?

Enterprise error handling in Power Query requires: 1) try/otherwise expressions around every step that can fail (API calls, type conversions, lookups), 2) Error tables that capture failed rows with metadata (source row, error message, timestamp) instead of silently dropping them, 3) Row-level error replacement using Table.ReplaceErrorValues for non-critical fields, 4) Refresh failure alerting via Power Automate or data-driven alerts, 5) Data quality checks as explicit steps (row count validation, null percentage thresholds, schema drift detection). EPC Group builds error monitoring dashboards for every enterprise Power Query pipeline — silent failures are the biggest risk in automated data refresh.

How many data sources does Power Query support?

Power Query supports hundreds of data sources, and connector availability varies by host: Excel, Power BI semantic models, or Power Query Online (Microsoft Learn, accessed 2026-10-27). Examples include: SQL Server, Azure SQL, PostgreSQL, Oracle, MySQL (relational databases), Azure Blob Storage, SharePoint, OneDrive, ADLS Gen2 (file/cloud storage), REST APIs, OData feeds, Web pages (API/web), Dataverse, Dynamics 365, Salesforce (business applications), Excel, CSV, JSON, XML, Parquet (file formats), and Fabric Lakehouse, Warehouse, and Dataflow Gen2 (Microsoft Fabric). You can also build custom connectors using the Power Query SDK for proprietary systems. EPC Group has built custom connectors for healthcare EHR systems, financial trading platforms, and government databases that lack native support.

What are Power Query custom functions and when should you use them?

Power Query custom functions are reusable M code blocks that accept parameters and return results — essentially stored procedures for your ETL layer. Enterprise use cases: 1) Processing multiple files with identical structure (invoke function for each file in a folder), 2) API pagination (function that takes a page number, returns results), 3) Standardized data cleansing (trim, proper case, remove special characters applied consistently), 4) Complex business logic (fiscal year calculation, custom date hierarchies), 5) Reusable merge patterns (standard lookup function used across queries). EPC Group creates reusable function libraries for enterprise clients that standardize transformations across many queries.

How do you optimize Power Query performance for large datasets?

Power Query performance optimization for enterprise datasets: 1) Maximize query folding — every foldable step runs on the source database, not in memory, 2) Filter early — apply WHERE clauses in the first steps, not after joins, 3) Remove unnecessary columns immediately — do not carry extra columns through transformations, 4) Avoid Table.Buffer unless specifically needed for many-to-many merges, 5) Use Table.Partition for parallel processing of large tables, 6) Disable "include in report refresh" for staging queries, 7) Use native SQL queries for complex transformations that cannot fold, 8) Set query evaluation timeout appropriately for long-running source queries. EPC Group optimizes large Power Query pipelines through systematic folding and staging.

Should we use Power Query Dataflows or embedded Power Query in Power BI?

For enterprise environments, EPC Group recommends a tiered approach: Dataflow Gen2 in Microsoft Fabric for shared, governed, reusable data preparation that feeds multiple reports and datasets. Embedded Power Query in Power BI Desktop for report-specific transformations that are not shared. The decision framework: if 2+ reports need the same transformed data, use a Dataflow. If the transformation is unique to one report, embed it. Dataflows provide centralized governance, reuse, incremental refresh at the query level, and separation of ETL from reporting — which is essential for enterprise data mesh architectures. EPC Group deploys Dataflow Gen2 as the standard ETL layer for Fabric-enabled enterprises.

What are advanced data transformations you can do using Power Query?

Advanced data transformations in Power Query include merge, append, group by, pivot, and unpivot, and when the ribbon cannot do what you need, you edit the query’s M script in the Advanced Editor (Microsoft Learn, accessed 2026-10-27). Power Query records each transformation as a query step and applies it when the query runs, without modifying the source data (Microsoft Learn, accessed 2026-10-27). Parameters and custom functions then let you reuse the same logic across queries (Microsoft Learn, accessed 2026-10-27).

What are Power Query transformation best practices?

Power Query transformation best practices are to choose a purpose-built connector, filter data early, do expensive operations such as sorts last, set correct data types, and reuse logic with parameters and custom functions (Microsoft Learn, accessed 2026-10-27). Microsoft also recommends profiling data with the column quality, distribution, and profile tools, naming steps clearly, and splitting large queries into referenced queries (Microsoft Learn, accessed 2026-10-27). EPC Group applies these practices when it reviews and rebuilds Power Query pipelines for clients.

What is the difference between merge and append in Power Query?

Merge joins two tables on matching values in one or more columns, while append stacks the rows of two or more tables into a single table (Microsoft Learn, accessed 2026-10-27). Merge offers left outer, right outer, full outer, inner, left anti, and right anti joins, plus fuzzy matching on text columns (Microsoft Learn, accessed 2026-10-27). Append matches columns by header name rather than position and fills columns missing from a table with null values (Microsoft Learn, accessed 2026-10-27).

When should you unpivot or pivot columns in Power Query?

Unpivot when values are spread across many columns, such as one column per month, because it turns those columns into attribute-value pairs that are easier to filter and analyze (Microsoft Learn, accessed 2026-10-27). Unpivot other columns suits sources that gain new columns, since new columns are unpivoted at refresh, while Unpivot only selected columns leaves them unchanged (Microsoft Learn, accessed 2026-10-27). Pivot does the reverse; with the Don’t aggregate option, duplicate row and column combinations return errors (Microsoft Learn, accessed 2026-10-27).

How do you write M code in the Power Query Advanced Editor?

Open the Advanced Editor from the View tab or the Query group on the Home tab, then write the query as a let expression in which each named step builds on an earlier step and the in clause returns the result (Microsoft Learn, accessed 2026-10-27). M is case-sensitive, and step names that contain spaces are written as quoted identifiers that start with # (Microsoft Learn, accessed 2026-10-27). Changes you make through the editor interface update the M script automatically (Microsoft Learn, accessed 2026-10-27).

How does incremental refresh work with Power Query in Power BI?

Incremental refresh filters a table with two Power Query date/time parameters named RangeStart and RangeEnd (case-sensitive), and the Power BI service then partitions the table and refreshes only the recent period (Microsoft Learn, accessed 2026-10-27). The filter step should fold to the source; otherwise Power Query may retrieve every row and filter locally, which defeats the purpose (Microsoft Learn, accessed 2026-10-27). It is supported for Power BI Pro, Premium, Premium per user, and Embedded models (Microsoft Learn, accessed 2026-10-27).

What is Dataflow Gen2 in Microsoft Fabric and how does it differ from Gen1?

Dataflow Gen2 is the newer Fabric dataflow built on Power Query, adding autosave with background publishing, pipeline integration, and output destinations such as Lakehouse tables, Fabric Warehouse, and Azure SQL databases (Microsoft Learn, accessed 2026-10-27). It requires a Fabric, Fabric trial, or Power BI Premium capacity (Microsoft Learn, accessed 2026-10-27). As of April 2026, new Dataflow Gen2 items are always created with CI/CD and Git integration support, and the classic option is no longer available (Microsoft Learn, accessed 2026-10-27).

Need Enterprise Power Query Architecture?

EPC Group designs and builds enterprise Power Query pipelines that scale — parameterized, error-handled, fully-folded, and documented. From single-source dashboards to multi-source data platforms.

Request a Data Strategy Session (888) 381-9725

Power BI Strategy: 2026 Considerations for Power BI Power Query Enterprise Data Transformation Guide 2026

Direct Lake mode has changed the economics of enterprise Power BI in 2026: instead of importing data into Vertipaq, semantic models now query OneLake-resident Parquet files at near-Import-mode performance without the refresh-window cost. When an organization moves a model from scheduled Import-mode refresh to Direct Lake, we design for sub-second report interactions and verify them against your capacity and model; the Import data refresh is replaced by a metadata-only Direct Lake refresh called framing (Microsoft Learn, accessed 2026-10-28).

Row-level security (RLS) and object-level security (OLS) in Power BI Premium and Fabric F-SKU capacities are the single most-overlooked compliance control in HIPAA, SOC 2, and FINRA-regulated environments. Microsoft documents RLS as roles with DAX filter expressions defined in Power BI Desktop, members assigned in the Power BI service and each role checked with Test as role; RLS applies to workspace Viewers only, service principals cannot be added to an RLS role, and an embedding app that signs in with a service principal applies per-user RLS by passing an effective identity in the embed token (Microsoft Learn, accessed 2026-10-28). EPC Group tests RLS with the real effective identity of each deployment scenario, because Test as role cannot simulate embedded or B2B guest identities.

Decision factors EPC Group evaluates

Row-level security via service principal authentication
Capacity sizing decision (F2/F4/F64+) tied to peak concurrent users and refresh window
Copilot grounding quality assessment of semantic-model metadata
Direct Lake mode adoption for Fabric-resident semantic models
License optimization audit (Pro vs Premium Per User vs F-SKU)

For a tailored read on this topic in your specific tenant, contact EPC Group at contact@epcgroup.net or +1 (888) 381-9725. See pricing and engagement models.