PQ Backup - Power Query for Excel and Power BI - Excelerator BI

PQ Backup – Power Query for Excel and Power BI

Module 1: Introduction to Power Query

Lesson: Tips for Watching Videos


Course Material
There is no course material for this lesson.

Lesson: Welcome to Power Query for Excel and Power BI

Lesson: Power Query UI Walk Through


Course Material
The sample Adventure Works Flat file can be downloaded from the link below. You can use the sample file to practice the skills covered in the lesson.
sample-adventure-works

Lesson: When to Use Power Query


Course Material
The sample files can be downloaded from the link below. You can use the sample files to practice the skills covered in the lesson.
When to Use Power Query Lesson Files

Module 2: Loading From Data Sources

Lesson: Data Sources Overview


Course Material
There is no course material for this lesson, as the objective is only to get you acquainted with the different data sources that you can work with.

Lesson: Importing Data from the Web


Course Material
Here is the link to the World Cup Soccer Stats referred to in this lesson. You can use the link below, and work yourself following the examples in this lesson.
http://www.fifa.com/fifa-tournaments/statistics-and-records/worldcup/

Lesson: Load Data From File


Course Material
Click on the link given below to download the data files used in this lesson. You can use these data files and complete the exercises yourself if you wish.
Sample Data Files

Lesson: Load From SharePoint


Course Material
There is no course material for this lesson.

Lesson: Load From A Database


Course Material
Here is a link to the Contoso Database I used in this lesson. You can download the database given and work along with the lesson following the examples demonstrated.
https://www.microsoft.com/en-au/download/details.aspx?id=18279
You will need access to an installed version of SQL Server if you want to be able to use this database. You can download SQL Server Express (which is free) and install it from the link below if you are interested. This is a reasonable amount of effort but can be achieved by a competent Windows user.
https://www.microsoft.com/en-au/sql-server/sql-server-editions-express

Lesson: Using Your Queries


Course Material
There is no course material for this lesson.

Module 3: Transforming Data

Lesson: Power Transformations


Course Material
The sample files can be downloaded from the links below. You can use the sample files to practice the skills covered in the lesson.

 

Lesson: Column By Example


Course Material
You can click on the links provided below to follow along with the examples shown in this lesson.

https://en.wikipedia.org/wiki/List_of_cities_proper_by_population

https://www.rottentomatoes.com/top/bestofrt/

 

Lesson: Working with Rows


Course Material
You can use the following files to practice the skills demonstrated.
You can download from Dropbox here.

Lesson: Working with Columns


Course Material
You can download the tables from https://www.rottentomatoes.com/top/bestofrt/ and practice the skills covered in the lesson.

Lesson: Changing Data


Course Material
You can use the link given below to practice the examples shown in this lesson.
Australia’s Population size and growth

Module 4: Foundation Concepts

Lesson: Reusing Queries


Course Material
There is no course material for this lesson.

Lesson: Managing Queries


Course Material
The sample files can be downloaded from the link below. You can use the sample files to practice the skills covered in the lesson.
Adventure Works

Lesson: Joining Queries


Course Material
The data used in the examples in this lesson is available in Joining Queries. You can use the data file to practice the skills covered in the lesson.

Module 5: Power Query Patterns

Lesson: Appending Multiple Files


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Demo Files for Download

Lesson: Appending Multiple Tabs


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Consolidate-Worksheets

Lesson: Creating a Calendar Table from Scratch


Course Material
Start with a blank Excel workbook and follow through the lesson to build your own calendar table. You can download the final version of the one in the video here so you can review it on your PC.

Lesson: Adding Comments to Tabular Data


Course Material
You can download the data files to follow along with the examples in this lesson.
Self Ref Tables course material

Lesson: Parameters and Parameter Tables


Course Material
There is no specific course material for this lesson. You can use the course material from Module 2 – Lesson 3, combining files from multiple CSV files, for this lesson also.

Lesson: Import from PDF


Course Material
You can take any PDF file that is not password protected and that contains tabular data to follow through this lesson. Here is the file used in the video –
Abbreviation List PDF file

Module 6: Tips and Tricks

Lesson: Managing Credentials


Course Material
There is no course material for this lesson.

Lesson: Working with Multiple Models


Course Material
There is no course material for this lesson.

Lesson: Improving and Tidying your Queries


Course Material
There is no specific course material for this lesson. The course material shared in the earlier lessons is used for showing the examples.

Lesson: Managing Changing Columns


Course Material
You can download the data that is used in the illustrations in this lesson from the link given below.
Data Extract.

Lesson: Query Folding


Course Material
There is no specific course material for this lesson. You can use a database on SQL server to follow through the examples.

Module 7: Advanced Concepts

Lesson: An Introduction to Advanced Concepts


Course Material
There is no specific course material for this lesson.

Lesson: M Language Introduction


Course Material
There is no specific course material for this lesson.

Lesson: Relative Columns and Rows


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Consolidate-Worksheets

Lesson: Manual Functions


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Manual Functions Sample Data

Lesson: Automatic Functions


Course Material
Sample data used in this lesson is same as that used in the Manual Functions lesson. You can click on the link provided below to follow along with the examples shown in this lesson.
Manual Functions Sample Data

Lesson: Cross Join


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Cross Join Start

Lesson: Nested Index


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
fruit preferences

Lesson: Error Handling Using the Try Function


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Try sample

Module 8: Tricky Examples

Lesson: Find Previous Record


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Last ticket was only 7 days ago

Lesson: Extract Values


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
extract values sample files

Lesson: Pivot Text Data


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
pivot text data demo

Lesson: Handle Messy Headers


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
MessyHeaders

Lesson: Cleanse Multiple Characters from a Column


Course Material
You can click on the link provided below to follow along with the examples shown in this lesson.
Cleanse multiple characters from a column

Scroll to Top

Power Query can automate boring, repetitive tasks you do every day.  Watch free previews of my online training course to see how.