Introduction
You've built Power Query pipelines that work. Data comes in, transformations happen, and a clean table lands in Excel or Power BI. But as your data estate grows — more sources, more consumers, more business rules — that single-query approach starts to crack. You add a second report that needs slightly different aggregations. Someone asks for the raw data. A new source appears with different date formats. Suddenly you're copy-pasting queries, duplicating transformation logic, and spending more time maintaining pipelines than building new ones. Sound familiar?
This is the problem that multi-stage staging architectures solve. Borrowed directly from enterprise data warehouse design — where raw, cleansed, and conformed layers are first-class citizens — this approach brings the same discipline to Power Query. By intentionally separating what you received from what you cleaned from what you agreed it means, you create a pipeline that scales with your needs instead of collapsing under them. Each layer has a clear contract, a defined purpose, and an audience. When something breaks, you know exactly which layer to inspect. When requirements change, you modify the right layer without touching the others.
By the end of this lesson, you will understand not just the mechanics of building these layers in Power Query, but the reasoning behind each architectural decision. You'll be able to design a pipeline that a colleague can maintain six months from now, that can absorb new data sources without redesign, and that gives your downstream report authors a stable, trustworthy foundation.
What you'll learn:
- The architectural principles behind Raw, Cleansed, and Conformed staging layers and why each one exists
- How to implement each layer as distinct, purposeful query groups in Power Query with proper naming conventions and documentation
- How to use query references (not duplicates) to build a dependency chain that enforces data flow direction
- Techniques for parameterizing transformation logic so business rules live in one place
- How to manage performance implications of multi-layer architectures and when to use staging tables to break query folding chains
Prerequisites
This lesson assumes you're comfortable with intermediate-to-advanced Power Query concepts. Specifically, you should be able to:
- Write and read M code fluently — not just click through the GUI
- Use custom functions and parameters in Power Query
- Understand query folding and why it matters for performance
- Have worked with multiple data sources in a single Power BI report or Excel workbook
If query folding is still a fuzzy concept for you, revisit that topic before proceeding — the performance section of this lesson depends on it.
Why Single-Layer Pipelines Fail at Scale
Before building the solution, let's be precise about the problem. When most people start in Power Query, they write what you might call "destination-first" queries. They look at the final table they need — say, a sales fact table with clean dates, merged customer names, and normalized product categories — and they write one query that takes raw data from the source and delivers that final shape. This works beautifully until:
New consumers appear with different needs. Your finance team needs the same sales data but with cost center allocations. Your logistics team needs it with shipping region codes. Now you duplicate the query and start maintaining two versions of the same transformation logic. When the source schema changes, you have to update both. You will forget to update both. It will cause a problem at 4pm on a Friday.
Debugging becomes archaeology. When a number is wrong in a report, you open the query and stare at 40 transformation steps. Where did that filter go? Why was this column renamed? Which step introduced the NULL that's skewing the aggregation? Without a staging boundary, every step in the pipeline is equally suspect.
Reprocessing is all-or-nothing. If you need to recheck what data actually arrived from the source — maybe a vendor sent the wrong file — you have no way to see the raw payload. It's been overwritten by transformation.
The multi-layer architecture addresses all three of these failure modes systematically.
The Conceptual Framework: Three Layers with Three Jobs
Think of your pipeline as having three distinct zones, each with a clear mandate.
The Raw Layer
The raw layer has exactly one job: preserve what arrived. No transformations. No type conversions. No filters. If the source sends a date as the string "2024-13-45", your raw layer records "2024-13-45". If a column has 80% NULL values, your raw layer faithfully preserves that horror. This layer is your audit trail, your debugging surface, and your recovery point.
In enterprise data warehouses, the raw layer is sometimes called the "landing zone" or "bronze layer" (in Medallion Architecture terminology). The key principle is immutability: what came in is what's stored, unchanged.
In Power Query specifically, this means your raw query should contain nothing but source connection steps and the absolute minimum structural acknowledgment needed to read the data — column name assignment if the source lacks headers, for instance, but nothing else.
The Cleansed Layer
The cleansed layer's job is technical correctness. It answers the question: "Does this data conform to the structural and type expectations of our system?" This is where you:
- Cast columns to their correct data types
- Trim whitespace, normalize casing
- Handle encoding issues
- Split or combine structural fields (separating a full name into first/last only if that's a structural requirement, not a business rule)
- Remove true duplicates (exact row duplicates, not business-logic duplicates)
- Standardize NULL representations (converting empty strings, "N/A", "NULL" text to actual null values)
Notice what's not here: business logic. "Orders below $10 are considered samples and should be excluded from revenue" is not a cleansing rule. That's a business rule, and it belongs in the conformed layer. The cleansed layer doesn't know or care about business meaning. It only cares about technical validity.
The conformed layer applies business logic and semantic meaning. This is where your organization's rules live:
- Applying business filters ("only include orders with status = 'Completed' or 'Shipped'")
- Mapping raw codes to business labels ("region code 'EMEA-01' means 'Western Europe'")
- Merging reference data to enrich facts
- Applying business-defined date logic
- Creating calculated measures or categorizations
- Joining multiple cleansed sources into a single analytical model
The conformed layer is what your report authors see. It represents the agreed, governed definition of your data — the version that's been through the business's own rules and judgments.
Setting Up Your Query Architecture
Let's build a concrete example. We'll work with a sales pipeline: orders come from an ERP system via CSV export, and we need to produce a conformed sales fact table for a Power BI report.
Step 1: Organize with Query Groups
In Power Query (both Excel and Power BI), you can create query groups by right-clicking in the Queries pane and selecting "New Group." Create four groups:
- Parameters — connection strings, file paths, environment toggles
- Raw — source connections, zero transformations
- Cleansed — type-correct, technically valid data
- Conformed — business-ready analytical tables
Prefix your query names to make the layer immediately obvious to anyone opening the file:
raw_orders, raw_customers, raw_productscln_orders, cln_customers, cln_productscfm_sales_fact, cfm_product_dim
This naming convention is not optional decoration. When you have 40 queries in a complex workbook, it's the difference between understanding the architecture in 30 seconds and spending 20 minutes tracing dependencies.
Step 2: Build the Raw Query
Here's what a raw query looks like for our orders CSV:
let
// LAYER: Raw
// PURPOSE: Preserve the exact content of the orders CSV export as received.
// LAST UPDATED: 2024-11-01
// DO NOT add transformations to this query.
Source = Csv.Document(
File.Contents(orders_file_path),
[Delimiter = ",", Columns = 14, Encoding = 65001, QuoteStyle = QuoteStyle.None]
),
PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true])
in
PromoteHeaders
Two things to notice:
First, orders_file_path is a Parameter (defined in the Parameters group). This keeps your connection logic in one place. If the file moves, you update one parameter, not every raw query.
Second, the comment block at the top is deliberate. M doesn't have a built-in documentation system, so comments are your documentation. Write them as if you're leaving a note for a colleague who has no context — because in six months, that colleague will be you.
The raw query has exactly two steps: Source and PromoteHeaders. The header promotion is the bare minimum structural acknowledgment — without it, you can't reliably reference columns by name in downstream queries. Everything else is off-limits here.
Step 3: Build the Cleansed Query
The cleansed query references the raw query — it does not duplicate it. This is a critical distinction. When you use Reference (right-click a query and choose "Reference"), Power Query creates a new query whose first step points to the output of the source query. Any changes to the raw query automatically propagate forward. If you use Duplicate instead, you get a copy that diverges independently — exactly what you're trying to avoid.
let
// LAYER: Cleansed
// SOURCE: raw_orders
// PURPOSE: Apply type corrections, NULL normalization, and structural cleanup.
// BUSINESS LOGIC: None. Business rules belong in cfm_ queries.
Source = raw_orders,
// Cast columns to correct data types
TypedColumns = Table.TransformColumnTypes(
Source,
{
{"order_id", Int64.Type},
{"customer_id", Int64.Type},
{"order_date", type date},
{"ship_date", type date},
{"product_id", Int64.Type},
{"quantity", Int64.Type},
{"unit_price", Currency.Type},
{"discount_pct", Percentage.Type},
{"order_status", type text},
{"sales_rep_id", Int64.Type},
{"region_code", type text},
{"currency_code", type text},
{"shipping_method", type text},
{"notes", type text}
}
),
// Normalize NULL representations: empty strings and sentinel values to null
NullNormalized = Table.TransformColumns(
TypedColumns,
{
{"notes", each if _ = "" or _ = "N/A" or _ = "NULL" then null else _},
{"region_code", each if _ = "" or _ = "UNKNOWN" then null else _},
{"shipping_method", each if _ = "" then null else _}
}
),
// Trim whitespace from text columns
TextTrimmed = Table.TransformColumns(
NullNormalized,
{
{"order_status", Text.Trim},
{"region_code", each if _ <> null then Text.Trim(_) else null},
{"currency_code", Text.Trim},
{"shipping_method", each if _ <> null then Text.Trim(_) else null}
}
),
// Normalize casing for categorical text fields
CasingNormalized = Table.TransformColumns(
TextTrimmed,
{
{"order_status", Text.Upper},
{"currency_code", Text.Upper},
{"region_code", each if _ <> null then Text.Upper(_) else null}
}
),
// Remove true exact-row duplicates (same order_id should never appear twice;
// flag and remove only if ALL columns are identical — data entry duplication)
DuplicatesRemoved = Table.Distinct(CasingNormalized, {"order_id"})
in
DuplicatesRemoved
Notice the comment at the top explicitly states: "BUSINESS LOGIC: None." This is a forcing function for yourself and your team. When someone asks you to add a filter for only US orders in the cleansed layer, you have a written principle to point to: that filter is business logic, and it goes in the conformed layer.
Warning: The step DuplicatesRemoved uses Table.Distinct on order_id specifically — not on all columns. This is a structural decision: order IDs should be unique identifiers by definition. If you find duplicates here, that's a data quality issue that warrants investigation, not silent removal. Consider logging these duplicates to a separate diagnostic query (we'll cover this in the advanced patterns section).
The conformed query references the cleansed query and applies business logic. It may also merge multiple cleansed sources.
let
// LAYER: Conformed
// SOURCES: cln_orders, cln_customers, cln_products
// PURPOSE: Business-ready sales fact table.
// BUSINESS RULES APPLIED:
// - Only COMPLETED and SHIPPED orders included
// - Discount > 50% flagged as exceptional; discount > 100% excluded as data error
// - Extended price calculated as quantity * unit_price * (1 - discount_pct)
// - Orders with null region_code assigned to "UNALLOCATED" for reporting
Source = cln_orders,
// Business rule: Only include finalized orders
ActiveOrdersOnly = Table.SelectRows(
Source,
each [order_status] = "COMPLETED" or [order_status] = "SHIPPED"
),
// Business rule: Exclude physically impossible discounts (data errors)
ValidDiscounts = Table.SelectRows(
ActiveOrdersOnly,
each [discount_pct] <= 1.0 or [discount_pct] = null
),
// Business rule: Flag exceptional discounts for finance review
DiscountFlagged = Table.AddColumn(
ValidDiscounts,
"is_exceptional_discount",
each [discount_pct] >= 0.5,
type logical
),
// Business calculation: Extended price
ExtendedPrice = Table.AddColumn(
DiscountFlagged,
"extended_price",
each [quantity] * [unit_price] * (1 - (if [discount_pct] = null then 0 else [discount_pct])),
Currency.Type
),
// Business rule: Assign unallocated region code
RegionDefaulted = Table.TransformColumns(
ExtendedPrice,
{{"region_code", each if _ = null then "UNALLOCATED" else _}}
),
// Enrich with customer dimension
CustomerJoined = Table.NestedJoin(
RegionDefaulted,
{"customer_id"},
cln_customers,
{"customer_id"},
"customer_data",
JoinKind.Left
),
ExpandCustomer = Table.ExpandTableColumn(
CustomerJoined,
"customer_data",
{"customer_name", "customer_segment", "account_manager"},
{"customer_name", "customer_segment", "account_manager"}
),
// Enrich with product dimension
ProductJoined = Table.NestedJoin(
ExpandCustomer,
{"product_id"},
cln_products,
{"product_id"},
"product_data",
JoinKind.Left
),
ExpandProduct = Table.ExpandTableColumn(
ProductJoined,
"product_data",
{"product_name", "product_category", "product_subcategory"},
{"product_name", "product_category", "product_subcategory"}
),
// Final column selection and ordering for report consumers
FinalColumns = Table.SelectColumns(
ExpandProduct,
{
"order_id", "order_date", "ship_date",
"customer_id", "customer_name", "customer_segment", "account_manager",
"product_id", "product_name", "product_category", "product_subcategory",
"region_code", "currency_code", "shipping_method",
"quantity", "unit_price", "discount_pct", "is_exceptional_discount",
"extended_price", "sales_rep_id", "order_status"
}
)
in
FinalColumns
The conformed query is where the business logic documentation really earns its keep. Any analyst looking at this query can read the comment block and understand every editorial decision that was made. When the finance team asks "Why is order 87234 not in the report?" you can point directly to the ActiveOrdersOnly step and explain the rule.
Parameterizing Business Rules
Hard-coding business rules as literals in your query is an anti-pattern that you'll regret. The rule [discount_pct] >= 0.5 for flagging exceptional discounts will change. When it does, you'll need to find every query where you typed 0.5 — and you'll probably miss one.
Instead, create a parameters table. This is a technique where you store configuration values in a small reference table (either hardcoded in Power Query or sourced from an external config file or SharePoint list) and look them up by name.
// In the Parameters group: tbl_business_rules
let
Source = Table.FromRows(
{
{"exceptional_discount_threshold", "0.5"},
{"max_valid_discount", "1.0"},
{"default_region_code", "UNALLOCATED"},
{"active_order_statuses", "COMPLETED|SHIPPED"}
},
{"parameter_name", "parameter_value"}
)
in
Source
Then create a helper function to look up values:
// fn_GetParameter
(parameter_name as text) as text =>
let
Lookup = Table.SelectRows(tbl_business_rules, each [parameter_name] = parameter_name),
Value = Lookup{0}[parameter_value]
in
Value
And in your conformed query, reference parameters rather than literals:
exceptional_threshold = Number.From(fn_GetParameter("exceptional_discount_threshold")),
max_discount = Number.From(fn_GetParameter("max_valid_discount")),
ValidDiscounts = Table.SelectRows(
ActiveOrdersOnly,
each [discount_pct] <= max_discount or [discount_pct] = null
),
Tip: For Power BI specifically, you can expose frequently-changing thresholds as native Power Query Parameters (Manage Parameters dialog), which allows report publishers or even end users with the right permissions to modify them without opening the query editor. This is ideal for things like "current fiscal year start date" or "revenue tier thresholds."
Handling Multiple Sources with a Consistent Pattern
The architecture really proves its value when you have multiple source systems. Let's say in addition to orders, you have customer data from a CRM (via API) and product data from a product catalog (via SQL database).
Each source gets its own raw query:
// raw_customers — from CRM REST API
let
Source = Json.Document(
Web.Contents(crm_api_base_url, [RelativePath = "/customers", Headers = [#"Authorization" = "Bearer " & crm_api_key]])
),
ToTable = Table.FromList(Source[data], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expanded = Table.ExpandRecordColumn(ToTable, "Column1", {"id", "name", "segment", "account_manager_id", "created_at", "status"})
in
Expanded
// raw_products — from SQL Server
let
Source = Sql.Database(sql_server_name, sql_database_name),
ProductTable = Source{[Schema = "dbo", Item = "products"]}[Data]
in
ProductTable
Each then gets a corresponding cleansed query (cln_customers, cln_products) that applies the same philosophy: type correction, NULL normalization, whitespace handling, structural cleanup — no business rules.
The conformed layer (cfm_sales_fact) then reaches into all three cleansed layers to join the enriched result. The conformed layer doesn't know or care whether customers came from a REST API or a SQL database. That's been abstracted away by the cleansed layer.
This is the seam principle in action: each layer provides a clean interface to the layer above it. Swap the CRM for a different vendor? Rewrite raw_customers and cln_customers. The conformed layer is untouched.
Here is where architects make or break a multi-layer Power Query pipeline. The multi-stage approach adds computational overhead — each query reference adds a step in the dependency chain. In a refresh scenario where Power Query evaluates the full graph, this can mean the same source data is fetched and re-processed multiple times.
Understanding Query Folding in Layered Architectures
Query folding is Power Query's ability to translate M transformations into native source queries (SQL, OData filters, etc.). This is massive for performance — instead of fetching 10 million rows and filtering in Power Query, the database does the filtering and sends you only what you need.
The critical problem with multi-layer referencing: query folding can break at layer boundaries.
When cln_orders references raw_orders, Power Query can usually maintain the folding chain — transformations in cln_orders are added to the query being sent to the source. But when you perform operations that can't be folded (custom M functions, certain Table.NestedJoin variants, Table.Buffer), folding breaks. Everything after that break point is evaluated in Power Query's local engine, row by row.
To inspect folding status, right-click any step in the Applied Steps pane. If "View Native Query" is available, that step is folding. If it's grayed out, folding has broken.
Strategic Use of Table.Buffer
When you know folding will break (for instance, because your raw source is a CSV file and doesn't support folding at all), you can use Table.Buffer at strategic points to prevent redundant re-evaluation.
In a multi-stage architecture, the ideal buffering point is at the boundary between raw and cleansed layers — specifically, at the end of the raw query:
// raw_orders — with strategic buffering
let
Source = Csv.Document(
File.Contents(orders_file_path),
[Delimiter = ",", Columns = 14, Encoding = 65001, QuoteStyle = QuoteStyle.None]
),
PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Buffered = Table.Buffer(PromoteHeaders)
in
Buffered
Table.Buffer forces the table into memory and caches it. Any downstream query that references raw_orders reads from that in-memory cache rather than re-executing the file read. For sources with network latency (SharePoint files, slow APIs), this can reduce refresh time dramatically.
Warning: Table.Buffer is not free. It commits the entire table to memory. For very large datasets (millions of rows), buffering may cause memory pressure or even OutOfMemory errors. Profile your dataset sizes before adding buffers indiscriminately. The right rule is: buffer at the raw layer if and only if (a) folding is not possible, and (b) the raw query is referenced by two or more downstream queries.
In Power BI Desktop, you have another powerful option: load intermediate layers to the data model as tables, not just as query outputs. By default, only the "output" queries — the ones you want as tables in your model — are loaded. But you can force a cleansed layer to load as a table, which means it's materialized during refresh and downstream queries read from the materialized version.
To do this, right-click the cleansed query and uncheck "Enable Load" if you want it to stay as a computed table reference, or keep it enabled if you want it materialized. For truly large datasets where the cleansed-to-conformed transformation is expensive, materializing the cleansed layer can significantly speed up overall refresh.
However, materializing intermediate layers has a cost: storage space in the model and additional data in the import. Be deliberate about which layers you materialize.
Advanced Pattern: Diagnostic and Audit Queries
A mature staging architecture includes diagnostic queries that surface data quality issues at each layer boundary. Rather than silently dropping bad records, you capture them in a separate diagnostic table.
Here's a pattern for capturing records that failed cleansing validation:
// diag_orders_cleansing_failures
let
Source = raw_orders,
// Type the columns the same way as cln_orders
TypeAttempted = Table.TransformColumnTypes(
Source,
{{"order_id", Int64.Type}, {"order_date", type date}, {"unit_price", Currency.Type}}
),
// Identify rows with null order_id after typing (indicates non-parseable value)
NullOrderIds = Table.SelectRows(TypeAttempted, each [order_id] = null),
// Identify rows with null dates (non-parseable dates)
NullDates = Table.SelectRows(TypeAttempted, each [order_date] = null),
// Combine failures with failure reason annotation
OrderIdFailures = Table.AddColumn(NullOrderIds, "failure_reason", each "unparseable_order_id", type text),
DateFailures = Table.AddColumn(NullDates, "failure_reason", each "unparseable_order_date", type text),
AllFailures = Table.Combine({OrderIdFailures, DateFailures}),
// Add audit timestamp
WithTimestamp = Table.AddColumn(
AllFailures,
"detected_at",
each DateTime.LocalNow(),
type datetime
)
in
WithTimestamp
This diagnostic query sits in a separate "Diagnostics" group in the Queries pane. You load it to a table in the model and build a simple "Data Quality" report page that shows your pipeline health. Operations teams love this — it gives them visibility into whether the source is degrading, without having to dig into the query editor.
Documenting the Architecture for Team Consumption
A multi-stage architecture is only as good as its documentation. Without it, a new team member opens the query editor, sees 30 queries with unfamiliar naming conventions, and rewrites everything "simpler" — collapsing your carefully designed layers into a single monolithic query.
In-query documentation using M comments is your first line of defense (as shown in the examples above). But you should also maintain a Query Lineage Document — a simple table, either in a SharePoint page or even a documentation query inside the workbook itself, that maps source to raw to cleansed to conformed.
A documentation query that surfaces its own lineage:
// meta_query_lineage
let
Source = Table.FromRows(
{
{"raw_orders", "CSV file", "orders_file_path", "cln_orders", "cfm_sales_fact"},
{"raw_customers", "CRM REST API", "crm_api_base_url", "cln_customers", "cfm_sales_fact"},
{"raw_products", "SQL Server", "sql_server_name", "cln_products", "cfm_sales_fact, cfm_product_dim"}
},
{"raw_query", "source_type", "connection_parameter", "cleansed_query", "conformed_consumers"}
)
in
Source
Disable loading this query to the model — it's for documentation purposes only. But keeping it in the workbook means it travels with the file and is always up to date (assuming you update it when you add new queries, which should be part of your development workflow).
Hands-On Exercise
Apply everything from this lesson to a scenario you can complete in Power BI Desktop or Excel with Power Query.
The Scenario:
You've been given two data files: a monthly export of HR headcount data (CSV) and a department reference table (also CSV). Your task is to build a three-layer staging architecture that produces a conformed cfm_headcount_fact table.
Setup:
Create two CSV files locally:
headcount_export.csv:
employee_id,dept_code,hire_date,termination_date,salary,status,employment_type
1001,ENG,2019-03-15,,85000,Active,FT
1002,MKT,2020-07-22,2024-01-31,72000,Terminated,FT
1003,eng,2021-11-01,,91000,active,PT
1004,FIN,,, ,Active,FT
1005,UNKNOWN,2018-05-10,,68000,Active,FT
1006,MKT,2022-02-14,,65000,ACTIVE,FT
department_reference.csv:
dept_code,department_name,division,cost_center
ENG,Engineering,Technology,CC-1100
MKT,Marketing,Commercial,CC-2200
FIN,Finance,Corporate,CC-3300
HR,Human Resources,Corporate,CC-3400
Your Tasks:
Build raw_headcount and raw_departments: Connect to both files using a file path parameter. No transformations beyond header promotion.
Build cln_headcount: Apply type casting (dates, integers, currency). Normalize status and employment_type to uppercase. Convert empty strings to null. Normalize dept_code to uppercase.
Build cln_departments: Type cast, trim, uppercase dept_code for reliable joining.
Build cfm_headcount_fact: Apply these business rules:
- Only include
Active employees (business rule, not a cleansing rule)
- Join department reference to get
department_name, division, and cost_center
- For employees with a
dept_code not found in the reference table, assign division = "UNCLASSIFIED"
- Add a calculated column
years_of_service based on hire_date to today
- Exclude records where
hire_date is null (cannot calculate service length reliably)
Build diag_headcount_issues: Capture any records from raw_headcount where employee_id is not a valid integer or salary cannot be parsed as a number.
Verification Checklist:
- Does your raw query contain exactly two steps?
- Does your cleansed query contain zero business rule filters?
- Can you trace every business rule in
cfm_headcount_fact to a named step with a descriptive comment?
- If you rename
raw_headcount in the Queries pane, does cln_headcount still reference it correctly? (It should, if you used Reference, not Duplicate.)
Common Mistakes & Troubleshooting
Mistake 1: Using Duplicate Instead of Reference
This is the most common architecture-breaking mistake. When you duplicate a query, you get a copy that has no dependency on the original. Changes to the raw query don't propagate. You end up with two versions of "truth" that slowly diverge.
Fix: Always use Reference to create downstream layer queries. If you've already duplicated, check whether the first step of the downstream query is Source = some_query (a reference) or Source = Csv.Document(...) (a re-connection). If it's the latter, delete the query and rebuild it correctly.
Mistake 2: Putting Business Logic in the Cleansed Layer
The most seductive mistake: you're already in cln_orders, and someone asks you to filter out test orders. "I'll just put a filter here," you think. "It's harmless."
It's not harmless. Now the cleansed layer has a business rule baked in. When the definition of "test order" changes, you have to dig through cleansing code to find business logic. The next person building a conformed query that should include test orders (for a QA report, for instance) gets filtered data when they reference the cleansed layer.
Fix: The comment "BUSINESS LOGIC: None" at the top of every cleansed query is your guardrail. If you're about to add something that contradicts it, create a conformed query instead.
Mistake 3: Buffering Everything
After learning about Table.Buffer, some people add it everywhere "just in case." Buffering at every layer boundary means everything is in memory simultaneously, and for large datasets, this crashes the refresh.
Fix: Buffer only when folding is impossible AND the query is referenced by multiple downstream queries. Profile first; buffer second.
If your conformed query skips the cleansed layer and reads directly from raw, you're losing the type safety and structural guarantees that the cleansed layer provides. Now your business logic queries have to defensively handle dirty data, which means your cleansing logic ends up duplicated across multiple conformed queries.
Fix: The conformed layer always reads from the cleansed layer. Always. This is not negotiable.
Mistake 5: Circular References
In a correctly designed multi-stage architecture, data flows in one direction: raw → cleansed → conformed. Circular references (where a conformed query's output is referenced back into a cleansed query) will cause Power Query to either throw an error or produce incorrect results.
Fix: If you find yourself wanting to reference a conformed output in an earlier layer, you're probably trying to solve a problem at the wrong layer. Rethink the design. Usually, the right solution is to add another conformed query that handles the specific enrichment you need.
Troubleshooting: "Expression.Error: The name 'cln_orders' wasn't recognized."
This happens when a query that references cln_orders is evaluated before cln_orders is evaluated, or when the name has changed. Power Query resolves query references lazily, but naming errors surface immediately.
Fix: Check the Queries pane to confirm the source query name exactly matches what's referenced. M identifiers are case-sensitive. cln_orders and Cln_orders are different queries.
Troubleshooting: Refresh Is Slow After Adding Layers
Adding layers adds computational steps. If refresh became noticeably slower after implementing staging architecture:
- Check if query folding broke. Right-click each step in the cleansed and raw queries and look for "View Native Query."
- Check if the raw query is being evaluated multiple times (for sources where you have multiple cleansed references). Add
Table.Buffer to the raw query.
- Consider whether materializing the cleansed layer as a table in the model is appropriate.
Summary & Next Steps
You now have a complete, principled framework for building multi-stage Power Query pipelines. Let's anchor the key ideas:
The three layers exist because they have three different jobs. Raw preserves provenance. Cleansed ensures technical correctness. Conformed applies business meaning. Mixing these jobs in a single layer creates brittle, unmaintainable code.
Query References, not Duplicates, enforce the dependency chain. Every cleansed query's first step should be a reference to its raw counterpart. Every conformed query references one or more cleansed queries. Data flows in one direction.
Naming conventions and comments are architecture. A file with 40 queries and no naming convention is not an architecture — it's a puzzle. The raw_, cln_, cfm_ prefixes and in-query comment blocks turn a query editor into a self-documenting system.
Performance requires deliberate choices. Table.Buffer at raw layer boundaries when folding is impossible. Materialized intermediate tables when the transformation workload justifies it. Never buffer indiscriminately.
Business rules have one home: the conformed layer. Enforce this as a team norm, not just a personal habit. The comment "BUSINESS LOGIC: None" in your cleansed queries is a conversation starter, not just documentation.
Where to Go From Here
With multi-stage staging architecture as your foundation, the natural next steps are:
- Incremental Refresh in Power BI: Now that your conformed layer is cleanly defined, implementing incremental refresh (loading only new or changed data) becomes much more tractable. The layer boundaries give you natural points to add date-range parameters.
- Custom Function Libraries: The helper function pattern (
fn_GetParameter) extends into full function libraries — reusable transformation functions that encode your organization's data standards and can be imported across multiple reports.
- Dataflow Integration: Power BI Dataflows implement exactly this layer concept at the service level, with Gen2 dataflows supporting computed entities (the equivalent of your conformed queries). Understanding the architecture you built here maps directly to the Dataflow design model.
- Delta Detection and Change Data Capture: With a stable raw layer preserving historical data, you can build delta detection logic that compares the current raw layer to a previous snapshot and produces a change set — a primitive but effective form of CDC without needing database infrastructure.
The architecture you've built here is not just a Power Query pattern. It's the same medallion/layer thinking that underpins modern lakehouse architectures at enterprise scale. You're not just building better Power Query pipelines — you're building the mental model that scales all the way up.
