Power Query
Transforming Data with Power Query
Description: Power Query is the specialized data preparation engine built into Power BI. It acts as an ETL (Extract, Transform, Load) tool that allows you to clean, reshape, and refine your data before it ever reaches your report. Any change you make in Power Query is recorded and can be repeated automatically whenever the data is refreshed.
Why: Raw data is rarely "report-ready." It often contains duplicates, incorrect formatting, or unnecessary columns. Beginners should view Power Query as the "cleaning room"—by fixing data issues here, you ensure that your DAX formulas and visualizations work perfectly without errors.
Common Transformation Tasks
Power Query provides a user-friendly "ribbon" interface to perform complex data manipulations without writing code:
| Transformation | Purpose |
|---|---|
| Remove/Rename | Delete unnecessary columns to reduce file size and rename headers (e.g., "Amt" to "Sales Amount") for clarity. |
| Change Data Types | Ensuring a "Date" column is recognized as a date and "Price" is a decimal number so math operations can be performed. |
| Filter & Remove | Filtering out specific years or removing blank rows and duplicate entries to maintain data integrity. |
| Merge & Append | Merge: Join two tables side-by-side (like a VLOOKUP). Append: Stack tables on top of each other (e.g., combining Sales Jan and Sales Feb). |
| Unpivot | Turning "short and wide" data (months as columns) into "long and thin" data (one column for months, one for values). |
The "Applied Steps" Concept
One of the most powerful features of Power Query is that it does not change your original source file. Instead, it records every click as a sequential Applied Step.
- Automation: When you get new data next month, Power BI simply "replays" these steps to clean the new data automatically.
- The M Language: Behind the scenes, every step you click creates code in a language called M. Advanced users can view and edit this code in the "Advanced Editor" for highly complex logic.
Example: A Standard Cleaning Workflow
Cleaning a Sales Report:
- Remove Blank Rows: Use "Remove Rows" → "Remove Blank Rows" to clean the bottom of the sheet.
- Fix Types: Click the icon next to the "Date" header and select "Date" to ensure proper sorting.
- Standardize: Use "Replace Values" to change "USA" and "United States" into a single uniform name.
- Finalize: Click "Close & Apply" to load this clean version into your Model View.
Key Notes
- Case Sensitivity: Power Query is case-sensitive. "Sales" and "sales" are treated as two different things when filtering or grouping.
- Undo/Redo: There is no "Undo" button in Power Query. Instead, you delete a step from the Applied Steps list by clicking the "X" next to it.
- Data Profiling: Use the "View" tab to turn on "Column Quality" and "Column Distribution." This gives you a visual indicator of how much of your data is valid, empty, or contains errors.
🏋️ Test Yourself With Exercises
Take our quiz on Power Query to test your knowledge.
Browse Quizzes »