Power Query Level 1

Power BI Desktop Visualizing Data

Extract, Transform and Load!

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: Companies gather data - lots of it! And while gathering data is important, analyzing data is even more important! However, efficiently analyzing data requires that our data be organized optimally. Many folks download data and use macros and other tools to rearrange and format their data. But now there is a way to do this easily since Power Query is built-in to Excel! This course will teach you how to transform the way your data currently looks.

Target Student: : If you are in charge of providing the design and structure for your company’s Power BI data model, this class is for you. Learn how to query different data sources and build connections to your data.

Prerequisites: To ensure your success, we recommend you first take the following CompuTrain courses or have equivalent knowledge: Excel 2016 - Intermediate. Alternately, relational database experience and/or training would be helpful.

Course Objectives

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

  • Import files into a data model.
  • Override Power Query defaults.
  • Automatically append data to existing table.
  • Import all files in a folder into the data model.
  • Handle nondelimited text
  • Connect to an SQL database
  • Import data from the web
Course Content
Lesson 1: Introducing Power Query
  • Why Use Power Query/Get Data?
  • Importing CSV files
  • Exploring the Data Model Manager Interface
 Lesson 2: Data Types
  • Overriding Power Query Defaults
  • Data Types
Lesson 3: Combining Files
  • Basic Append
  • Importing All files in a folder
  • Filtering Files
  • Preserving File Properties
Lesson 4: Unpivoting
  • Unpivot Data
  • Unpivot Other Columns
Lesson 5: Combining all worksheets in a workbook
  • Starting from a blank query
  • Reusing Existing Connections to Update the Model
Lesson 6: Nondelimited Text
  • Trim and Clean Text
  • Splitting Columns
  • Combining Columns
Lesson 7: Importing from Databases
  • Connect to SQL database
  • Selecting Data
  • Combining Columns
Lesson 8: Importing Web Data
  • Importing Tables from the Internet
  • Importing Raw Data

Importing text files