Power Query Level 2

Power BI Desktop Visualizing Data

Amazing Transformations!

Course Length: 1 Day
Course Format: Instructor–Led
Course Hours: 9:00 a.m. – 5:00 p.m.

Back to class dates and registration.
Download a PDF of the course outline.

If seeking CPE Credit Hours, completion of this course earns:
8 CPE Credit Hours

Course Description

Course Overview: Reorganizing complex data is a challenge, but building an efficient Power BI solutions requires that we do it anyway! This course is designed to provide students with complex data more insight into the world of Power Query, also known as “M.”

Target Student: This course is for students who know that they will be heavily transforming data before they load it into their data models. This course is also for students who are in the middle of creating a Power BI Solution and realize that transforming their data is more complicated than they originally thought.

Prerequisites: To ensure your success, we recommend you first take the following CompuTrain courses or have equivalent knowledge: Power BI Boot Camp or Power Query - Level 1. Alternately, relational database experience and/or training would be helpful.

Course Objectives

Upon successful completion of this course, students will be able to:

  • Load queries into different destinations
  • Merge tables and queries
  • Create statistical summaries
  • Transpose and unpivot complex data
  • Edit and customize query schedules
  • Build formulas in queries
  • Group queries
Course Content
Lesson 1: Query Loading Destinations
Lesson 2: Merging Tables and Queries
  • One to Many Merges
  • Many to Many Merges
Lesson 3: Grouping and Summarizing
  • Grouping and summarizing hierarchically
  • Creating Summary Statistics
  • Custom Columns
Lesson 4: Transposing and Unpivoting Complex Data
  • Unpivoting Subcategorized Tables
  • Transpose stacked tables
Lesson 5: Schedule Queries
  • Modify Excel’s Query Scheduling
  • Custom scheduling with VBA
Lesson 6: Power Query Formulas
  • Basic Custom Formulas
  • Differences from Excel/DAX
  • Text Functions
  • Date Functions
  • Conditional Logic
Lesson 7: Query Organization
  • Arranging and Grouping Queries