Power Query and Data Cleaning Interview Questions for Advanced Excel
25 Power Query and Data Cleaning questions with answers for advanced excel roles — 4 easy, 15 medium, 6 hard.
About Power Query and Data Cleaning
Power Query imports, cleans and reshapes data with recorded steps that rerun on refresh. Learn folder imports, append and merge, join kinds, unpivot, locale-aware data types, group by, parameters, custom M columns and functions, and query folding. This is the skill that turns hours of monthly copy-paste into a single click.
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.
✓ 3 questions free • Unlock the rest in seconds
01
What is Power Query and why use it instead of manual cleaning?
Power Query (Data > Get & Transform) is Excel's tool to import, clean and reshape data from files, folders, databases and the web. Every action is recorded as a step, so next month you just click Refresh and the same cleaning runs again on new data. This removes repeated copy-paste work, reduces errors and keeps the original source untouched, which manual cleaning cannot offer.
02
What are Applied Steps and the M language in Power Query?
Each transformation you click, like removing columns or changing types, is saved as a step in the Applied Steps pane, and the steps run in order on every refresh. Behind them is M, a functional formula language visible in the Advanced Editor. You can rename steps, delete or reorder them, and edit M directly for logic the interface does not provide, such as custom functions or dynamic column lists.
03
What is the difference between Append and Merge in Power Query?
Append stacks tables one below another, like combining January, February and March sales that have the same columns; it is similar to SQL UNION. Merge joins two tables side by side on a matching key, like adding product category from a Products table to Sales; it is similar to a SQL JOIN or VLOOKUP. Choosing the right one is the first step in any multi-table task.
Select Transform > Group By, choose one or more grouping columns such as Region and Month, then add aggregations like Sum of Amount, Count Rows and Max of Order Date. The Advanced option allows several groupings and aggregations together. The All Rows operation keeps the detail rows as a nested table, useful for ranking within groups or picking the top record per customer.
05
How do you combine all files in a folder using Power Query?
Place files with the same structure in one folder. Go to Data > Get Data > From File > From Folder, select the folder and click Combine & Transform. Choose the sheet or table from the sample file; Power Query creates a helper function and appends all files, adding a Source.Name column with the file name. Apply cleaning steps once, Close & Load, and later just drop new files in and Refresh.
06
Explain the join kinds available in Power Query Merge.
Left Outer keeps all rows from the first table and matches from the second, the most common choice. Right Outer does the reverse. Full Outer keeps all rows from both. Inner keeps only matching rows. Left Anti keeps rows in the first table with no match, and Right Anti keeps rows in the second with no match. Anti joins are excellent for finding missing invoices or customers during reconciliation.
07
Your data has months as columns (Apr, May, Jun...). How do you convert it into a proper table format?
Load the data into Power Query, select the identifying columns such as Branch and Product, then right-click and choose Unpivot Other Columns. The month headers become an Attribute column and the numbers become a Value column; rename them to Month and Sales. Using Unpivot Other Columns rather than Unpivot Columns means new months added later are included automatically on refresh.
08
Your Power Query output has 20 lakh rows and Excel says it cannot load it to a sheet. What do you do?
A worksheet holds at most 1,048,576 rows, so the result will not fit. Choose Close & Load To and select Only Create Connection with Add this data to the Data Model. The model compresses data and can handle many millions of rows, and you analyse it with a pivot table or DAX measures. If users only need summaries, group the data inside Power Query before loading.
09
How do you split and clean a column like 'EMP001 - Rahul Sharma - Delhi' in Power Query?
Select the column, choose Split Column > By Delimiter, enter space-hyphen-space as a custom delimiter and split at each occurrence. This gives ID, Name and City columns. Then use Transform > Format > Trim and Clean to remove extra spaces and hidden characters, Capitalize Each Word for names, and set correct data types. Split by Number of Characters handles fixed-width codes like state and branch prefixes.
10
Why are data types important in Power Query, and what problems does the automatic Changed Type step cause?
Data types decide how values are sorted, summed and loaded, so a number stored as text will not total and a date stored as text will not group by month. Power Query adds an automatic Changed Type step that hard-codes every column name, so a renamed or removed column later causes refresh errors. Many analysts delete it and set types once, deliberately, after the structure is final.
11
Your query refresh fails with 'The column Amount of the table wasn't found'. What happened and how do you fix it?
The source file's header changed, for example Amount became Amount (Rs), and a step that refers to the old name, often Changed Type or Renamed Columns, breaks. Open the query, click through the steps to find the first error, and update the name or rename the column back early in the query. To prevent repeats, agree a fixed template with the data owner and avoid steps that list every column unnecessarily.
12
Dates from a vendor file in dd/mm/yyyy format show as errors or swap day and month. How do you fix it?
The system locale is probably English (United States), which reads dates as mm/dd/yyyy, so 13/04/2026 becomes an error and 05/04/2026 becomes 4 May. In Power Query, right-click the column > Change Type > Using Locale, choose Date and English (India). This tells Power Query how to read the text regardless of the computer's settings, so the file refreshes correctly on any machine.
13
How do you remove duplicate customers but keep the most recent record in Power Query?
Sort by Updated Date descending, then buffer the table so the sort order is respected before removing duplicates on the key:
Sorted = Table.Sort(Source,{{"UpdatedOn", Order.Descending}}),
Buffered = Table.Buffer(Sorted),
Latest = Table.Distinct(Buffered,{"CustomerID"})
Without Table.Buffer, Power Query may not keep the sorted order, so the record kept could be an older one.
14
Describe a time you convinced your team to move from manual cleaning to Power Query.
Situation: our team spent a day each month cleaning vendor files by hand. Task: I believed Power Query could fix it but the team feared a new tool. Action: I rebuilt one month's cleanup as a query, then ran it side by side with the manual process and showed identical results in five minutes. I documented each step and trained two colleagues. Result: the team adopted it, saving about eight hours a month.
15
What are parameters in Power Query and when do you use them?
A parameter is a named value, created in Manage Parameters, that queries can reference instead of a hard-coded value. A common use is a FolderPath parameter so the same workbook works when moved to another computer or shared drive. Parameters can also hold a cut-off date or a region filter. Changing one value updates every query that uses it, which makes files easier to hand over.
16
What is the difference between Reference and Duplicate in Power Query?
Duplicate copies all steps of a query into an independent new query, so 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 future changes. Use Reference to build several outputs from one cleaned staging query, such as a monthly summary and a customer list, so the cleaning logic lives in one place.
17
A report exported from PDF repeats the header row on every page and has page-footer lines. How do you clean it?
Use Get Data > From PDF or import the converted file, then promote the first row to headers. Filter out rows where the first column equals the header text, such as Name, and rows containing footer text like Page or Printed on, using Text Filters > Does Not Contain. Remove blank rows, Fill Down any group labels, and set types. The steps then run automatically on next month's PDF.
18
What checks do you run when cleaning a new dataset?
Check row counts against the source, then look for duplicate keys, blanks in mandatory fields, inconsistent text such as Delhi and New Delhi, extra spaces and mixed case, dates stored as text or in the wrong locale, negative or extreme numbers that look like typos, and codes losing leading zeros. Power Query's Column Quality and Column Profile views show errors, empties and distinct counts for each column quickly.
19
Tell me about a time you automated a repetitive task.
Situation: every Monday I merged five regional order files and cleaned them by hand, which took about three hours. Task: reduce the effort and errors. Action: I built a Power Query folder import, standardised columns, fixed date locale issues and loaded the result to a pivot dashboard, then wrote a one-page guide for the team. Result: the job took ten minutes, copy-paste errors disappeared and two other teams adopted the same setup.
20
Write a Power Query custom column that classifies orders into High, Medium and Low by amount.
Add Column > Custom Column and enter an M if expression; M is case-sensitive and uses lowercase if, then, else:
if [Amount] >= 100000 then "High"
else if [Amount] >= 25000 then "Medium"
else "Low"
Then set the new column type to Text. The Conditional Column dialog can build the same logic without typing, but writing M is faster for nested or combined conditions using and/or.
Query folding means Power Query translates your steps into the source's own query language, such as SQL, so filtering and grouping happen inside the database and only the result is sent to Excel. This is much faster for large sources. Steps like adding index columns or some custom M functions can break folding. Right-click a step and check View Native Query to see whether it still folds.
22
A monthly process takes four hours of copy-paste from 30 branch files. How would you automate it?
First agree a fixed template with the branches, ideally a named Table. Save all files to one SharePoint or shared folder, then build a From Folder query that combines them, adds the branch name from the file name, standardises types and filters bad rows. Load to the Data Model, build the pivots and dashboard on it, and add a check sheet comparing row counts per branch. Monthly work becomes refresh and review.
23
What causes the Formula.Firewall error in Power Query and how do you resolve it?
The error appears when a query combines data from sources with different privacy levels, for example using a value from an Excel sheet to filter a web or database query, because Power Query prevents data from one source leaking to another. Fix it by setting correct privacy levels in Data Source Settings, restructuring so the combination happens in separate staging queries, or, for trusted personal files, choosing to ignore privacy levels.
24
Refreshing a Power Query that combines 200 CSV files from a folder takes ten minutes. How do you speed it up?
Reduce work as early as possible: remove unneeded columns and filter rows in the sample file transform so every file is smaller. Delete redundant Changed Type steps and set types once. Avoid sorting and merges against large tables before the combine. Set helper queries to connection only. Copy files to a local drive if SharePoint is slow, and archive old years into a single file so only recent files are combined each time.
25
How do you create and use a custom function in Power Query?
Write a query that takes a parameter, then call it for each row or file:
(filePath as text) as table =>
let
Src = Excel.Workbook(File.Contents(filePath)),
Data = Src{[Item="Sales",Kind="Table"]}[Data]
in
Data
Save it as fnGetSales, then Add Column > Invoke Custom Function on a table of file paths and expand the result. This gives more control than the automatic Combine Files helper.
Frequently asked questions
How important is Power Query and Data Cleaning in Advanced Excel interviews?
Power Query imports, cleans and reshapes data with recorded steps that rerun on refresh. Learn folder imports, append and merge, join kinds, unpivot, locale-aware data types, group by, parameters, custom M columns and functions, and query folding. This is the skill that turns hours of monthly copy-paste into a single click.
How should I use the Power Query and Data Cleaning sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.