Cleaning and Transforming Data

Reading Time: 8 Mins | Level: Beginner to Advanced

1. Common Data Cleaning Tasks in Power Query

1. Use First Row as Headers

Raw CSV and Excel files often import with generic column headers (Column1, Column2) while the actual headers sit in row 1.

• Click "Use First Row as Headers" on the Home ribbon.

2. Changing Data Types

Always enforce correct datatypes (e.g. Dates to Date, Revenue to Fixed decimal number, IDs to Text).

• Pro Tip: Always store numerical identifiers (like Zip Codes, Phone Numbers, or Student IDs) as Text, so Power BI does not attempt to sum them up!

3. Unpivoting Columns (Wide to Long)

If months are spread across columns (Jan, Feb, Mar), select the attribute columns and click Transform $ o$ Unpivot Columns.