Power Query lives in Excel under Data → Get Data and in Power BI. It records every step – delete a column, change a data type, join tables – and repeats it at the push of a button with new data. For controllers it is the biggest time-saver in Excel. Here are the 30 most important features, in the order you typically need them:
Back to the overview: Excel for controllers: the 30 most important functions
Getting data
1. From Table/Range
Data → From Table/Range loads a table from the current file into the Power Query editor. The starting point for anything already in Excel.
2. From Text/CSV
For exports from accounting and ERP systems. Power Query detects delimiter and encoding, but both can be adjusted – important for special characters.
3. From Workbook
Loads tables or sheets from another Excel file without opening it. Ideal for submissions from departments.
4. From Folder
Get Data → From File → From Folder → Combine stacks all files in a folder. Drop next month's file in, and after a refresh it is included automatically.
5. From database and web
Load directly from SQL Server, Access or a web page – for example exchange rates or data from a data warehouse.
Cleaning
6. Use first row as headers
Home → Use First Row as Headers. Nearly every export needs this step.
7. Change data type
Every column gets a type: text, whole number, decimal, date. For numbers with a decimal comma from other systems: Change Type → Using Locale.
8. Remove columns
Prefer Remove Other Columns: only the selected columns stay, and new columns in the export do no harm.
9. Filter rows
Use the filter arrow as in Excel – e.g. only account classes 4–7 or one company code. The filter is reapplied on every refresh.
10. Remove top and blank rows
Reduce Rows → Remove Rows: delete report headers, blank lines and total rows at the bottom.
11. Remove duplicates
Select a column → Remove Duplicates. Gives you a clean customer or cost centre list, for example.
12. Replace values
Right-click → Replace Values: harmonise spelling variants such as “Vienna”, “VIENNA” and “Wien”.
13. Trim, clean, change case
Transform → Format: remove leading and trailing spaces (Trim), invisible characters (Clean) and harmonise case. Fixes most problems with later joins.
14. Split column
Split by delimiter or number of characters, e.g. “4100 Sales” into number and name.
15. Fill down
Transform → Fill → Down fills empty cells with the value above. Saves exports where the cost centre appears only in the first row.
16. Replace or remove errors
Right-click the column → Replace Errors or Remove Errors, so the query does not fail when one row contains nonsense.
Reshaping
17. Unpivot columns
The most important feature for controllers: a table with months as columns becomes a long list with month and amount columns, which can then be analysed cleanly. Select the account column → Unpivot Other Columns.
18. Pivot column
The opposite: a long list becomes a cross table again, e.g. accounts in rows and months in columns.
19. Group by
Sums, counts or averages before loading – e.g. postings by account and month. Keeps the Excel file small.
20. Transpose
Swaps rows and columns. Useful for exports laid out across instead of down.
21. Sort
Sorts by one or more columns. The order of the sort steps sets the priority.
New columns
22. Custom column
Add Column → Custom Column calculates with a formula in the M language.
23. Conditional column
If-then rules in a dialog, no formula: e.g. “if account starts with 4, then revenue, else expense”.
24. Column from examples
Type two or three desired results and Power Query detects the pattern – e.g. the cost centre number inside a long text.
25. Index column
A running number per row. Helps with sorting and keeping the original order.
26. Split dates
Add Column → Date: year, month, quarter or end of month as separate columns. End of month as a formula:
Combining and loading
27. Merge queries
Power Query's VLOOKUP, only faster and without formulas: join postings with cost centre master data. The “Left Outer” join matches VLOOKUP, “Left Anti” shows what is missing from the master data.
28. Append queries
Stacks tables with the same structure, e.g. actuals from three companies.
29. Parameters
Home → Manage Parameters: set the folder path or reporting date as a parameter. If the file moves to another computer, you change one place only.
30. Close & Load To …
Load the result as a table, as a PivotTable, as a connection only or into the Data Model. Intermediate steps as “connection only” keep the file tidy.
Each step is listed on the right under “Applied Steps” and can be renamed, edited or deleted. You see the code behind it under Home → Advanced Editor. The same Power Query is built into Power BI – what you learn in Excel carries over one to one.
Frequently asked questions
Where do I find Power Query in Excel?
Under Data → Get Data. Power Query is built into Excel for Windows from version 2016 and into Microsoft 365. Excel 2010 and 2013 had a free add-in.
Does Power Query change my original data?
No. Power Query only reads the source and saves the result as a new table. The export files stay unchanged.
What does unpivot mean?
A cross table with months as columns becomes a long list with one row per account and month. Pivot tables, Power BI and most formulas need this format.