In this post we are going to cover Power Query and query folding: the M language, how folding works, which steps break it, Merge and Append, incremental refresh and the Formula.Firewall error. These are 7 real Power BI interview questions, and the last one is a scenario a manager asks: a refresh of a 50-million-row table takes two hours and sometimes times out.
Power Query is where data gets cleaned and shaped before it lands in the model. Query folding decides who does that work: the source database, or Power Query itself. On a small table nobody notices. On a big table, folding is the difference between a refresh that takes minutes and one that times out. That is why interviewers ask about it.
Every example below runs on a small sample data set, so you can rebuild it and check each number yourself.
Part 3 of the Real Time Power BI Interview Questions and Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: What is Power Query, what is the M language, and where does Power Query run?
- Question 2: What is query folding, and how do you check whether a step folds?
- Question 3: Which steps break query folding, and how should you order your steps?
- Question 4: What is the difference between Reference and Duplicate, and between Merge and Append?
- Question 5: How do parameters and incremental refresh work, and why must the filter fold?
- Question 6: What are privacy levels, and how do you fix a Formula.Firewall error?
- Question 7: Scenario - refreshing a 50-million-row SQL Server table takes two hours and sometimes times out. What do you do?
- The full SQL script
The short answer
Power Query shapes data with steps written in M. Query folding pushes those steps to the source as one native query; everything after a non-folding step runs locally. Filter early, check folding, and use incremental refresh for big tables.
Keep that summary in mind. Every question below is one piece of it.
Let's create some sample data
We use a tiny shop: 5 products, 4 customers in two regions, a date table for 2025 and 2026, and 12 sales in January to March of each year. The script below is written for PostgreSQL (the date table uses generate_series), and it works on any database with small changes. Load the four tables into Power BI Desktop and relate fact_sales to each dimension on its key. The M examples below connect to the same tables in a SQL Server database called TBShop.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);Here are the 12 sales. Each row is one sale line: a date, a product, a customer, a quantity and a unit price.
| sale_id | order_date | product_key | customer_key | qty | unit_price |
|---|---|---|---|---|---|
| 1 | 2025-01-10 | 1 | 1 | 2 | 40.00 |
| 2 | 2025-01-22 | 3 | 2 | 1 | 250.00 |
| 3 | 2025-02-05 | 2 | 3 | 5 | 10.00 |
| 4 | 2025-02-18 | 5 | 4 | 1 | 60.00 |
| 5 | 2025-03-03 | 4 | 1 | 2 | 50.00 |
| 6 | 2025-03-27 | 3 | 3 | 1 | 250.00 |
| 7 | 2026-01-08 | 1 | 2 | 3 | 40.00 |
| 8 | 2026-01-25 | 3 | 1 | 1 | 250.00 |
| 9 | 2026-02-11 | 2 | 4 | 4 | 10.00 |
| 10 | 2026-02-20 | 4 | 3 | 2 | 50.00 |
| 11 | 2026-03-09 | 5 | 2 | 2 | 60.00 |
| 12 | 2026-03-30 | 3 | 4 | 2 | 250.00 |
And the products. We use them for the merge in Question 4.
| product_key | product | category |
|---|---|---|
| 1 | Laptop Stand | Accessories |
| 2 | USB-C Cable | Accessories |
| 3 | Monitor | Electronics |
| 4 | Keyboard | Electronics |
| 5 | Desk Lamp | Home |
Question 1: What is Power Query, what is the M language, and where does Power Query run?
Power Query is the data preparation engine in Power BI. You connect to a source, then clean and shape the data with steps in the editor. Every step you click is written in M, the Power Query formula language.
Here is the simplest query: connect to SQL Server and pick the sales table.
let
Source = Sql.Database("srv", "TBShop"),
Sales = Source{[Schema="dbo",Item="fact_sales"]}[Data]
in
SalesA query is a let expression: a chain of named steps, and the step after in is the output. Every click in the editor adds one more named step to that chain.
Power Query runs in two places:
- Power Query Desktop - inside Power BI Desktop and Excel.
- Power Query Online - in the browser, for example in Power BI dataflows and Dataflow Gen2 in Microsoft Fabric.
A strong answer adds the key point: Power Query shapes the data before it lands in the model. So clean it here, or even further upstream, and keep DAX for business logic.
Question 2: What is query folding, and how do you check whether a step folds?
Query folding means Power Query translates your steps into the source's own language, so the source does the work.
Let's filter the sales table to 2026 and keep four columns.
let
Source = Sql.Database("srv", "TBShop"),
Sales = Source{[Schema="dbo",Item="fact_sales"]}[Data],
Rows2026 = Table.SelectRows(Sales,
each [order_date] >= #datetime(2026,1,1,0,0,0)),
Cols = Table.SelectColumns(Rows2026,
{"sale_id", "order_date", "qty", "unit_price"})
in
ColsAgainst SQL Server, those two steps fold into one select statement with a where clause. This is roughly what reaches the database:
-- folded query (simplified)
select [sale_id], [order_date], [qty], [unit_price]
from [dbo].[fact_sales]
where [order_date] >= '2026-01-01'And this is what comes back.
| sale_id | order_date | qty | unit_price |
|---|---|---|---|
| 7 | 2026-01-08 | 3 | 40.00 |
| 8 | 2026-01-25 | 1 | 250.00 |
| 9 | 2026-02-11 | 4 | 10.00 |
| 10 | 2026-02-20 | 2 | 50.00 |
| 11 | 2026-03-09 | 2 | 60.00 |
| 12 | 2026-03-30 | 2 | 250.00 |
Check this result yourself with SQL
The filter and the column selection fold into one SELECT, so only the six 2026 rows leave the database.
SELECT sale_id, order_date, qty, unit_price
FROM fact_sales
WHERE order_date >= DATE '2026-01-01'
ORDER BY sale_id;Only the 6 sales from 2026 leave the database, with only the four columns we asked for. On 12 rows that doesn't matter. On 50 million rows it matters a lot.
How do you check it?
- View Native Query - in Desktop, right-click a step. If it is available, that step folds. If it is greyed out, it may not fold, or the connector just can't show it.
- Folding indicators and the query plan - in Power Query Online, for every step.
- Query diagnostics - in Desktop, shows the exact query sent to the source.
Question 3: Which steps break query folding, and how should you order your steps?
Folding stops at the first step the source can't translate. And here is the key point: the steps after it usually run locally in the Power Query engine, even simple filters that could have folded.
Here is the bad order. We add an index column first, then filter.
// index first: the filter runs locally
Idx = Table.AddIndexColumn(Sales, "Idx", 1, 1),
Rows = Table.SelectRows(Idx,
each [order_date] >= #datetime(2026,1,1,0,0,0))The index doesn't fold, so the filter after it runs in Power Query, and the whole table is read from SQL Server.
Now the same steps in a better order: filter rows and remove columns first, so they fold, and add the index last, on the small result.
// filter and trim first: these fold
Rows = Table.SelectRows(Sales,
each [order_date] >= #datetime(2026,1,1,0,0,0)),
Cols = Table.SelectColumns(Rows, {"sale_id", "qty"}),
Idx = Table.AddIndexColumn(Cols, "Idx", 1, 1)Now only the index runs locally. Common folding breakers to name:
- Add index column
- Table.Buffer
- Some custom M functions
- Combining different sources, for example SQL with Excel
The rule to say: filter rows and remove columns early, and put non-folding steps as late as possible, because everything after them runs in the mashup engine.
Question 4: What is the difference between Reference and Duplicate, and between Merge and Append?
Duplicate copies all the steps into a new, independent query. Reference starts a new query from the output of the original, so if the original changes, the new one follows.
Merge is a join: it adds columns from another table. Append is a union: it stacks rows from tables with the same columns.
| What it does | |
|---|---|
| DUPLICATE | an independent copy of all the steps |
| REFERENCE | a new query that starts from the output of the original |
| MERGE | join, adds columns: left outer, inner, full outer, left anti... |
| APPEND | union, stacks rows from tables with the same columns |
Let's try a left anti merge: keep the products that have no match in the March 2026 sales.
Mar2026 = Table.SelectRows(Sales, each
[order_date] >= #datetime(2026,3,1,0,0,0) and
[order_date] < #datetime(2026,4,1,0,0,0)),
NoSales = Table.NestedJoin(Product, {"product_key"},
Mar2026, {"product_key"}, "S", JoinKind.LeftAnti),
Result = Table.RemoveColumns(NoSales, {"S"})| product_key | product | category |
|---|---|---|
| 1 | Laptop Stand | Accessories |
| 2 | USB-C Cable | Accessories |
| 4 | Keyboard | Electronics |
Check this result yourself with SQL
A left anti join keeps the left rows (products) that have no match on the right (March 2026 sales).
SELECT p.product_key, p.product, p.category
FROM dim_product p
WHERE NOT EXISTS (
SELECT 1 FROM fact_sales f
WHERE f.product_key = p.product_key
AND f.order_date >= DATE '2026-03-01'
AND f.order_date < DATE '2026-04-01')
ORDER BY p.product_key;The answer is 3 products: Laptop Stand, USB-C Cable and Keyboard. In March 2026 we only sold a Desk Lamp and a Monitor, so the other three have no match on the right side.
Use Reference to build one cleaned base query and branch from it. Then turn off Enable load on the helper queries, so they don't land in the model.
Question 5: How do parameters and incremental refresh work, and why must the filter fold?
A parameter is a named value you reuse, like a server name, so you can switch from dev to production in one place. Incremental refresh uses two special DateTime parameters, named exactly RangeStart and RangeEnd.
You filter the date column with them: greater than or equal to RangeStart, and less than RangeEnd.
// parameters: RangeStart, RangeEnd (DateTime)
Rows = Table.SelectRows(Sales, each
[order_date] >= RangeStart and
[order_date] < RangeEnd)The service creates the partitions and sets the two values for each one. For the March 2026 partition, the filter folds into this where clause:
-- one partition: March 2026
where [order_date] >= '2026-03-01 00:00:00'
and [order_date] < '2026-04-01 00:00:00'| sale_id | order_date | qty | unit_price |
|---|---|---|---|
| 11 | 2026-03-09 | 2 | 60.00 |
| 12 | 2026-03-30 | 2 | 250.00 |
Check this result yourself with SQL
Each partition is the same folded query with different RangeStart and RangeEnd values; March 2026 has two rows.
SELECT sale_id, order_date, qty, unit_price
FROM fact_sales
WHERE order_date >= DATE '2026-03-01'
AND order_date < DATE '2026-04-01'
ORDER BY sale_id;That partition returns only 2 rows, sales 11 and 12. Every partition is the same query with different values.
The policy has two windows. The archive window keeps older history without refreshing it. The refresh window is the recent period that gets reloaded every time.
Two pitfalls to mention:
- The filter must fold. If it doesn't, Power Query may read every row from the source for every partition.
- Licensing. Pro, PPU and capacities support incremental refresh, but the real-time DirectQuery partition needs PPU or a capacity.
Question 6: What are privacy levels, and how do you fix a Formula.Firewall error?
Each data source has a privacy level: Private, Organizational or Public. Privacy levels stop Power Query from leaking data from one source into a query sent to another source.
To enforce that, the firewall splits your queries into partitions. A partition may access a data source, or reference other queries, but not both. Break that rule, and you get this error:
Formula.Firewall: Query 'Sales' (step 'Merged Queries') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
The fix is to restructure, in this order:
- Stage each source in its own query. For example, a Sales query for SQL and a Targets query for Excel.
- Combine by referencing only. Do the merge in a separate query that only references the two staging queries and never touches a source.
- Set the right privacy levels. For example, both sources Organizational.
- Ignore privacy levels only as a last resort.
What not to say in the interview: "just tick Ignore the Privacy Levels". That switches off the protection. Use it only when you are sure the combination is safe.
Question 7: Scenario - refreshing a 50-million-row SQL Server table takes two hours and sometimes times out. What do you do?
Answer it as a sequence: find the cause first, fix the query, and talk about licensing last.
- Find out why. On a Pro workspace, a scheduled refresh has a 2-hour limit, so the timeout is expected. Then check folding: View Native Query on the last step, or ask the DBA what query actually reaches the server.
- Fix the query. Move filters and column removal to the top, remove columns nobody uses, and push heavy joins and calculations into a SQL view, so everything folds.
- Add incremental refresh. Keep the history in the archive window and reload only the recent period. Now each refresh reads recent rows, not 50 million.
- Check the gateway. Steps that don't fold run on the gateway machine. Give it enough CPU and memory, keep it close to the database, and use a gateway cluster.
- Only then, licensing. PPU and capacities allow 5 hours, but a faster query beats a longer timeout.
If you open with "buy a bigger license", the manager hears that you would pay to hide the problem. Open with folding.
Key points to remember
- Power Query shapes data; every step is M
- Folding pushes steps to the source as one query
- After a non-folding step, everything runs locally
- RangeStart / RangeEnd filter must fold
- Fix the firewall by staging queries, not by ignoring it
The full SQL script
If you want to rebuild everything at once, here is the whole script: the sample data and the SQL check behind every result above.
Show the full SQL script (sample data and every check)
-- ========================================================================
-- Power BI Interview Q&A #3 - Power Query and query folding
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA pbi_q03; SET search_path TO pbi_q03;
-- SQL Server: write DATE '2026-06-01' as CAST('2026-06-01' AS DATE).
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_product (product_key INT PRIMARY KEY, product VARCHAR(30), category VARCHAR(20));
INSERT INTO dim_product VALUES
(1, 'Laptop Stand', 'Accessories'), (2, 'USB-C Cable', 'Accessories'),
(3, 'Monitor', 'Electronics'), (4, 'Keyboard', 'Electronics'),
(5, 'Desk Lamp', 'Home');
CREATE TABLE dim_customer (customer_key INT PRIMARY KEY, customer VARCHAR(30), city VARCHAR(20), region VARCHAR(10), email VARCHAR(40));
INSERT INTO dim_customer VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@tbshop.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@tbshop.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@tbshop.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@tbshop.com');
-- one row per day, 2025-01-01 .. 2026-12-31 (calendar year = fiscal year here)
CREATE TABLE dim_date (date DATE PRIMARY KEY, year INT, quarter VARCHAR(2), month_no INT, month VARCHAR(3));
INSERT INTO dim_date
SELECT d, EXTRACT(YEAR FROM d), 'Q' || EXTRACT(QUARTER FROM d), EXTRACT(MONTH FROM d), TO_CHAR(d, 'Mon')
FROM generate_series(DATE '2025-01-01', DATE '2026-12-31', INTERVAL '1 day') AS g(d);
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
order_date DATE REFERENCES dim_date(date),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
qty INT,
unit_price DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
( 1, '2025-01-10', 1, 1, 2, 40.00), ( 2, '2025-01-22', 3, 2, 1, 250.00),
( 3, '2025-02-05', 2, 3, 5, 10.00), ( 4, '2025-02-18', 5, 4, 1, 60.00),
( 5, '2025-03-03', 4, 1, 2, 50.00), ( 6, '2025-03-27', 3, 3, 1, 250.00),
( 7, '2026-01-08', 1, 2, 3, 40.00), ( 8, '2026-01-25', 3, 1, 1, 250.00),
( 9, '2026-02-11', 2, 4, 4, 10.00), (10, '2026-02-20', 4, 3, 2, 50.00),
(11, '2026-03-09', 5, 2, 2, 60.00), (12, '2026-03-30', 3, 4, 2, 250.00);
-- Step 1: Folded query: 2026 sales, four columns
SELECT sale_id, order_date, qty, unit_price
FROM fact_sales
WHERE order_date >= DATE '2026-01-01'
ORDER BY sale_id;
-- Step 2: Left anti merge: products with no sales in March 2026
SELECT p.product_key, p.product, p.category
FROM dim_product p
WHERE NOT EXISTS (
SELECT 1 FROM fact_sales f
WHERE f.product_key = p.product_key
AND f.order_date >= DATE '2026-03-01'
AND f.order_date < DATE '2026-04-01')
ORDER BY p.product_key;
-- Step 3: One incremental refresh partition: RangeStart = 2026-03-01, RangeEnd = 2026-04-01
SELECT sale_id, order_date, qty, unit_price
FROM fact_sales
WHERE order_date >= DATE '2026-03-01'
AND order_date < DATE '2026-04-01'
ORDER BY sale_id;
Part 4: Data Modeling and Relationships
The sample data on this page is invented for teaching. The result tables were produced by running the same calculation in SQL on PostgreSQL 16 over that sample data (open "Check this result yourself" under any result), so you can rebuild every number in Power BI Desktop and compare.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.