Building Multi-Stage ETL Pipelines in Power Query M: Orchestrating Dependent Transformations with Modular Query Chains

Introduction

You've got five data sources that need to come together before you can produce a single reliable output. Customer records from a CRM export, transaction data from a billing system, a product catalog from SharePoint, exchange rates from a web API, and a territory mapping table someone built in Excel six years ago and nobody wants to touch. Each of these sources needs cleaning. Several need to be joined in a specific sequence. Some transformations depend on values computed in earlier steps. And every time the data refreshes, it all has to run correctly, in the right order, without you babysitting it.

This is the reality of production ETL work, and it's where most Power Query implementations start to fall apart. Analysts who learned Power Query by clicking through the GUI often end up with a single massive query that does everything in one place — or worse, a tangle of queries where nobody is sure what depends on what, or why changing one thing breaks three others. When you're working at the practitioner level, you need a different mental model: queries as composable pipeline stages, each with a single responsibility, assembled into a deliberate dependency chain.

By the end of this lesson, you'll be able to architect multi-stage ETL pipelines in Power Query M where each stage is a modular, testable unit. You'll understand how M's lazy evaluation model affects pipeline performance, how to pass parameters between stages, how to build reusable transformation functions, and how to manage the entire chain in a way that makes maintenance and debugging tractable.

What you'll learn:

• How to design a query dependency graph and why execution order matters in M
• How to build modular query stages using named queries and M functions as reusable components
• How to pass context and parameters between pipeline stages without creating brittle dependencies
• How to use staging queries to isolate raw data from transformations, and why that separation pays off
• How to debug a multi-stage pipeline systematically when something breaks mid-chain

---

Prerequisites

You should be comfortable writing M expressions by hand in the Advanced Editor, understand the let...in structure, and have worked with table operations like Table.SelectRows, Table.ExpandTableColumn, and Table.Join. You don't need to be an expert in M's type system, but knowing what a record, list, and table are in M will make this much smoother.

---

The Mental Model: Queries Are Nodes in a Graph

Before writing a single line of code, let's fix a framing problem. In the GUI workflow, it's tempting to think of queries as independent sheets that happen to sometimes reference each other. That's backwards for serious pipeline work.

Think of your query collection as a directed acyclic graph (DAG). Each query is a node. Each reference from one query to another is a directed edge. Data flows through those edges. When Power Query evaluates your final output query, it walks that graph, evaluates dependencies first, and composes the results.

This has a concrete implication: M evaluates lazily. Power Query doesn't execute every query and hand off a result table to the next query the way a script would. Instead, M composes expressions. When QueryB references QueryA, Power Query builds a combined expression that represents both operations. The engine then decides how to evaluate that expression — and it may push work down to the source (query folding) or evaluate it in the M engine depending on whether folding is possible.

Why does this matter for pipeline design? Because it means:

1. You don't incur the cost of materializing intermediate results unless you force it.
2. Query folding can propagate across query boundaries — if QueryB references QueryA and both are against a SQL source, folding may span both stages.
3. Circular references are impossible and will cause an error immediately. Design your DAG accordingly.

The practical advice: draw your dependency graph on paper (or a whiteboard) before you start building. Every box is a query. Every arrow is a reference. Arrows should only point in one direction, from sources toward your final output.

---

Stage 1: Isolating Raw Sources with Staging Queries

The first principle of a well-built pipeline is that raw data and transformed data should never live in the same query. Your staging queries are pure extraction — they connect to the source, pull the data with minimal manipulation, and do nothing else.

Here's what a staging query looks like for a CSV export from a billing system:

// Query name: Raw_Transactions
let
    Source = Csv.Document(
        File.Contents("C:\DataFeeds\billing_export_2024.csv"),
        [Delimiter = ",", Columns = 9, Encoding = 1252, QuoteStyle = QuoteStyle.None]
    ),
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    SetTypes = Table.TransformColumnTypes(
        PromoteHeaders,
        {
            {"transaction_id", type text},
            {"customer_id", type text},
            {"product_sku", type text},
            {"transaction_date", type date},
            {"quantity", Int64.Type},
            {"unit_price", type number},
            {"currency_code", type text},
            {"region_code", type text},
            {"status", type text}
        }
    )
in
    SetTypes

Notice what this query does not do: it doesn't filter out cancelled transactions, it doesn't join to any other table, it doesn't compute derived columns. It connects, promotes headers, and sets types. That's it.

Why the strict discipline? Three reasons. First, when your pipeline breaks, you can open Raw_Transactions and immediately verify whether the problem is in the source data or in a downstream transformation — you haven't confused the two. Second, if multiple downstream queries need the raw data (perhaps one for sales analysis, one for returns processing), they can both reference the same staging query without duplicating the connection logic. Third, if the source file changes location or format, you have exactly one place to update.

Do the same for every source:

// Query name: Raw_Customers
let
    Source = Salesforce.Tables(
        "https://yourorg.salesforce.com",
        [ApiVersion = "51.0"]
    ),
    AccountsTable = Source{[Name = "Account"]}[Data],
    SelectColumns = Table.SelectColumns(
        AccountsTable,
        {"Id", "Name", "BillingCountry", "Industry", "AnnualRevenue", "OwnerId"}
    ),
    RenameColumns = Table.RenameColumns(
        SelectColumns,
        {{"Id", "customer_id"}, {"Name", "customer_name"}, {"BillingCountry", "country"}}
    )
in
    RenameColumns

// Query name: Raw_ExchangeRates
let
    Source = Web.Contents(
        "https://api.exchangerate-api.com/v4/latest/USD"
    ),
    ParseJson = Json.Document(Source),
    RatesRecord = ParseJson[rates],
    RatesTable = Record.ToTable(RatesRecord),
    RenameColumns = Table.RenameColumns(
        RatesTable,
        {{"Name", "currency_code"}, {"Value", "usd_rate"}}
    ),
    SetTypes = Table.TransformColumnTypes(
        RenameColumns,
        {{"currency_code", type text}, {"usd_rate", type number}}
    )
in
    SetTypes

Tip: Name your staging queries with a consistent prefix like Raw_ or Src_. This makes the query list self-documenting. Anyone opening the workbook can immediately see what's a source, what's a transformation stage, and what's a final output.

---

Stage 2: Building Reusable Transformation Functions

Once you have clean staging queries, resist the urge to start writing your transformation logic inline. For any transformation you'll apply more than once — or any transformation complex enough to deserve a name — extract it into a function.

In M, functions are first-class values. A query can be a function rather than a table, and other queries can call that function. This is the mechanism for genuine reusability in a Power Query pipeline.

Here's a practical example. Across multiple data sources, you need to standardize country names from various ad-hoc strings into ISO country codes. Instead of writing that logic three times, build it once:

// Query name: fn_NormalizeCountry
// Parameters: country_raw (type text) -> returns text
(country_raw as text) as text =>
let
    Uppercased = Text.Upper(Text.Trim(country_raw)),
    Normalized = [
        #"UNITED STATES" = "US",
        #"USA" = "US",
        #"U.S.A." = "US",
        #"UNITED KINGDOM" = "GB",
        #"UK" = "GB",
        #"GREAT BRITAIN" = "GB",
        #"GERMANY" = "DE",
        #"DEUTSCHLAND" = "DE",
        #"FRANCE" = "FR",
        #"CANADA" = "CA",
        #"AUSTRALIA" = "AU"
    ],
    Result = if Record.HasFields(Normalized, Uppercased)
             then Record.Field(Normalized, Uppercased)
             else Text.Upper(Text.Start(country_raw, 2))
in
    Result

Notice the technique here: we're using a record as a lookup dictionary. Record.HasFields checks if the uppercased input exists as a field name in the record, and Record.Field retrieves the value. The fallback — taking the first two characters — is a reasonable default for inputs you haven't explicitly mapped yet. In production, you'd extend this record or replace the fallback with an error or a null.

Now you can call this function anywhere in the pipeline:

// Inside a transformation query
NormalizeCustomerCountry = Table.TransformColumns(
    PreviousStep,
    {{"country", fn_NormalizeCountry, type text}}
)

Let's build another function — this one for a more complex operation: computing weighted average unit prices per product per region from the transactions table.

// Query name: fn_WeightedAvgPrice
// Parameters: transactions (type table), group_columns (type list) -> returns table
(transactions as table, group_columns as list) as table =>
let
    GroupedData = Table.Group(
        transactions,
        group_columns,
        {
            {"total_revenue", each List.Sum([unit_price] * [quantity]), type number},
            {"total_quantity", each List.Sum([quantity]), Int64.Type},
            {"transaction_count", each Table.RowCount(_), Int64.Type}
        }
    ),
    AddWeightedAvg = Table.AddColumn(
        GroupedData,
        "weighted_avg_price",
        each if [total_quantity] = 0 then null
             else [total_revenue] / [total_quantity],
        type number
    )
in
    AddWeightedAvg

Warning: When you multiply two columns in M like [unit_price] * [quantity], this doesn't work directly in a Table.Group aggregation context the way you might expect. The expression each List.Sum([unit_price] * [quantity]) works because inside Table.Group, column references like [unit_price] return lists, and multiplying two lists element-wise is valid M. If you're ever unsure, test the expression in isolation on a small table before embedding it in a function.

---

Stage 3: The Transformation Layer — Cleaning and Enriching

With your staging queries and helper functions in place, you can now build the transformation layer. Each transformation query has one job: take the raw data from a staging query (or another transformation query), apply a specific set of transformations, and produce a cleaner or richer table.

Here's the transaction cleaning stage:

// Query name: Trans_CleanTransactions
let
    Source = Raw_Transactions,

    // Remove cancelled and test transactions
    RemoveInvalidStatus = Table.SelectRows(
        Source,
        each [status] = "completed" or [status] = "refunded"
    ),

    // Remove transactions with null or zero quantity
    RemoveNullQuantity = Table.SelectRows(
        RemoveInvalidStatus,
        each [quantity] <> null and [quantity] > 0
    ),

    // Remove transactions with negative prices (data entry errors)
    RemoveNegativePrices = Table.SelectRows(
        RemoveNullQuantity,
        each [unit_price] >= 0
    ),

    // Normalize region codes to uppercase
    NormalizeRegion = Table.TransformColumns(
        RemoveNegativePrices,
        {{"region_code", Text.Upper, type text}}
    ),

    // Add a computed revenue column in source currency
    AddSourceRevenue = Table.AddColumn(
        NormalizeRegion,
        "source_revenue",
        each [quantity] * [unit_price],
        type number
    ),

    // Add a record-level flag for refunds
    AddRefundFlag = Table.AddColumn(
        AddSourceRevenue,
        "is_refund",
        each [status] = "refunded",
        type logical
    )
in
    AddRefundFlag

And the customer enrichment stage:

// Query name: Trans_EnrichedCustomers
let
    Source = Raw_Customers,

    // Apply country normalization function
    NormalizeCountries = Table.TransformColumns(
        Source,
        {{"country", fn_NormalizeCountry, type text}}
    ),

    // Segment customers by annual revenue
    AddRevenueSegment = Table.AddColumn(
        NormalizeCountries,
        "revenue_segment",
        each if [AnnualRevenue] = null then "Unknown"
             else if [AnnualRevenue] >= 1000000000 then "Enterprise"
             else if [AnnualRevenue] >= 100000000 then "Large"
             else if [AnnualRevenue] >= 10000000 then "Mid-Market"
             else "SMB",
        type text
    ),

    // Trim and standardize the industry field
    StandardizeIndustry = Table.TransformColumns(
        AddRevenueSegment,
        {{"Industry", each if _ = null then "Unclassified" else Text.Trim(_), type text}}
    )
in
    StandardizeIndustry

Notice that Trans_EnrichedCustomers references both Raw_Customers and fn_NormalizeCountry. This is the dependency graph taking shape. You now have two transformation queries that each depend on exactly one staging query and potentially one or more helper functions. The dependency chain is clean and traceable.

---

Stage 4: The Join Layer — Composing the Pipeline

The join layer is where your independent transformation streams come together. This is also where practitioners most often create performance problems, so the sequencing of joins matters.

The general principle: reduce your table sizes as aggressively as possible before joining. Filter rows, select only the columns you need, and remove duplicates in your transformation layers before bringing tables together. A join between a 10-million-row table and a 500,000-row table is avoidable if your actual use case only needs the last 90 days of transactions.

Here's the currency conversion join — connecting cleaned transactions to exchange rates:

// Query name: Trans_TransactionsUSD
let
    Source = Trans_CleanTransactions,

    // Join exchange rates on currency code
    JoinRates = Table.NestedJoin(
        Source,
        {"currency_code"},
        Raw_ExchangeRates,
        {"currency_code"},
        "ExchangeData",
        JoinKind.LeftOuter
    ),

    // Expand only the rate column
    ExpandRate = Table.ExpandTableColumn(
        JoinRates,
        "ExchangeData",
        {"usd_rate"},
        {"usd_rate"}
    ),

    // Handle transactions where exchange rate is missing
    HandleMissingRates = Table.TransformColumns(
        ExpandRate,
        {{"usd_rate", each if _ = null then 1.0 else _, type number}}
    ),

    // Compute USD revenue
    AddUSDRevenue = Table.AddColumn(
        HandleMissingRates,
        "revenue_usd",
        each [source_revenue] / [usd_rate],
        type number
    ),

    // Drop the now-redundant columns
    CleanupColumns = Table.RemoveColumns(
        AddUSDRevenue,
        {"source_revenue", "usd_rate", "currency_code"}
    )
in
    CleanupColumns

Tip: Always use JoinKind.LeftOuter by default rather than JoinKind.Inner unless you have a strong reason to drop unmatched rows. An inner join that silently drops rows because a lookup table is incomplete is one of the nastiest bugs to diagnose in a production pipeline — especially when the lookup table is an Excel file someone edits by hand.

Now the final assembly join, bringing currency-converted transactions together with enriched customers:

// Query name: Trans_TransactionsWithCustomers
let
    Source = Trans_TransactionsUSD,

    JoinCustomers = Table.NestedJoin(
        Source,
        {"customer_id"},
        Trans_EnrichedCustomers,
        {"customer_id"},
        "CustomerData",
        JoinKind.LeftOuter
    ),

    ExpandCustomerFields = Table.ExpandTableColumn(
        JoinCustomers,
        "CustomerData",
        {"customer_name", "country", "Industry", "revenue_segment"},
        {"customer_name", "country", "industry", "revenue_segment"}
    )
in
    ExpandCustomerFields

At this point, your dependency graph looks like this:

Raw_Transactions ──► Trans_CleanTransactions ──► Trans_TransactionsUSD ──► Trans_TransactionsWithCustomers
                                                          ▲                           ▲
Raw_ExchangeRates ────────────────────────────────────────┘                           │
                                                                                      │
Raw_Customers ──► Trans_EnrichedCustomers ─────────────────────────────────────────────┘
fn_NormalizeCountry ──────────────────────────────────────────────────────────────────┘

Every edge is explicit and unidirectional. There are no cycles. Each stage has a single, well-defined responsibility.

---

Stage 5: The Output Layer — Final Aggregations and Presentation

The output layer sits at the top of the DAG. These queries are what actually get loaded into your data model, and they should contain only the final shaping — aggregations, column reordering, final type enforcement. No business logic belongs here.

// Query name: Output_SalesSummary
let
    Source = Trans_TransactionsWithCustomers,

    // Exclude refunds from sales summary
    ExcludeRefunds = Table.SelectRows(
        Source,
        each [is_refund] = false
    ),

    // Apply the weighted average pricing function by product and region
    WeightedPricing = fn_WeightedAvgPrice(
        ExcludeRefunds,
        {"product_sku", "region_code"}
    ),

    // Add a simple date key for the model
    AddDateKey = Table.AddColumn(
        WeightedPricing,
        "date_key",
        each Date.Year([transaction_date]) * 10000
            + Date.Month([transaction_date]) * 100
            + Date.Day([transaction_date]),
        Int64.Type
    ),

    // Final column selection and ordering
    FinalShape = Table.SelectColumns(
        AddDateKey,
        {
            "product_sku",
            "region_code",
            "customer_name",
            "country",
            "revenue_segment",
            "industry",
            "total_revenue",
            "total_quantity",
            "weighted_avg_price",
            "transaction_count",
            "date_key"
        }
    )
in
    FinalShape

---

Managing Parameters Across the Pipeline

In production, your pipeline shouldn't have hardcoded file paths, date cutoffs, or environment-specific URLs buried inside individual queries. These should be M parameters — or better, a centralized parameters query.

Create a query named Config that returns a record:

// Query name: Config
let
    Parameters = [
        DataFolder = "C:\DataFeeds\",
        CutoffDate = #date(2024, 1, 1),
        BaseCurrency = "USD",
        ExchangeRateAPIUrl = "https://api.exchangerate-api.com/v4/latest/USD",
        Environment = "Production"
    ]
in
    Parameters

Now your staging queries reference Config for any environment-specific values:

// In Raw_Transactions, replace the hardcoded path:
Source = Csv.Document(
    File.Contents(Config[DataFolder] & "billing_export_2024.csv"),
    [Delimiter = ",", Encoding = 1252]
),

And your transformation queries reference Config for business logic parameters:

// In Trans_CleanTransactions, add a date filter:
FilterByDate = Table.SelectRows(
    RemoveNegativePrices,
    each [transaction_date] >= Config[CutoffDate]
),

Warning: Using a Config query as a record works well, but be aware that every query that references Config adds Config as a dependency. In most cases this is negligible, but if Config itself makes a network call or reads a file, every dependent query will trigger that read. Keep Config purely as a record literal unless you have a compelling reason to do otherwise.

---

Hands-On Exercise: Build a Three-Source Pipeline

Let's put this together in a real exercise. Your task is to build a pipeline from scratch using the following scenario:

Scenario: A retail analytics team needs a weekly report that shows net revenue by product category and country, with transactions converted to USD and customer records enriched with tier classifications.

Data sources:

• orders.csv — order-level data with columns: order_id, customer_id, sku, quantity, unit_price, order_date, currency, status
• customers.csv — customer records with columns: customer_id, company_name, country, tier (Gold/Silver/Bronze/Unknown)
• products.csv — product catalog with columns: sku, product_name, category, cost_usd

Steps:

Step 1 — Create your Config query:

// Config
let
    Parameters = [
        DataPath = "C:\RetailData\",
        ReportStartDate = #date(2024, 1, 1),
        BaseCurrency = "USD"
    ]
in
    Parameters

Step 2 — Create three staging queries (Raw_Orders, Raw_Customers, Raw_Products) that connect to each CSV, promote headers, and set types. Do not filter or transform anything else.

Step 3 — Create fn_TierScore — a function that takes a tier text value and returns a numeric priority score (Gold = 3, Silver = 2, Bronze = 1, Unknown = 0). This will be used in downstream analysis.

// fn_TierScore
(tier as text) as number =>
    if tier = "Gold" then 3
    else if tier = "Silver" then 2
    else if tier = "Bronze" then 1
    else 0

Step 4 — Create Trans_CleanOrders that filters to completed orders only, adds a revenue_local column (quantity * unit_price), and filters to dates on or after Config[ReportStartDate].

Step 5 — Create Trans_EnrichedCustomers that adds a tier_score column using fn_TierScore applied to the tier column.

Step 6 — Create Trans_OrdersWithContext that joins Trans_CleanOrders to Trans_EnrichedCustomers on customer_id, then joins to Raw_Products on sku, expanding category and product_name from products and country and tier_score from customers.

Step 7 — Create Output_CategoryCountrySummary that groups by category and country, summing revenue_local and quantity, and computing average tier_score per group.

When complete, you should have nine queries: one Config, three Raw, one function, two Trans, and one Output. Your dependency graph should be fully traceable from output back to sources with no query referenced more than once in the critical path.

---

Common Mistakes & Troubleshooting

Mistake 1: Transforming data in staging queries

Staging queries should be inert. The moment you add a Table.SelectRows to filter out "bad" rows in a staging query, you've coupled your business logic to your data extraction layer. If the definition of "bad" changes, you have to find and edit the staging query rather than the transformation query where that logic belongs.

Mistake 2: Creating circular reference chains

This happens when QueryA references QueryB, and QueryB also references QueryA — either directly or through an intermediate query. Power Query will throw a Formula.Firewall error or a circular reference error. Always draw your DAG before building and verify it has no cycles.

Mistake 3: Expanding too many columns from nested tables

After a Table.NestedJoin, expanding the nested column pulls all specified columns into the parent table. Expanding ten columns when you only need two means you're carrying unnecessary data through the rest of the pipeline. Only expand exactly the columns you'll use in downstream steps.

// Bad: expands everything
Table.ExpandTableColumn(Joined, "CustomerData", Table.ColumnNames(Trans_EnrichedCustomers))

// Good: expands only what's needed
Table.ExpandTableColumn(Joined, "CustomerData", {"customer_name", "country"}, {"customer_name", "country"})

Mistake 4: Forgetting that function queries are not loaded into the data model

If your query list shows a function query with a table icon rather than the function icon (the fx symbol), Power Query may be treating it as a regular table instead of a function. This happens when your function query doesn't start with a function expression at the in statement. Make sure your function query's final value is a function, not a table:

// Correct: function query returns a function
in
    (country_raw as text) as text => ...

Mistake 5: Hardcoding source paths inside nested joins

Don't reference your raw queries inside Table.NestedJoin using a raw Csv.Document() call inline. Always reference the staging query name. This ensures query folding is considered across the full path and keeps your dependency graph explicit.

Debugging a broken pipeline:

When a multi-stage pipeline throws an error, the error message usually points to the output query — which tells you almost nothing about where the problem actually lives. Systematic approach:

1. Open the query that threw the error and look at each step in the Applied Steps pane.
2. Click on the step immediately before the error step. If the preview looks correct, the problem is in that error step. If the preview is already wrong, work backwards.
3. If the error says "Expression.Error: The column 'X' of the table wasn't found," the column was likely dropped or renamed in an upstream transformation query. Open the upstream query and verify the column still exists with the expected name.
4. If the error says "DataFormat.Error" or "type mismatch," the problem is usually a type enforcement step receiving unexpected values. Open the staging query and check whether the raw data has changed shape.
5. Use a temporary diagnostic query: create a new blank query, reference the stage you suspect, and add Table.Profile() to get quick statistics on nulls, distinct values, and type mismatches.

// Diagnostic query pattern
let
    Source = Trans_CleanTransactions,
    Profile = Table.Profile(Source)
in
    Profile

---

Performance Implications and When This Approach Pays Off

Multi-stage pipeline architecture adds overhead in terms of query count and M parsing complexity. For a 10,000-row Excel file, the architecture described here is overkill — the GUI approach works fine. This approach pays off when:

• You have multiple output queries that share upstream transformations. The modular structure prevents duplicating logic, and M's lazy evaluation means shared upstream queries are composed, not recomputed separately for each downstream reference.

• You're working against a folding-capable source (SQL Server, Snowflake, PostgreSQL via connectors). By keeping transformation steps compositionally clean and avoiding operations that break folding (like custom function invocations on columns), you maximize the chance that Power Query pushes the work to the database.

• Your pipeline will be maintained by more than one person. The naming convention (Raw_, Trans_, Output_, fn_) and the single-responsibility structure make onboarding a new analyst dramatically faster.

• Your source data changes shape or volume regularly. When your billing export adds a new column or the CRM changes a field name, having staging queries as the only point of contact with raw data means you have exactly one place to absorb that change.

Tip: If query folding is important in your environment, avoid wrapping function calls around columns inside Table.TransformColumns at transformation stages — custom M functions break folding. Instead, translate values using Table.Join against a static mapping table, which folds much better against SQL-based sources.

---

Summary & Next Steps

You've built a complete multi-stage ETL pipeline with a clear separation between extraction, transformation, enrichment, join assembly, and output layers. The key architectural principles you've applied:

• Staging queries isolate raw data from business logic, creating a single point of contact with each source.
• Transformation queries have single responsibilities and reference exactly the upstream queries they need — nothing more.
• Helper functions encapsulate reusable logic and prevent duplication across pipeline branches.
• A Config query centralizes environment-specific parameters so the pipeline is portable across development and production.
• The dependency graph is explicit, unidirectional, and designed before any code is written.

This architecture scales. When a new data source gets added, you add a staging query and a transformation query, then connect them to the join layer. When a business rule changes, you find the transformation query that owns that rule and change it in one place. When something breaks, the structure tells you exactly where to look.

Where to go next:

• Error handling in M — learn to use try...otherwise and Value.ReplaceErrorAt to make individual steps fault-tolerant without crashing the whole pipeline.
• Dynamic schema handling — when source column names change, use Table.Schema and programmatic column selection to make your pipelines resilient to shape changes.
• Incremental refresh patterns — once your pipeline is modular, adding Power BI incremental refresh to the output queries becomes much more tractable because your transformation stages are clearly bounded.
• M query folding verification — use the Query Diagnostics feature in Power Query to audit which steps fold and which steps run in the M engine, then optimize accordingly.