Power Query and Data Transformation Interview Questions for Power BI
25 Power Query and Data Transformation questions with answers for power bi roles — 9 easy, 12 medium, 4 hard.
About Power Query and Data Transformation
Power Query is where messy Excel files, SQL tables and SharePoint folders become clean, reliable tables. Learn merge and append, unpivot, data types, parameters, custom functions, combining files from folders, error handling, privacy levels, dataflows and query folding, with M code examples that interviewers often ask freshers and experienced developers to explain.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
Power Query is the data connection and transformation engine in Power BI, Excel, dataflows and Fabric Data Factory. It lets you connect to hundreds of sources and shape data with a recorded series of steps, such as removing columns, changing types, merging and unpivoting. Behind the interface each step is written in the M language. Steps re-run automatically on every refresh, making cleaning repeatable.
02
What is the difference between Merge Queries and Append Queries?
Merge is like a SQL join: it combines columns from two tables based on matching key columns, with join kinds like left outer, inner, full outer, left anti and right anti. Append stacks rows of tables with the same structure on top of each other, like a SQL UNION ALL, for example combining monthly sales files. Merge widens a table, append lengthens it.
Unpivot turns columns into rows, creating attribute and value columns. It is used when data comes in a wide, report-style layout, such as months as separate columns Jan, Feb, Mar. Unpivoting them into a Month column and an Amount column gives a tall, tidy table that works well with relationships, a date table and DAX. Unpivot Other Columns is safer because new months are handled automatically.
04
Why should you set data types correctly in Power Query?
Correct data types make sorting, filtering, aggregation and relationships work properly; text-typed numbers cannot be summed and mismatched key types break relationships. Types also affect compression and model size, for example whole numbers compress better than decimals or text. Set types once near the end of a query and avoid repeated automatic Changed Type steps, which can fail when column names change.
05
Every month a new Excel file arrives in a folder. How do you load all of them automatically?
Use the Folder connector, or SharePoint Folder for files on SharePoint, pointing to the folder, filter to the right file extension and naming pattern, then use Combine Files. Power Query creates a sample-file transform function applied to every file and appends the results. New files dropped into the folder are included on the next refresh, as long as their structure matches the sample.
06
How do you add a conditional column in Power Query?
Use Add Column, Conditional Column and define rules, or write M directly:
= Table.AddColumn(Source, "Band", each
if [Amount] >= 100000 then "High"
else if [Amount] >= 25000 then "Medium"
else "Low", type text)
Adding the type at the end avoids an extra Changed Type step. Conditional columns built here are computed at refresh, not at query time.
07
How do you split a full name column and clean text in Power Query?
Select the column, use Transform, Split Column by Delimiter (space, leftmost) to get first and last names. Then use Format, Trim and Clean to remove extra spaces and non-printable characters, and Capitalize Each Word for consistent case. In M:
= Table.TransformColumns(Source, {{"Name", each Text.Proper(Text.Trim(_)), type text}})
08
How do you handle a business user who keeps changing report requirements?
I first understand the goal behind the changes, because frequent changes often mean the core question was not clear. I write down agreed requirements and a mock-up, then deliver in small iterations so feedback comes early. In one project weekly 20-minute reviews cut rework a lot, and changes that were large were logged for the next version rather than added mid-sprint.
09
What is the difference between Power Query M and DAX?
M is the language of Power Query, used at refresh time to extract, clean and shape data before it enters the model, such as merging tables or splitting columns. DAX is used after data is loaded, to define measures, calculated columns and security that evaluate in filter context at query time. As a rule, do row-level shaping in M or the source and dynamic calculations in DAX.
Query folding is when Power Query translates transformation steps into a native source query, such as SQL, so the source does the work and only the result is loaded. It makes refreshes much faster and is required for incremental refresh and efficient DirectQuery. Steps like filtering, removing columns and grouping usually fold on SQL sources, while some custom M functions or adding an index column can break folding.
Parameters are named values used inside queries, such as server name, database, file path or a date range. They let you switch from a development to a production database without editing each query, and they are required for incremental refresh through RangeStart and RangeEnd. Parameters can be changed in the Service under semantic model settings, which helps deployment pipelines between environments.
12
What is a dataflow and how is it different from Power Query in Desktop?
A dataflow runs Power Query transformations in the Power BI Service and stores the result in the cloud, so multiple semantic models can reuse the same cleaned tables. Desktop Power Query logic lives inside one model only. Dataflows Gen2 in Fabric can also write output to lakehouses or warehouses. Dataflows centralise preparation logic, reduce duplicate source load and separate data prep from modelling.
13
What is the difference between Reference and Duplicate in Power Query?
Duplicate copies all the steps of a query into a new independent query, so later changes to the original do not affect it. Reference creates a new query whose source is the output of the original, so it inherits any changes. Reference is useful for building several tables from one staging query, such as dimension tables from a cleaned sales table, while Duplicate is for experimenting separately.
14
Refresh takes 90 minutes because of heavy Power Query steps. How do you speed it up?
I would check query folding with View Native Query or the folding indicators and move non-folding steps after folding ones. Filter rows and remove unused columns as early as possible, push joins and aggregations into SQL views, avoid row-by-row custom functions over large tables, and disable load for staging queries. For large fact tables, set up incremental refresh so only recent partitions reload.
15
Sales data has region codes, while targets use region names. How do you combine them?
I would create or import a region mapping table with code and name, clean both sides by trimming spaces and fixing case, then merge in Power Query or better, build a Region dimension table with both columns and relate sales by code and targets by name to it. Unmatched rows should be checked using a left anti join so missing mappings are fixed rather than silently dropped.
16
A source table has duplicate customer rows with different spellings of the same name. What do you do?
For exact duplicates, Remove Duplicates on the key column. For spelling variations, use fuzzy matching in Merge Queries with a similarity threshold and transformation table, review results, and maintain a mapping table of approved matches. The best long-term fix is to use a stable customer ID from the source system and report data quality issues to the owner rather than cleaning endlessly in Power BI.
17
Write an M query that filters rows, removes columns and changes types.
let
Source = Sql.Database("server", "SalesDB"),
Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2024, 1, 1)),
Kept = Table.SelectColumns(Filtered, {"OrderID", "OrderDate", "CustomerID", "Amount"}),
Typed = Table.TransformColumnTypes(Kept, {{"Amount", Currency.Type}, {"OrderDate", type date}})
in
Typed
All these steps fold to SQL on a database source.
let
StartDate = #date(2022, 1, 1),
EndDate = #date(2026, 12, 31),
Days = Duration.Days(EndDate - StartDate) + 1,
Dates = List.Dates(StartDate, Days, #duration(1, 0, 0, 0)),
T = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
Typed = Table.TransformColumnTypes(T, {{"Date", type date}}),
Y = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
M = Table.AddColumn(Y, "Month", each Date.MonthName([Date]), type text)
in
M
19
Describe a time you cleaned very messy data under a deadline.
Situation: a client sent 36 monthly Excel files with merged headers and changing columns two days before a review. Task: build a consolidated sales report. Action: I used the Folder connector with a custom function that promoted the right header row, unpivoted month columns and mapped renamed fields through a lookup table. Result: the report was ready a day early, and future files loaded automatically with no manual work.
20
How do you handle errors in Power Query without failing the whole refresh?
Use Keep Errors on a copy of the query to inspect the bad rows, then fix the cause, such as wrong data types or invalid dates. Replace Errors can substitute a value, and in M try ... otherwise catches errors per value:
= Table.AddColumn(Source, "AmountNum", each try Number.From([Amount]) otherwise null, type nullable number)
Log error rows to a separate query so they can be reviewed rather than silently dropped.
21
What is the difference between Group By in Power Query and summarising in DAX?
Group By in Power Query aggregates rows at refresh time and stores only the summarised table, reducing model size, which is useful when detail is never needed. Summarising in DAX through measures keeps detail in the model and aggregates dynamically for any filter at query time. Use Power Query grouping for fixed summaries such as monthly snapshots, and DAX measures when users need flexible slicing.
22
What are the privacy levels in Power Query and why do they cause errors?
Privacy levels, Private, Organizational and Public, control whether data from one source can be sent to another source during combining, to avoid leaking sensitive data. When a query mixes sources with incompatible levels, Power Query may block it with a Formula.Firewall error or stop folding. The fix is to set correct privacy levels for each source or restructure queries so sources are combined in a staged way.
23
A refresh fails with 'column not found' after the source team renamed a column. How do you make queries more robust?
I would fix the immediate step, then reduce dependence on exact column names: avoid hard-coded Changed Type steps on all columns, select only needed columns with MissingField.Ignore where suitable, and standardise names in one place. I would agree a data contract or SQL view with the source team so renames go through a stable interface, and set up refresh failure notifications.
24
How do you set up a custom function in Power Query to apply logic to many files?
Write a query for one file, turn the file path into a parameter, then right-click and Create Function. Or write it in M:
(path as text) as table =>
let
Src = Excel.Workbook(File.Contents(path), true),
Sheet = Src{[Item="Sales", Kind="Sheet"]}[Data]
in
Sheet
Then invoke it on a column of file paths with Add Column, Invoke Custom Function and expand the results.
25
Two queries return the same data from different systems, and totals do not match. How do you investigate?
I would compare row counts and totals at a high level first, then drill down by date and key dimensions to find where the difference starts. Merge both tables with a full outer join on the business key and use anti joins to list missing rows, then compare values on matched rows. Common causes are different cut-off times, filters, duplicates, time zones or currency conversions.
Frequently asked questions
How important is Power Query and Data Transformation in Power BI interviews?
Power Query is where messy Excel files, SQL tables and SharePoint folders become clean, reliable tables. Learn merge and append, unpivot, data types, parameters, custom functions, combining files from folders, error handling, privacy levels, dataflows and query folding, with M code examples that interviewers often ask freshers and experienced developers to explain.
How should I use the Power Query and Data Transformation sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.