Power Query Content:
Video/Class
Project 03:
Video to watch:
MSPTDA 03: Power Query Introduction: Importing & Transformation Data in Excel &
Power BI Desktop
( 57:12 min)
Start files for Video/Class Project to download:
003-MSPTDA-IntroToPowerQueryStartFile.xlsx
MSPTDA-003-AccessData.accdb
MSPTDA-003-TextFiles.zip
Finished files (completed after video done):
003-MSPTDA-IntroToPowerQueryFinishedFile.xlsx
MSPTDA-003-PowerBITextImportFinished.pbix
pdf Notes Handout to download: 003-MSPTDA-IntroToPowerQuery.pdf
Assigned Homework:
Download Word Document and read:
MSPTDA-003-HomeworkDescription.docx
Then download the rest of the files and complete the
homework:
MSPTDA-003-ImportTextFilesForHomework.zip
Examples of Finished Homework:
MSPTDA-003-FinishedHomework.xlsx
MSPTDA-003-FinishedHomework.pbix
Video/Class
Project 04:
Video to watch:
MSPTDA 04: Power Query: Import Multiple Excel
Files & Combine (Append) into Proper Data Set
( 32:02 min)
Start files for Video/Class Project to download:
004-MSPTDA-StartFile-PowerQueryImportExcelFilesAppend.xlsx
004-MSPTDA-ExcelFiles.zip
SanJose.xlsx
Finished files (completed after video done):
004-MSPTDA-FinishedFile-PowerQueryImportExcelFilesAppend.xlsx
pdf Notes Handout to download: 004-MSPTDA-PowerQueryImportMultipleExcelFilesAppend.pdf
Assigned Homework:
Download Word Document and read:
004-MSPTDA-HomeworkDescription.docx
Then download the rest of the files and complete the
homework:
004-MSPTDA-Homework-ExcelFiles.zip
Examples of Finished Homework:
004-MSPTDA-Homework-PowerPivot-Answer.xlsx
004-MSPTDA-Homework-PowerBI-Desktop-Answer.pbix
Video/Class
Project 05:
Video to watch:
MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel
Tables in Excel Workbook ( 27:44 min)
Start files for Video/Class Project to download:
005-01-MSPTDA-StartFile-PQAppendAllTablesCurrentWorkbook.xlsx
005-02-MSPTDA-StartFile-PQAppendAllTablesCurrentWorkbook.xlsx
Finished files (completed after video done):
005-01-MSPTDA-FinishedFile-PQAppendAllTablesCurrentWorkbook.xlsx
005-02-MSPTDA-FinishedFile-PQAppendAllTablesCurrentWorkbook.xlsx
pdf Notes Handout to download: 005-MSPTDA-PQAppendAllTablesCurrentWorkbook.pdf
Assigned Homework:
Download Excel File with Homework:
005-MSPTDA-Homework-Start.xlsx
Example of Finished Homework:
005-MSPTDA-Homework-Finished.xlsx
Video/Class
Project 06:
Video to watch:
MSPTDA 06: Power Query: Merge, Append, &
UnPivot – 3 Important Transformations ( 23:38 min)
Start files for Video/Class Project to download:
006-MSPTDA-StartFilePowerQueryMergeAppendUnPivot.xlsx
006-MSPTDA-dSalesRepTable.xlsx
006-MSPTDA-dTerritoryTable.txt
Finished files (completed after video done):
006-MSPTDA-FinishedFilePowerQueryMergeAppendUnPivot.xlsx
pdf Notes Handout to download: 006-MSPTDA-PowerQueryImportCurrentWorkbook.pdf
Assigned Homework:
Download Excel File with Homework:
006-MSPTDA-Homework-Start.xlsx
Download Files to import for Merge Homework:
006-MSPTDA-HomeworkExcelSalesTable.xlsx
006-MSPTDA-HomeworkTextdProductTable.txt
Example of Finished Homework:
006-MSPTDA-Homework-Finished.xlsx
Video/Class
Project 07:
Video to watch:
MSPTDA 07: Power Query: 6 Types of Joins, 6
Types of Merges: 9 Examples ( 37:38 min)
Start files for Video/Class Project to download: 007-MSPTDA-PowerQueryJoinsMergesStart.xlsx
Finished files (completed after video done):
007-MSPTDA-PowerQueryJoinsMergesFinished.xlsx
pdf Notes Handout to download: 007-MSPTDA-PowerQueryJoinsMerges.pdf
Assigned Homework:
Download Excel File with Homework:
007-MSPTDA-Homework-Start.xlsx
Example of Finished Homework:
007-MSPTDA-Homework-Finished.xlsx
Video/Class
Project 08:
Video to watch:
MSPTDA 08: Power Query Group By feature & Table.Group
Function (5 Examples) ( 30:42 min)
Start files for Video/Class Project to download:
008-MSPTDA-GroupBy-Start.xlsx
Finished files (completed after video done):
008-MSPTDA-GroupBy-Finished.xlsx
pdf Notes Handout to download: 008-MSPTDA-GroupBy.pdf
Assigned Homework:
Download Excel File with Homework:
008-MSPTDA-Homework-Start.xlsx
Example of Finished Homework:
008-MSPTDA-Homework-Finished.xlsx
Video/Class
Project 08 1/2:
Video to watch: MSPTDA
08.5: Power Query Group By Unique List or Consecutive Occurrences
( 07:18 min)
Start files for Video/Class Project to download: 00805-MSPTDA-GroupByConsecutiveOccurances-Start.xlsx
Finished files (completed after video done): 00805-MSPTDA-GroupByConsecutiveOccurances-Finished.xlsx
pdf Notes Handout to download: Sames
notes as above for video #8. Just use notes from previous video.
Assigned Homework:
Download Excel File with Homework: 008-05-MSPTDA-Homework-Start.xlsx
Example of Finished Homework: 008-05-MSPTDA-Homework-Finished.xlsx
Video/Class
Project 09 & 9.5:
Videos to watch: First:
MSPTDA 09 Power Query Complete M Code
Introduction: Values, let, Lookup, Functions, Parameters, More ( 01:07:26 min
- 1 hour 7 minutes)
Second:
Formula.Firewall Error in Power Query & Power
BI: Rebuild This Data Combination Solved (MSPTDA 9.5) (05:21
min)
Start files for Video/Class Project to download:
009-MSPTDA-ValuesExpressionsFunctions-Start.xlsx
009-ParameterQueryTables.zip
Finished files (completed after video done): 009-MSPTDA-ValuesExpressionsFunctions-Finished.xlsx
pdf Notes Handout to download: 009-MSPTDA-ValuesExpressionsFunctions.pdf
Assigned Homework:
Download Excel File with Homework: 009-MSPTDA-Homework-Start.xlsx
Example of Finished Homework: 009-MSPTDA-Homework-Finished.xlsx
Video/Class Project
10:
Video to watch: MSPTDA
10: Power BI M Code for Moving Annual Total (MAT): Custom Function Power Query
Custom Column ( 29:39
min)
Start files for Video/Class Project to download: 010-MSPTDA-CustomFunction-MovingAnnualTotals-Start.pbix
Finished files (completed after video done): 010-MSPTDA-CustomFunction-MovingAnnualTotals-Finished.pbix
Excel example:
010-MSPTDA-CustomFunction-MovingAnnualTotals-Excel.xlsx
zipped folder:
010-TextFiles.zip
pdf Notes Handout to download: 010-MSPTDA-CustomFunction-MovingAnnualTotals.pdf
Assigned Homework:
Download pdf file with homework description:
010-MSPTDA-Homework-Start.pdf
Example of Finished Homework in Power BI Desktop:
010-MSPTDA-Homework-Finished.pbix
Video/Class Project
11:
Video to watch: First: MSPTDA
11: Power Query to Import from SQL Server Database in Excel or Power BI Desktop ( 19
min)
Second:
MSPTDA 11.5 Which Power Query Steps Are Used
in SQL Query Folding? “View Native Query” feature! (3
min)
Start files for Video/Class Project to download: 011-MSPTDA-ImportSQLIntoPowerQuery-Start.xlsx
Finished files (completed after video done):
011-MSPTDA-ImportSQLIntoPowerQuery-Finished.xlsx
011-MPSTDA-PowerBIDesktop-GetSQL-Data.pbix
pdf Notes Handout to download: 011-MSPTDA-PowerQueryImportSQLData.pdf
Assigned Homework:
Download Homework File: 011-MSPTDA-Homework-Start.xlsx
Example of Finished Homework: 011-MSPTDA-Homework-Finished.xlsx
Video/Class Project
12:
Video to watch: MSPTDA
12: Using Locale in Power Query Power BI: Import & Append Text Files from
Different Countries ( 20
min)
Start files for Video/Class Project to download:
012-MSPTDA-LocaleStart.xlsx
zipped folder:
TextFiles012.zip
Finished files (completed after video done):
012-MSPTDA-LocaleFinished.xlsx
012-MSPTDA-LocaleFinished.pbix
pdf Notes Handout to download: 012-MSPTDA-Locale.pdf
Assigned Homework:
Download pdf file with homework description:
012-MSPTDA-Homework-Start.docx
Zipped Text Files:
012-TextFilesForHomework.zip
Example of Finished Homework in Excel:
012-HomeworkFinishedExample.xlsx
Video/Class Project
36:
Video to watch:
MSPTDA 36: How To Build Sample File Parameter
For A Custom Function to Transform 12 Cross Tab Tables ( 13
min)
Start files for Video/Class Project to download:
036-MSPTDA-SampleFileParameterForCustomFunction-Start.xlsx
Finished files (completed after video done):
036-MSPTDA-SampleFileParameterForCustomFunction-Finished.xlsx
pdf Notes Handout to download: none
Assigned Homework:
none