Excel: Power Query (Get & Transform)

Introduction

• Make your data useful with Power Query

1. What Is Power Query (Get & Transform)?

• Power Query example
• Differences between Excel and Power Query

2. Working with Queries

• Data types explained
• Query data from a table or range
• Query data from another Excel file
• Load data only as a connection

3. Working with Columns

• Fill up and fill down
• Split column by delimiter
• Split into rows
• Add conditional and custom columns
• Add column by example
• Merge columns
• Sort and filter data in Power Query

4. Working with Formulas

• Use IF formulas
• Nest IF and AND
• AddDays to determine the deadline

5. Pivoting and Unpivoting Data

• Pivot data in Power Query
• Pivot and append data
• Pivot and don't aggregate
• Unpivot data in Power Query
• Unpivot warnings

6. Grouping

• Group By feature

7. Appending Queries

• Two data sets
• Multiple tables
• Query data from a folder and import multiple files
• Append multiple sheets

8. Midterm Challenge

• Midterm challenge: Get a count of all colors

9. Merging Data with Joins

• Overview of joins in Power Query
• Walk through all six joins
• Joins: Left or right
• Outer join versus XLOOKUP
• Merge with multiple fields
• Approximate match equivalent of VLOOKUP: Binning
• Approximate match equivalent of VLOOKUP: Conditional column
• Cross Join

10. Drill Down to Create Variables

• Drill down to create a variable in Power Query

11. Fuzzy Matching

• Fuzzy matching by percentage
• Merging inconsistent data with a transformation table

12. Apply Your Learning with Real-World Challenges

• Challenge setup
• Real-world challenge 1: Projects
• Real-world challenge 2: Donors
• Real-world challenge 3: Soccer team

Conclusion

• Next steps