Power Query Basics

What You'll Learn

• What Power Query is and why it matters
• Navigate the Power Query Editor
• Basic transformation operations
• Apply and close queries

What is Power Query?

Power Query is Power BI's built-in data cleaning and transformation tool.

Think of it as a powerful Excel on steroids for preparing data.

Why Use Power Query?

• Clean messy data
• Combine multiple sources
• Remove duplicates
• Split/merge columns
• Filter rows
• Change data types

Opening Power Query Editor

From Get Data: When importing data, click "Transform Data" instead of "Load"

From Power BI: Home > Transform Data

This opens the Power Query Editor window!

Power Query Interface

Top Section: Ribbon

Contains all transformation commands:

Home Tab:

• Remove rows/columns
• Keep/remove duplicates
• Split columns
• Change data types

Transform Tab:

• Text transformations
• Number operations
• Date/time functions

Add Column Tab:

• Create new columns
• Custom columns
• Conditional columns

Left: Queries Pane

Shows all your queries (data tables)

Center: Data Preview

See your data and transformations in real-time

Right: Query Settings

Properties: Query name

Applied Steps: Every transformation you make appears here! Click any step to see data at that point Delete steps by clicking X

Basic Transformations

Remove Columns

Remove unwanted columns:

1. Select column header
2. Right-click
3. Choose "Remove" or "Remove Other Columns"

Filter Rows

Keep only rows you need:

1. Click dropdown on column header
2. Uncheck values to exclude
3. Click OK

Number filters:

• Greater than
• Less than
• Between

Text filters:

• Contains
• Begins with
• Equals

Change Data Type

Fix data types:

1. Click icon next to column name
2. Select correct type:
   • Text
   • Whole Number
   • Decimal Number
   • Date
   • True/False

Power BI auto-detects but might be wrong!

Rename Columns

Make names clearer:

1. Double-click column header
2. Type new name
3. Press Enter

Or right-click > Rename

Remove Duplicates

Clean duplicate rows:

1. Select column(s)
2. Home > Remove Duplicates

Based on selected columns only!

Remove Top/Bottom Rows

Remove header rows or footers:

1. Home > Remove Rows
2. Choose:
   • Remove Top Rows
   • Remove Bottom Rows
   • Remove Alternate Rows

Split Columns

Split "First Last" into two columns:

1. Select column
2. Home > Split Column
3. Choose delimiter:
   • By Delimiter (comma, space, etc.)
   • By Number of Characters
   • By Positions

Merge Columns

Combine "First" and "Last" into "Full Name":

1. Select columns (hold Ctrl)
2. Transform > Merge Columns
3. Choose separator (space, comma, etc.)
4. Name new column

Applied Steps

Every action creates a step!

Step Example:

1. Remove column > "Removed Columns"
2. Filter rows > "Filtered Rows"
3. Change type > "Changed Type"

Why This Matters:

• See transformation history
• Edit/delete steps later
• Steps run in order!

Reorder Steps: Drag steps up/down (be careful!)

Close & Apply

When done transforming:

Home > Close & Apply

This:

1. Saves transformations
2. Closes Power Query
3. Loads data into Power BI
4. You're back in Report View!

Refresh Data

When source data changes:

Home > Refresh

Power Query re-runs all transformations automatically!

Try This Exercise

Clean Sample Data:

1. Create Excel with messy data:
   • Extra header rows
   • Wrong data types
   • Duplicate entries
   • Unwanted columns
2. Import to Power BI with "Transform Data"
3. In Power Query:
   • Remove top rows
   • Delete unwanted columns
   • Change data types
   • Remove duplicates
4. Close & Apply
5. Verify clean data in Power BI!

Common Mistakes

Changing order of steps: Later steps depend on earlier ones!

Not renaming queries: Use clear names like "Sales_Clean" not "Query1"

Loading too much data: Filter early to improve performance

Best Practices

Always use Transform Data: Don't import messy data directly

Name your steps: Right-click step > Rename for clarity

Document with notes: Right-click query > Properties > add description

Test incrementally: Apply one transformation, check result, continue

Next Steps

Now let's dive deeper into specific data transformations!

Tip: Power Query is forgiving. If you mess up, just delete the step and try again!