Power Query for Excel and Power BI Online Training

Power Query is a revolutionary tool to help Excel and Power BI Professionals get easy access to the data they need to do their job.  Power Query is easy to learn, and you can super charge your learning experience with this Online Training course specifically targeted to you.

Introduction and Overview


The course has been developed by Matt Allington (Microsoft MVP and Author of the book “Learn to Write DAX”).  The course contains more than 40 video lessons with a total duration of 7.5 Hrs.

Free Preview – Tips for Watching Videos

Free Preview – When to Use Power Query?

Why Should I Purchase?

  • It has been designed and created Matt Allington (Microsoft MVP).
  • Matt is a true Power BI/Power Pivot/Power Query professional who understands business users AND the technology that can solve these problems. i.e. he understands you and your problems.
  • The training is all “example based” using relevant real live scenarios to teach you the tricks.
  • All the examples in the course are available to download so you can do the exercises too.  This will greatly assist your learning.
  • It is great value at US$250 for 12 months access to the online materials.
  • Buy this and any Learn To Write DAX Online Training at the same time and automatically get 10% off the price of those products!

Course Content

Course Duration – 7.5 Hrs.

Module 1: Introduction to Power Query - 42 minutes
Lesson Key Topics Covered
Tips for Watching Videos
5 minutes
Structure of each lesson
Watching the video on the website
Pausing and doing some practice
Finding the section a second time
Watching the videos faster
Welcome to Power Query for Excel and Power BI
3 minutes
Intro to training and trainer
Power Query UI Walk Through
13 minutes
Versions of Power Query
Where to download
Ribbon and button overview
Formula bar
Advanced editor
Loading data from Query Editor
Re-editing workbook queries
When to Use Power Query?
21 minutes
Power BI Desktop
Shape your data
Cleanse your data
Eliminate repetitive processes
Auditing information
Module 2: Loading From Data Sources - 89 minutes
Lesson Key Topics Covered
Data Sources Overview
4 minutes
Get & Transform commands in Excel 2016
Various data sources for importing data
Office 365 user options
Data Source Settings
Query Options
Creating a New Query – From Table
Close & Load from Query Editor
Show Queries Recent Sources
Importing Data from the Web
12 minutes
Finding the data you need
Split Columns
Copying Queries to Power BI Desktop
Refresh the data
Load Data From File
19 minutes
Importing data from a single CSV file
Importing data from an Excel file
Unpivoting columns
Importing multiple sheets from an Excel workbook
Combining multiple CSV files
Load From SharePoint
21 minutes
Connecting to SharePoint
Differences between Excel and Power BI Desktop
Managing Credentials
Importing from lists
Importing from document libraries
Nested tables of content
Data formatting in Power Query
Load From A Database
16 minutes
Connecting to a DB (SQL Server)
Select Related Tables
Naming Conventions
Joining Tables
Query Folding
Native SQL Queries
Using Your Queries
17 minutes
Do not Load – reuse in other queries
Power Pivot/Power BI
Power Pivot for Excel 2010
Excel Tables
Direct load to Pivot Tables
Module 3: Transforming Data - 72 minutes
Lesson Key Topics Covered
Power Transformations
16 minutes
Unpivot data
Pivot data
Temporary unpivoting
Pivot text data
Working with Rows
12 minutes
Use First Row as Header
Removing Top and Bottom Rows
Filtering Rows
Working with Duplicates
Pause to Check your Satisfaction You must be happy with the course and ready to proceed with other lessons.Just in case you are not you can stop here and ask for your money back.
Working with Columns
22 minutes
Removing
Splitting
Extracting
Merging
Duplicating
Numerical Manipulation
Index Columns
Conditional Columns
Changing Data
16 minutes
Transpose your data
Reordering columns
Removing columns
Changing row order
Reverse ordering
Column By Example
6 minutes
Example to demonstrate how Column From Example works
Example to show that Column From Example might fail in certain cases
Module 4: Foundation Concepts - 33 minutes
Lesson Key Topics Covered
Reusing Queries
10 minutes
Disabling load
New query from reference
Copying queries
New queries from merge
Managing Queries
8 minutes
Reusing standard queries
Copying queries between applications
Using query folders
Query dependency view
Joining Queries
15 minutes
Append
Merge
Join Types
Grouping and Summarizing
Module 5: Power Query Patterns - 92 minutes
Lesson Key Topics Covered
Appending Multiple Files
19 minutes
Combining .CSV files
Combining Excel Files
Converting Queries to Functions
Differences between Power BI and Excel
Appending Multiple Tabs
13 minutes
Importing multiple sheets
Combining multiple sheets
Special trick to rename columns with a relative reference
Creating a Calendar Table
27 minutes
Used for Power Pivot and Power BI
Start from a blank query
Create a list of dates
Set the following editable fields
– Start
– Today
– Duration
Build out the Calendar
Copy and paste for reuse
Comment against Data
8 minutes
Data used in the lesson
Download Bank Statements
Combine into a table
Add comments in Excel You can’t easily add a comments table to an Excel Power Query table
Make the comments stick via self-referencing tables
Parameters and Parameter Tables
17 minutes
Parameters in Power BI Desktop
Parameter Tables in Excel
Import from PDF
8 minutes
Converting PDF file to HTML (.mht) file in Microsoft Word
Loading HTML file into Power BI Desktop
Combining Two Sets of Columns to a Single Set of Columns
Module 6: Tips & Tricks - 38 minutes
Lesson Key Topics Covered
Managing Credentials
8 minutes
Managing Credentials
Privacy Levels
A Common Error and Solution for it
Working with Multiple Models
6 minutes
Cut and Paste Code
Cut and Paste Query
Organizational Data Catalog
Open a Second Instance of Excel
Improving and Tidying your Queries
9 minutes
Rename important query steps
Removing spaces from query step names
Delete all but the last reorder column step
Grouping queries in folders
Refer to a step in another query
Managing Changing Columns
9 minutes
Tip to fix when additional columns not required in the data model appear in the data
Tip to fix when a column in the data model goes missing in the data
Query Folding
6 minutes
Example to show Query Folding
Example to show where Query Folding does not work
Module 7: Advanced Concepts - 52 minutes
Lesson Key Topics Covered
An Introduction to Advanced Concepts
1 minutes
Introduction to the advanced topics covered in this module.
M Language Introduction
6 minutes
M language code for query steps
Advanced editor
shared operator to get a list of M functions
Converting list to table
Creating a list
M language online documentation
Useful information on M language on my site
Relative Columns and Rows
8 minutes
An example to show where relative reference is necessary
Demo of using relative reference
Manual Functions
11 minutes
Create a Query
Give a good name to the function
Edit the code in Advanced Editor
Automatic Functions
9 minutes
Combine & Edit feature
Folders and files created in the Queries List panel
Sample Transformation and the M function
Parameter file and Sample file
Editing Sample Transformation
Cross Join
5 minutes
Demo to show how to Cross Join two tables
Nested Index
7 minutes
Demo to show how to Cross Join two tables
Error Handling Using the Try Function
5 minutes
Using #Shared to get a list of M Functions and their Descriptions
Demo of an error occurrence using Number.From Function
Demo of the usage of Try Keyword
Demo of the usage of Otherwise Keyword
Module 8: Tricky Examples - 32 minutes
Lesson Key Topics Covered
Find Previous Record
9 minutes
Step 1: Sort the data in the correct order
Step 2: Add an Index Column
Step 3: Add an Index Column that indicates the previous row
Step 4: Join the table to itself using Internal Join
Step 5: Write a custom formula that compares opening and closing dates
Extract Values
5 minutes
Using Combine & Edit
Extracting a Value using UI
Combining the Values into a Table
Pivot Text Data
4 minutes
Demo to show why pivot text data does not work
Demo of the trick to pivot text data
Manage Messy Headers
5 minutes
Sort out the first two columns
Transpose the table
Sort out the Headers
Cleanse Dirty Data
9 minutes
Excel table for Cleanse data
Loading multiple tables on an Excel worksheet to Power Query
Ensuring to get the data from the right table
Using M function Text.Remove
Demo of modifying Excel Cleanse table to reflect results in the data table

Additional Goodies

  • Course Material at the end of each lesson to follow through the examples given in the lesson.
  • Quiz at the end of each lesson to reinforce the concepts covered in the lesson.
  • Self-paced training with timestamps provided for all the topics in every lesson to go through any specific section anytime.

Money Back Guarantee

Watch the first 12 lessons in the first 30 days. If you are not totally happy, simply contact Matt via the “contact the teacher” form to receive your money back. Any feedback is greatly appreciated but not required.

Ready to purchase? You can see the purchase online from my shop here and start learning immediately.

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x