Day 3: Power Query — Data Cleaning

Select the column(s) that should be unique → Home → Remove Rows → Remove Duplicates. If no column is selected, it removes rows where ALL columns match. The step is recorded in Applied Steps as "Removed Duplicates" and re-runs automatically on refresh. Unlike Excel's Remove Duplicates (which modifies data in place and is irreversible), Power Query keeps the source unchanged and re-applies the transformation every time.

Click the data type icon (left of the column header) → choose the correct type from the dropdown. Or: right-click the column header → Change Type. Common types: Whole Number, Decimal Number, Date, Date/Time, Text, True/False. Always set data types explicitly — do not rely on Power Query's auto-detection, especially for date columns (which are often detected as text when imported from CSV). Incorrect data types cause DAX calculations to fail silently.

To replace nulls: right-click the column → Replace Values → in "Values to Find" leave blank (for null) → enter the replacement value. Or: Transform → Replace Values. To remove rows with nulls in a specific column: select the column → Home → Remove Rows → Remove Blank Rows. To filter out nulls: click the column filter dropdown → uncheck "null". For numeric columns: replace null with 0 if zero is meaningful, or use a DAX measure with CALCULATE and ISBLANK to handle dynamically.

Select the column → Transform → Split Column → By Delimiter. Choose the delimiter (comma, space, semicolon, custom) and split position (each occurrence, left-most, right-most). Power Query creates new columns for each part. Use case: a "Full Name" column with "First Last" format → split by space → two columns "Name.1" (first name) and "Name.2" (last name) → rename them. For fixed-width splits: Split Column → By Number of Characters.

"Promoted Headers" is an Applied Step that Power Query adds automatically when it detects that the first row of your data should be the column headers. This happens when connecting to CSV files or Excel ranges where the header row is the first data row. If your data has multiple header rows or metadata rows at the top, you may need to remove top rows first (Home → Remove Rows → Remove Top Rows) before promoting headers. If Power Query promotes headers incorrectly, delete the "Promoted Headers" step and add it manually via Transform → Use First Row as Headers.