How I Learned Data Analytics—And How You Can Too. Power BI Series

Load Data, Clean, and Transform in Power BI

Video reference: https://www.youtube.com/watch?v=gP-AxNi6uxo

Get the Software

Download and install Power BI from the official Microsoft website. It's free for personal use and includes all the features you need to get started.

Import and Load Data

Video timestamp: 1:50 - 2:26

Getting your data into Power BI is straightforward:

1. Prepare your data: Download your Excel file and save it in an easily accessible folder
2. Open Power BI Desktop and click "Get Data" → "Excel"
3. Select your file: Power BI will show you all available tables and sheets in your workbook
4. Choose what to import: Select the specific sheets or tables you want (you can select multiple)
5. Click "Load" to bring the data into Power BI

What if your data needs work? Numbers showing as text, messy formatting, or inconsistent data? That's where transformation comes in.

Transform Your Data

Video timestamp: 2:27 onwards

You can clean and reshape your data using Power Query Editor - this is where the real magic happens. Think of it as your data preparation workshop.

Getting to the Power Query Editor

Instead of clicking "Load," click "Transform Data" to open the Power Query Editor. You can also access it later by clicking "Transform Data" in the Home ribbon.

Essential Data Cleaning Tasks

Remove Unwanted Rows (5:13)

Method 1 - Remove by position:

• Go to Home → Remove Rows
• Choose from options like: top rows, bottom rows, alternate rows, duplicates, blank rows, or error rows

Method 2 - Remove by condition (8:00 - 8:59):
Say you want to remove rows where the price column is empty:

1. Click the column header dropdown arrow
2. Select Text Filters → Does not contain
3. Specify your condition (like "null" or blank values)

Set Proper Headers (6:16 - 6:50)

If your first row contains the actual column names:

• Go to Transform → Use First Row as Headers

Pro tip: You can undo any step by going to "Applied Steps" on the right and clicking the X next to any transformation.

Fix Data Types (6:50 - 8:00)

Power BI guesses data types, but it's not always right:

• Click the dropdown arrow next to any column header
• Select the correct data type (Text, Number, Date, etc.)

Warning: Changing text to numbers will cause errors if the text contains non-numeric characters.

Remove Unnecessary Columns (9:00 - 9:22)

To delete columns you don't need:

1. Click the column header
2. Go to Home → Remove Columns
3. Click Remove Columns

Fix Pivot Table Issues (9:22 - 10:35)

If you have data spread across multiple date columns (like Jan, Feb, Mar), you need to unpivot:

1. Highlight the columns you want to unpivot
2. Go to Transform → Unpivot Columns
3. This creates a cleaner structure with one date column and one value column

Rename Columns (10:35 - 11:00)

Simply double-click any column header and type the new name.

Watch Out for Data Type Errors (11:00 - 12:15)

Be careful when changing data types. If you try to convert text like "N/A" to a number, Power BI will show an error. Always check your data first.

Apply Your Changes

Video timestamp: 12:16 - end

When you're finished transforming:

1. Click Home → Close & Apply
2. Your cleaned data will load into Power BI Desktop

Need to make more changes later? Click Transform Data in the Home ribbon to reopen the Power Query Editor.

Quick Summary

1. Get Data → Select your Excel file
2. Transform Data instead of Load (if cleaning is needed)
3. Clean in Power Query Editor: Remove rows/columns, fix headers, set data types
4. Close & Apply to load your clean data
5. Transform Data anytime to make additional changes

The key is taking time to clean your data properly upfront - it makes creating visualizations much easier later!