What is Power Query?
Power Query is the data preparation engine in Power BI, Excel and dataflows. Each change you make in the editor (remove a column, change a type, filter rows) is recorded as an applied step written in the M language, and every refresh replays the steps on the new data. It is where you clean data before it reaches the model.
What is the difference between Merge and Append?
Merge joins two queries side by side on matching columns, like a SQL join, and adds the other table's columns. Its join kinds are left outer, right outer, full outer, inner, left anti and right anti. Append stacks queries with the same columns on top of each other, like
UNION ALL, for example twelve monthly files into one table.Which Power Query transformations do you use most?
Promote headers, change data types, remove or reorder columns, filter rows, replace values, split a column by a delimiter, trim and clean text, fill down, remove duplicates, group by, merge and append, and unpivot. Setting correct data types early matters most: a number stored as text sorts and sums wrongly and blocks relationships.
When would you unpivot columns?
When values sit in column headers, such as a sheet with one column per month. Unpivot turns Jan, Feb and Mar into two columns, Month and Amount, with one row per month. Now one measure sums Amount, the date table can filter it, and next month's column does not need a new measure. Select the columns to keep and use Unpivot Other Columns, so new months are picked up on refresh.
What does an M query look like?
A
letexpression: each step is a name bound to an expression that usually takes the previous step, andinreturns the last one. M is case sensitive.let Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter = ",", Encoding = 65001]), Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), Typed = Table.TransformColumnTypes(Promoted, {{"Amount", type number}, {"Date", type date}}), Paid = Table.SelectRows(Typed, each [Status] = "Paid") in Paideach [Status] = "Paid"is a short function applied to every row.What is query folding, and why does it matter?
Query folding means Power Query translates the applied steps into one query the source runs, such as a SQL
SELECTwithWHEREandGROUP BY, instead of loading everything and transforming it locally. Refreshes are faster and incremental refresh depends on it. Right click a step and check View Native Query, or look at the step's folding indicator. Steps such as an index column or some custom functions stop folding, so put filters and column removal first. See query folding.What are Power Query parameters used for?
Parameters hold values that change between uses of the same queries: the server and database name (development or production), a folder path, a date range, or a row limit while developing. Queries reference the parameter instead of a hard coded value, and in the Service the values can be changed in the semantic model settings or by deployment pipeline rules. Incremental refresh is built on two of them,
RangeStartandRangeEnd.How does incremental refresh work?
Filter the fact query on two date/time parameters,
RangeStartandRangeEnd(one bound inclusive, the other exclusive, so no row loads twice), then set a policy: keep, say, five years and refresh only the last ten days. The Service splits the table into date partitions and refreshes only the recent ones. It needs the filter to fold to the source, otherwise each partition still reads all the data. See incremental refresh.What is the difference between Reference and Duplicate on a query?
Duplicate copies all of a query's steps into a new, independent query; later changes to the original do not reach the copy. Reference creates a query whose source is the original's output, so changes upstream flow through. Use Reference for staging: one query reads and cleans the source with load disabled, and several queries reference it.
How do you find and handle errors in Power Query?
Turn on column quality and column profile under View, and switch profiling from the first 1,000 rows to the entire dataset to see every error. Keep Errors shows only the failing rows so you can find the cause, usually a value that does not fit the column's type. Then fix the source or the step, use Replace Errors with a default, or wrap an expression in
try ... otherwise. Removing error rows silently loses data, so do it only on purpose.What is a dataflow, and when would you use one?
A dataflow is Power Query that runs in the Power BI Service and stores its result, so many semantic models can reuse the same cleaned tables (a shared customer or date table) instead of each repeating the logic. Dataflow Gen2 in Microsoft Fabric can write its output to a lakehouse or warehouse. Use one when several models need the same prepared data or the source should be read once.
Power Query Interview Questions
Shaping data before it loads: applied steps, merges and appends, the M language, query folding, parameters and incremental refresh.