DAY 15: Excel - Power Query Basics + WEEK 3 TEST

Exercise 1: Import and Clean CSV Data with Power Query

Goal: Learn to import messy CSV data into Excel using Power Query and apply automated data cleaning transformations.

Setup: Download or create a sample CSV file with customer data that includes duplicates, extra spaces, inconsistent capitalization, and missing values.

Step-by-Step Instructions:

1. Open Excel and create a new blank workbook
2. Access Power Query: Navigate to the Data tab on the ribbon
3. Import CSV file: Click Get Data → From File → From Text/CSV
4. Select your CSV file and click Import. Excel will show a preview of the data
5. Click "Transform Data" instead of "Load" - this opens the Power Query Editor (keyboard shortcut: Alt → A → F → T in sequence)
6. Observe the Power Query Editor interface: Notice the ribbon with transformation options, the query pane on the left, and the "Applied Steps" pane on the right showing each transformation
7. Remove duplicates: Select the CustomerID column, then click Home → Remove Rows → Remove Duplicates
8. Trim extra spaces: Select the Name column, right-click column header → Transform → Trim (removes leading/trailing spaces)
9. Fix capitalization: While Name column is still selected, right-click → Transform → Capitalize Each Word
10. Replace null values: Select the Phone column, click Transform tab → Replace Values, enter null in "Value To Find" and Not Provided in "Replace With"
11. Change data types: Select the CustomerID column, click the data type icon (ABC/123) in the column header → choose Whole Number
12. Rename columns for clarity: Double-click the "CustomerID" column header and rename to "Customer ID" (with space)
13. Review Applied Steps: Look at the right pane to see each transformation listed - you can click any step to see the data at that point
14. Close and Load: Click Home → Close & Load (or Alt → H → C → L) to load the cleaned data into Excel
15. Verify the results: Check that your data is now clean with no duplicates, proper capitalization, and handled null values

💡 Tips:

• Non-destructive editing: Power Query doesn't change your source file - all transformations are stored as steps you can modify later
• Refreshable transformations: If your source CSV updates, just right-click the table → Refresh to reapply all cleaning steps automatically
• Applied Steps are editable: Click the gear icon next to any step in "Applied Steps" to modify or delete it
• Keyboard shortcut: Press Ctrl+Z in Power Query Editor to undo the last transformation
• Column operations shortcuts: Right-click any column header for quick access to common transformations
• Formula bar: Advanced users can see and edit the M code (Power Query language) behind each step using the formula bar

✅ Expected Result: A clean Excel table with no duplicates, trimmed text, proper capitalization, handled null values, and correct data types. The table has a connection to the source CSV that can be refreshed anytime.

🔄 Connection to Day 13: Just like Day 13's What-If Analysis automated scenario testing, Power Query automates data cleaning. Instead of manually cleaning data repeatedly, you create a reusable transformation workflow that adapts to new data!

Exercise 2: Transform and Reshape Data with Column Operations

Goal: Master Power Query's column transformation features including splitting, merging, extracting, and creating custom columns.

Setup: Use a dataset with a "FullName" column (e.g., "John Smith") and an "OrderDate" column that needs to be split into components.

Step-by-Step Instructions:

1. Open Power Query Editor: From the Data tab, click Get Data → From File → From Excel Workbook (or use your existing query)
2. Click "Transform Data" to open the Power Query Editor
3. Split column by delimiter: Select the FullName column → Home → Split Column → By Delimiter
4. Configure split: Choose "Space" as delimiter, select "At the leftmost delimiter", click OK
5. Rename split columns: Rename "FullName.1" to "First Name" and "FullName.2" to "Last Name"
6. Extract date components: Select the OrderDate column (ensure it's Date type) → click Add Column tab
7. Extract year: With OrderDate selected, click Date → Year (creates new column with year values)
8. Extract month name: Select OrderDate again → Add Column → Date → Month → Name of Month
9. Extract day of week: Select OrderDate again → Add Column → Date → Day → Name of Day
10. Create custom column with calculation: Click Add Column → Custom Column
11. Configure custom column: Name it "Days Since Order", enter formula: [OrderDate] - DateTime.LocalNow(), change type to Duration
12. Merge columns: Select First Name and Last Name columns (hold Ctrl while clicking) → Transform → Merge Columns
13. Configure merge: Choose "Space" as separator, name the new column "Full Name", click OK
14. Create conditional column: Click Add Column → Conditional Column
15. Configure condition: Name: "Order Age Category", If "Year" equals current year, Output: "This Year", Else: "Previous Years"
16. Reorder columns: Drag column headers to arrange them logically (Customer ID, Full Name, OrderDate, Year, Month, etc.)
17. Close & Load: Click Home → Close & Load to see your transformed data

💡 Tips:

• Add vs Transform: Add Column creates new columns (keeps original), Transform modifies existing columns (replaces original)
• Custom column power: Use M language in custom columns for complex calculations - similar to DAX formulas but for data transformation
• Date intelligence: Power Query has extensive date functions - explore Add Column → Date for age, quarter, week number, etc.
• Conditional logic: Conditional Column is great for simple IF logic; use Custom Column for complex nested conditions
• Column selection: Hold Ctrl to select multiple non-adjacent columns, Shift to select a range
• Keyboard shortcut: Use Alt + A + M to open the "Merge Columns" dialog quickly

✅ Expected Result: A transformed dataset with split name columns, extracted date components (Year, Month Name, Day Name), merged full names, calculated days since order, and conditional categorization - all created without formulas in the Excel grid!

🔄 Connection to Day 11: Day 11's date functions (TODAY, DATEDIFF, EOMONTH) work on individual cells. Power Query applies these transformations to entire columns at once during data import, creating a more efficient, automated workflow!

Exercise 3: Merge Queries to Combine Customer and Order Data

Goal: Master Power Query's Merge feature to combine data from multiple tables, similar to SQL JOINs.

Setup: You need two Excel tables or CSV files - one with customer information (CustomerID, Name, Email) and another with orders (OrderID, CustomerID, OrderDate, Amount).

Step-by-Step Instructions:

1. Import first table: Go to Data → Get Data → From File → select your Customers file
2. Load to Power Query: Click Transform Data to open in Power Query Editor
3. Rename the query: In the Query Settings pane (right side), change the name from "Sheet1" to "Customers"
4. Clean the data: Apply any necessary transformations (remove duplicates on CustomerID, trim spaces, etc.)
5. Click "Close & Load To": Click the dropdown arrow next to "Close & Load" → Close & Load To...
6. Select "Only Create Connection": This loads the query but doesn't add it to a worksheet yet - click OK
7. Import second table: Go to Data → Get Data → From File → select your Orders file
8. Load and rename: Click Transform Data, rename the query to "Orders"
9. Start the merge: With Orders query open, click Home → Merge Queries (not "Merge Queries as New")
10. Configure the merge: - Top dropdown: Select "Orders" (should already be selected) - Click the CustomerID column in the Orders preview - Bottom dropdown: Select "Customers" - Click the CustomerID column in the Customers preview - Join Kind: Select "Left Outer (all from first, matching from second)" - Click OK
11. Expand the merged column: A new column appears named "Customers" with "Table" values. Click the expand icon (two arrows) in the column header
12. Select columns to expand: Uncheck "Use original column name as prefix", select "Name" and "Email", click OK
13. Review the results: You now have order data enriched with customer names and emails from the Customers table
14. Rename expanded columns: Rename "Name" to "Customer Name" and "Email" to "Customer Email" for clarity
15. Close & Load: Click Home → Close & Load to see the merged data in Excel
16. Test refresh: Try updating the source data and right-click the table → Refresh to see changes propagate

💡 Tips:

• Join types explained: Left Outer (all orders + matching customers), Inner (only orders with customers), Full Outer (all from both), Right Outer (all customers + matching orders)
• Merge vs Append: Use Merge to add columns (like SQL JOIN), use Append to add rows (like UNION)
• Multiple column merge: Hold Ctrl and select multiple columns in each table to merge on composite keys
• Merge as New: Use "Merge Queries as New" to create a third query without modifying the original - great for creating multiple views
• Performance tip: Merge queries load faster than VLOOKUP formulas for large datasets - Power Query is optimized for data operations
• Fuzzy matching: Power Query can do fuzzy matching for approximate joins - useful when data isn't perfectly clean

✅ Expected Result: A single table showing all orders with enriched customer information (Name, Email) pulled from the Customers table, similar to a SQL LEFT JOIN. Changes to source data are reflected when you refresh the query.

🔄 Connection to Day 6: Day 6 taught SQL JOINs to combine tables in databases. Power Query's Merge does the same thing in Excel, letting you work with relational data without SQL - perfect for combining data from multiple sources!

Exercise 4: Append Multiple Data Sources into One Table

Goal: Learn to combine multiple tables with the same structure into a single consolidated table using Append.

Setup: Create or obtain three separate files with the same column structure - for example, sales data from Q1, Q2, and Q3 in separate CSV files or Excel sheets.

Step-by-Step Instructions:

1. Import first dataset: Go to Data → Get Data → From File → select Q1_Sales.csv
2. Transform and rename: Click Transform Data, rename query to "Q1_Sales"
3. Add a source column: Click Add Column → Custom Column, name it "Quarter", value: "Q1", click OK
4. Load as connection only: Click Close & Load To → Only Create Connection
5. Import second dataset: Data → Get Data → From File → select Q2_Sales.csv
6. Transform and add identifier: Click Transform Data, rename to "Q2_Sales", add Custom Column "Quarter" with value "Q2"
7. Load as connection only: Close & Load To → Only Create Connection
8. Import third dataset: Repeat for Q3_Sales.csv, adding Quarter column with value "Q3"
9. Create new Append query: Go to Data → Get Data → Combine Queries → Append
10. Choose "Three or more tables": Select this option (even though we have exactly 3), click OK
11. Select tables to append: In "Available tables", select Q1_Sales, click Add, repeat for Q2_Sales and Q3_Sales
12. Review table order: The order in "Tables to append" determines row order in final result - reorder if needed using arrows
13. Click OK: Power Query creates a new query combining all three tables
14. Rename the combined query: Change name from "Append1" to "All_Sales_2024"
15. Add year column: Click Add Column → Custom Column, name: "Year", value: 2024
16. Sort by date: Select the Date column → Home → Sort Ascending
17. Group by quarter: Click Transform → Group By, Group by: Quarter, New column name: "Total Sales", Operation: Sum, Column: Amount
18. Undo grouping to see details: Click the X next to "Grouped Rows" in Applied Steps (we just wanted to see how grouping works)
19. Close & Load: Click Home → Close & Load to load the consolidated data

💡 Tips:

• Append vs Merge reminder: Append stacks tables vertically (combines rows), Merge joins tables horizontally (combines columns)
• Column matching: Append matches columns by name - ensure column headers are identical across all tables
• Handling mismatched columns: If tables have different columns, Append includes all columns and fills missing values with null
• Folder append: Use Get Data → From Folder to automatically append all files in a directory - great for monthly reports!
• Append as New: Use "Append Queries as New" to create a separate combined query without modifying originals
• Performance advantage: Appending in Power Query is much faster than copying/pasting data from multiple files manually

✅ Expected Result: A single consolidated table containing all sales records from Q1, Q2, and Q3, with each row tagged with its source quarter. The table automatically updates when source files change and you refresh the query.

🔄 Connection to Day 8: Day 8 used SQL GROUP BY to aggregate data. Power Query's Group By does the same thing, and when combined with Append, you can consolidate and analyze data from multiple sources in one operation!

Exercise 5: Create Reusable Data Transformation Workflows

Goal: Build a complete end-to-end Power Query workflow that imports, cleans, transforms, and combines data - creating a reusable, production-ready data pipeline.

Setup: Use multiple data sources - a CSV file with sales data (messy), an Excel file with product information, and another Excel file with customer data.

Step-by-Step Instructions:

1. Import and clean sales data: - Data → Get Data → From File → From Text/CSV → select sales file - Click Transform Data, rename query to "Sales_Raw" - Remove duplicates on OrderID column - Trim and capitalize all text columns - Change OrderDate to Date type, Amount to Currency type - Filter out rows where Amount is null or zero - Close & Load To → Only Create Connection
2. Import product reference data: - Data → Get Data → From File → From Excel Workbook → select products file - Click Transform Data, rename to "Products" - Remove any unnecessary columns - Ensure ProductID is Whole Number type - Close & Load To → Only Create Connection
3. Import customer reference data: - Data → Get Data → From File → From Excel Workbook → select customers file - Click Transform Data, rename to "Customers" - Remove duplicates on CustomerID - Clean email addresses (trim, lowercase) - Close & Load To → Only Create Connection
4. Create integrated sales report query: - Right-click "Sales_Raw" in Queries pane → Reference (this creates a new query based on Sales_Raw) - Rename the new query to "Sales_Report"
5. Merge with Products: - Click Home → Merge Queries - Select ProductID in Sales_Report, select Products table, select ProductID, use Left Outer join - Expand to include Product Name and Category
6. Merge with Customers: - Click Home → Merge Queries again - Select CustomerID in current query, select Customers table, select CustomerID, use Left Outer join - Expand to include Customer Name and Email
7. Add calculated columns: - Add Column → Custom Column, name: "Year", formula: Date.Year([OrderDate]) - Add Column → Custom Column, name: "Month", formula: Date.MonthName([OrderDate]) - Add Column → Custom Column, name: "Quarter", formula: "Q" & Text.From(Date.QuarterOfYear([OrderDate]))
8. Add revenue category: - Add Column → Conditional Column - Name: "Revenue Category" - If Amount is greater than or equal to 1000, Output: "High Value" - Else if Amount is greater than or equal to 500, Output: "Medium Value" - Else: "Low Value"
9. Reorder and clean up columns: - Arrange columns logically: OrderID, OrderDate, Year, Month, Quarter, CustomerID, Customer Name, Email, ProductID, Product Name, Category, Amount, Revenue Category - Remove any intermediate columns you don't need
10. Add documentation: - Right-click the query → Properties - Add description: "Integrated sales report combining sales, product, and customer data with enrichments"
11. Close & Load: Click Home → Close & Load to create the final report table
12. Test the workflow: - Modify one of the source files (add a row, change a value) - Right-click the Sales_Report table → Refresh - Verify that changes propagate through the entire workflow
13. View query dependencies: Click Data → Queries & Connections → Query Dependencies to see how queries relate

💡 Tips:

• Reference vs Duplicate: "Reference" creates a query that depends on another (efficient), "Duplicate" creates an independent copy (flexible)
• Query organization: Group related queries into folders in the Queries pane for better organization in complex workbooks
• Parameter queries: Create parameter queries to make your workflow dynamic (e.g., date ranges, file paths) - accessible via Manage Parameters
• Error handling: Use Keep Errors or Remove Errors to handle data quality issues gracefully in production workflows
• Performance optimization: Disable "Enable load" for intermediate queries (only load final results to Excel) to improve workbook performance
• Sharing workflows: Power Query transformations are saved in the Excel file - when you share the workbook, others can refresh your queries automatically

✅ Expected Result: A comprehensive, production-ready sales report that automatically imports data from three sources, cleans and validates it, enriches it with product and customer information, adds calculated fields and categorizations, and can be refreshed with one click to reflect updated source data. This workflow is fully documented and reusable.

🔄 Connection to Day 10: Day 10 introduced SQL subqueries for complex data retrieval. Power Query workflows achieve similar complexity by chaining transformations and combining queries, but with a visual, user-friendly interface. Both approaches build layered data logic - SQL uses nested queries, Power Query uses referenced queries!