Excel Power Query and Power Pivot
About This Course
This course equips participants with the skills to leverage Excel Power Query and Power Pivot for advanced data analysis. Starting with Power Query, learners will explore various data types, perform queries from different sources, and manipulate data using techniques like fill up/down, split columns, merge columns, and sort/filter data. These foundational skills enable uncovering trends and patterns effectively in diverse datasets.
The course progresses to complex analyses using Power Pivot, teaching participants to use IF formulas, nest logical functions, and utilize advanced features like Pivot, Group By, and Append Queries. Advanced Power Query topics such as joins, fuzzy matching, and the use of transformation tables will also be covered, providing learners with the tools to address business issues through robust data analysis.
What You'll Learn
LO1: Review data sets using Excel Power Query to uncover trends and patterns.
LO2: Develop methods in Power Pivot to conduct complex analyses tailored to specific datasets.
LO3: Apply advanced Power Query analytics to examine business issues.
Course Outline:
Topic 1. Review Data with Power Query
Data types explained
Query data from a table or range
Query data from another Excel file
Load data only as a connection
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
Topic 2. Data Analysis with Power Query and Power Pivot
Use IF formulas
Nest IF and AND
AddDays to determine the deadline
Pivot data in Power Query
Pivot and append data
Pivot and don't aggregate
Unpivot data in Power Query
Unpivot warnings
Group By feature
Appending Queries
Topic 3. Advanced Data Analysis using Power Query
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
Drill down to create a variable in Power Query
Fuzzy matching by percentage
Merging inconsistent data with a transformation table
Entry Requirements
Knowledge and Skills
• Able to operate using computer functions
• Minimum 3 GCE ‘O’ Levels Passes including English or WPL Level 5 (Average of Reading, Listening, Speaking & Writing Scores)
Attitude
• Positive Learning Attitude
• Enthusiastic Learner
Experience
• Minimum of 1 year of working experience
Target Age Group: 21-65 years old