Articles

Power Query M Interview Questions: Query Folding, Types, and Refresh Failures

Practice Power Query M interview questions on query folding, buffering, locale-aware types, error handling, and Desktop versus service refresh failures.

Power Query M interview questions often begin with a short transformation and end with a production failure. The preview looks correct, but refresh reads far too much data. A number changes meaning after deployment. Replacing every error with zero makes the query succeed while damaging the report.

Correct rows do not prove efficient source access; efficient access does not prove a reliable scheduled refresh.

Here, the focus is M transformations and their operational boundaries.

Evidence boundary: Microsoft documentation supplies official product facts. The three M snippets passed Microsoft Power Query Parser 2.0.0 grammar checks, and fixture arithmetic was checked independently; we have not executed these queries through a Power Query engine, captured their SQL requests, or run a Power BI service refresh. Predicted results are labeled accordingly. No candidate report is used to promise particular interview questions. Sources were checked on September 15, 2026.

What query folding actually proves

Official product fact: folding translates supported M transformations into work performed by the data source. A query can fold fully, partially, or not at all. Support depends on the connector, source, and transformations; a CSV file has no query engine to execute a pushed-down filter. Microsoft's folding overview.

Explain the consequence in terms of transferred rows and columns, local computation, and source load. “Folding makes everything fast” ignores expensive database plans, network latency, and a source already under pressure.

The M script also describes dependencies rather than a guaranteed sequence of fully materialized tables. Official evaluation behavior: optimization and lazy evaluation can avoid unnecessary work or reorder operations while respecting M semantics. The number of Applied Steps is therefore not a reliable cost model. Query evaluation.

In an interview, distinguish a hypothesis from evidence: “I expect this filter to fold with the SQL connector; I would inspect the emitted request” is stronger than declaring success because the function appears simple.

Exercise one: same rows, different execution placement

Use a disposable SQL Server table named dbo.Orders, with columns OrderId as integer, Region as text, OrderDate as SQL date, and Amount as decimal. Populate these six synthetic rows:

OrderId  Region  OrderDate   Amount
1        East    2026-08-31  10.00
2        East    2026-09-01  20.00
3        West    2026-09-01  30.00
4        East    2026-09-02  40.00
5        West    2026-09-02  50.00
6        North   2026-09-03  60.00

The contract is East orders on or after September 1, returning only OrderId and Amount. Row order is unspecified. Predicted output: (2, 20.00) and (4, 40.00), with a total of 60.00. The boundary date includes order 2 and excludes order 1.

This first query introduces a buffer before filtering:

let
    Source = Sql.Database("sql.example", "InterviewLab"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    Buffered = Table.Buffer(Orders),
    Filtered = Table.SelectRows(Buffered, each
        [Region] = "East" and
        [OrderDate] >= #date(2026, 9, 1)),
    Output = Table.SelectColumns(Filtered,
        {"OrderId", "Amount"})
in
    Output

Compare it with a version that removes the unnecessary buffer:

let
    Source = Sql.Database("sql.example", "InterviewLab"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    Filtered = Table.SelectRows(Orders, each
        [Region] = "East" and
        [OrderDate] >= #date(2026, 9, 1)),
    Output = Table.SelectColumns(Filtered,
        {"OrderId", "Amount"})
in
    Output

Replace the example server and database with your lab connection. Keep the source unchanged while comparing versions. An in-memory #table substitute can check transformation logic, but it cannot demonstrate SQL query folding.

Official function behavior: Table.Buffer buffers scalar cells and prevents downstream folding; its buffering is shallow. Reading and storing the data can make a query slower. It is not a universal optimization switch. Table.Buffer.

Do not report exact network row counts without a trace; metadata requests, previews, caching, and connector behavior complicate that measurement.

How would you verify the optimization?

First compare the output as a set of typed rows, not only its total. A total of 60 could conceal duplicated and missing orders. Then inspect the request sent to the source for the final step. Does it contain the region/date predicates and the two requested columns?

Official tooling distinction: query folding indicators and the query plan feature are available in Power Query Online. Where the connector supports it, View Native Query or View data source query provides another inspection path. A disabled menu is not universal proof that no folding occurred. Query plan and folding examples.

In Desktop, use Query Diagnostics for the relevant operation and inspect source requests when available. Official scope: diagnostics can include authoring activity beyond the single transformation you are investigating. Query Diagnostics.

For a meaningful comparison, record the input version, connector, cache conditions, requested columns, transferred rows when observable, source duration, and full refresh duration. Repeat under comparable conditions. Six rows validate the contract; a representative larger dataset reveals the performance trade-off. Neither a faster preview nor a fabricated benchmark establishes production improvement.

Types are a data contract, not a display setting

Official function fact: Table.TransformColumnTypes performs conversions using type-specific conversion functions. It accepts a culture argument, and a missing requested column raises an error unless an alternative missing-field policy is supplied. Table.TransformColumnTypes.

Ask what the source text means before converting it. Does 1.234,50 mean one thousand two hundred thirty-four and a half? Is a blank amount unknown, or explicitly zero? Is an identifier such as 00127 a number at all?

An automatic conversion that removes leading zeros can corrupt a join key while remaining syntactically valid. Keep identifiers as text when their representation is meaningful. For a mandatory amount column, silently introducing nulls after a rename can conceal schema drift; fail with a useful diagnostic instead. Reserve permissive missing-field behavior for fields the contract explicitly makes optional.

Official parsing fact: Number.FromText accepts a culture controlling how the text is interpreted. Number.FromText. Use a documented source convention rather than the locale of whichever machine happens to refresh the query.

Exercise two: keep four outcomes distinct

This original fixture represents nonnegative amounts supplied under the German locale. Its policy accepts zero, classifies a null as missing, quarantines unparseable text, and separately rejects negative amounts. Negative values are invalid for this fixture, not for every financial dataset.

let
    Raw = #table(
        type table [Id=number, AmountText=nullable text],
        {{1,"1.234,50"}, {2,"0"}, {3,null},
         {4,"pending"}, {5,"-3,50"}, {6,"12,50"}}),
    Parsed = Table.AddColumn(Raw, "Attempt", each
        try Number.FromText([AmountText], "de-DE")),
    Classified = Table.AddColumn(Parsed, "Status", each
        if [Attempt][HasError] then "Parse error"
        else if [Attempt][Value] = null then "Missing"
        else if [Attempt][Value] < 0 then "Rule error"
        else "Valid", type text),
    Amounts = Table.AddColumn(Classified, "Amount", each
        if [Status] = "Valid" then [Attempt][Value]
        else null, type nullable number),
    Output = Table.SelectColumns(Amounts,
        {"Id", "AmountText", "Status", "Amount"})
in
    Output

Official error-handling fact: a try expression exposes whether evaluation failed and returns either the value or an error record. Inspecting that record preserves a distinction that a blanket fallback discards. M error handling.

Predicted results: IDs 1, 2, and 6 are valid, with amounts 1234.50, 0, and 12.50. ID 3 is missing; ID 4 has a parse error; ID 5 violates the nonnegative rule. The valid total is 1247.00. All six input rows remain visible for reconciliation.

Our independent fixture check confirms these expected counts and arithmetic. It does not prove that a connector or Power Query runtime executed the M code. The grammar check likewise cannot certify numeric conversion behavior or refresh success.

Why not use try otherwise zero everywhere?

That replacement makes an unknown amount indistinguishable from a genuine zero. It also turns a source-format change into an apparently successful refresh. In this fixture, the valid zero must survive; the invalid records must remain explainable.

Keep raw values, row identifiers, and a reason beside rejected records. Decide whether downstream publication should fail, proceed with an explicit quality threshold, or publish a separate quarantine result. This is a business decision, not a universal M convention.

For example, if the contract requires every amount, the fixture should block publication despite its three valid rows. If partial reporting is permitted, expose accepted and rejected counts alongside the subtotal. Calling 1247.00 “complete revenue” would overstate what the accepted subset establishes.

Official profiling limit: column profiling defaults to the first 1,000 rows. Switch to the entire dataset when validating population-wide quality, and consider the cost. A clean preview cannot establish that later rows are clean. Data profiling tools.

Diagnose refresh failures in the right environment

A useful incident answer starts with the failing host and phase. Capture the refresh timestamp, exact error, affected query, first failing step, and whether the failure is reproducible with the same input. Then choose an observation that separates competing explanations.

Symptom	First useful check
Source cannot be reached	Gateway path and identity
New rows fail conversion	Raw values and culture
A required column disappears	Source schema and contract
Refresh grows much slower	Source request and volume
Cross-source combination fails	Privacy rules and host version

Official service constraints: a source that the service cannot reach directly needs an appropriate gateway connection. Credentials and source mappings must be valid in the refresh environment. Most dynamic data sources cannot refresh in the service, with documented exceptions including suitable Web.Contents RelativePath and Query usage. Power BI refresh documentation.

For an illustrative Desktop-success/service-failure incident, test source access from the gateway environment before rewriting a working filter. A local file path available on a laptop is not evidence that the refresh host can read it. If access succeeds, move to the failing transformation, then inspect data volume and resource pressure. Change one cause at a time so the successful retry explains something.

The Privacy Firewall answer needs a version

Official purpose: the firewall helps prevent unintended movement of data between sources during evaluation and folding. A cross-source join is therefore also a data-sharing decision. Data Privacy Firewall.

Current official version detail: Microsoft documents a setting introduced in July 2026 Power BI Desktop, enabled by default, that allows partitions to reference other partitions and access sources when the involved privacy levels are compatible. Older Desktop behavior can reject that pattern. This change does not remove privacy checks. Updated partition behavior.

Before repeating an old workaround, identify the host, version, setting, and source privacy classifications. Do not automatically disable privacy protection or relabel confidential data as public to make refresh succeed. Explain which source would receive which values and choose an approved combination strategy.

Five related questions to practice

These are adjacent data-cleaning and ETL exercises, not M execution environments. Follow each question's tool constraints; the dash-date exercise specifically asks for an Excel solution without Power Query and a pandas alternative.

Practice question	Skill
Fix dash dates and aggregate watch time	Missing dates
Explain ETL schema changes and ensure integrity	Schema rules
Analyze and Clean European Ecommerce Sales Data	Row selection
Build a Transaction CSV Cleaning Pipeline	Error handling
Design a 25,000-CSV ETL pipeline	Refresh design

Defend one exact output, one execution-placement hypothesis, and one observation that would falsify your diagnosis.