Power Query Online Training for Excel & Power BI Professionals

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 books “Supercharge Power BI” and “Supercharge Excel”).  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 time 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.
  • You will have access to the online videos for 36 months, so if you want to come back and watch them again then that is fine.
  • You can purchase the course and start-off on the course at any time, and you will get access to the lessons (videos) immediately.
  • It is great value at US$250 for 36 months access to the online materials (videos).
  • You can claim 10 CPD hours.
  • Certificate available on request after completion of the course.
  • Buy this and any Supercharge Power BI 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 Videos5 minutes Structure of each lessonWatching the video on the websitePausing and doing some practiceFinding the section a second timeWatching the videos faster
Welcome to Power Query for Excel and Power BI3 minutes Intro to training and trainer
Power Query UI Walk Through13 minutes Versions of Power QueryWhere to downloadRibbon and button overviewFormula barAdvanced editorLoading data from Query EditorRe-editing workbook queries
When to Use Power Query?21 minutes Power BI DesktopShape your dataCleanse your dataEliminate repetitive processesAuditing information
Module 2: Loading From Data Sources - 89 minutes
Lesson Key Topics Covered
Data Sources Overview4 minutes Get & Transform commands in Excel 2016Various data sources for importing dataOffice 365 user optionsData Source SettingsQuery OptionsCreating a New Query – From TableClose & Load from Query EditorShow Queries Recent Sources
Importing Data from the Web12 minutes Finding the data you needSplit ColumnsCopying Queries to Power BI DesktopRefresh the data
Load Data From File19 minutes Importing data from a single CSV fileImporting data from an Excel fileUnpivoting columnsImporting multiple sheets from an Excel workbookCombining multiple CSV files
Load From SharePoint21 minutes Connecting to SharePointDifferences between Excel and Power BI DesktopManaging CredentialsImporting from listsImporting from document librariesNested tables of contentData formatting in Power Query
Load From A Database16 minutes Connecting to a DB (SQL Server)Select Related TablesNaming ConventionsJoining TablesQuery FoldingNative SQL Queries
Using Your Queries17 minutes Do not Load – reuse in other queriesPower Pivot/Power BIPower Pivot for Excel 2010Excel TablesDirect load to Pivot Tables
Module 3: Transforming Data - 72 minutes
Lesson Key Topics Covered
Power Transformations16 minutes Unpivot dataPivot dataTemporary unpivotingPivot text data
Working with Rows12 minutes Use First Row as HeaderRemoving Top and Bottom RowsFiltering RowsWorking with Duplicates
Working with Columns22 minutes RemovingSplittingExtractingMergingDuplicatingNumerical ManipulationIndex ColumnsConditional Columns
Changing Data16 minutes Transpose your dataReordering columnsRemoving columnsChanging row orderReverse ordering
Column By Example6 minutes Example to demonstrate how Column From Example worksExample to show that Column From Example might fail in certain cases
Module 4: Foundation Concepts - 33 minutes
Lesson Key Topics Covered
Reusing Queries10 minutes Disabling loadNew query from referenceCopying queriesNew queries from merge
Managing Queries8 minutes Reusing standard queriesCopying queries between applicationsUsing query foldersQuery dependency view
Joining Queries15 minutes AppendMergeJoin TypesGrouping and Summarizing
Module 5: Power Query Patterns - 92 minutes
Lesson Key Topics Covered
Appending Multiple Files19 minutes Combining .CSV filesCombining Excel FilesConverting Queries to FunctionsDifferences between Power BI and Excel
Appending Multiple Tabs13 minutes Importing multiple sheetsCombining multiple sheetsSpecial trick to rename columns with a relative reference
Creating a Calendar Table27 minutes Used for Power Pivot and Power BIStart from a blank queryCreate a list of dates Set the following editable fields – Start – Today – DurationBuild out the CalendarCopy and paste for reuse
Comment against Data8 minutes Data used in the lessonDownload Bank StatementsCombine into a tableAdd comments in Excel You can’t easily add a comments table to an Excel Power Query tableMake the comments stick via self-referencing tables
Parameters and Parameter Tables17 minutes Parameters in Power BI Desktop Parameter Tables in Excel
Import from PDF8 minutes Converting PDF file to HTML (.mht) file in Microsoft WordLoading HTML file into Power BI DesktopCombining Two Sets of Columns to a Single Set of Columns
Module 6: Tips & Tricks - 38 minutes
Lesson Key Topics Covered
Managing Credentials8 minutes Managing CredentialsPrivacy LevelsA Common Error and Solution for it
Working with Multiple Models6 minutes Cut and Paste CodeCut and Paste QueryOrganizational Data CatalogOpen a Second Instance of Excel
Improving and Tidying your Queries9 minutes Rename important query stepsRemoving spaces from query step namesDelete all but the last reorder column stepGrouping queries in foldersRefer to a step in another query
Managing Changing Columns9 minutes Tip to fix when additional columns not required in the data model appear in the dataTip to fix when a column in the data model goes missing in the data
Query Folding6 minutes Example to show Query FoldingExample to show where Query Folding does not work
Module 7: Advanced Concepts - 52 minutes
Lesson Key Topics Covered
An Introduction to Advanced Concepts1 minutes Introduction to the advanced topics covered in this module.
M Language Introduction6 minutes M language code for query stepsAdvanced editorshared operator to get a list of M functionsConverting list to tableCreating a listM language online documentationUseful information on M language on my site
Relative Columns and Rows8 minutes An example to show where relative reference is necessaryDemo of using relative reference
Manual Functions11 minutes Create a QueryGive a good name to the functionEdit the code in Advanced Editor
Automatic Functions9 minutes Combine & Edit featureFolders and files created in the Queries List panelSample Transformation and the M functionParameter file and Sample fileEditing Sample Transformation
Cross Join5 minutes Demo to show how to Cross Join two tables
Nested Index7 minutes Demo to show how to Cross Join two tables
Error Handling Using the Try Function5 minutes Using #Shared to get a list of M Functions and their DescriptionsDemo of an error occurrence using Number.From FunctionDemo of the usage of Try KeywordDemo of the usage of Otherwise Keyword
Module 8: Tricky Examples - 32 minutes
Lesson Key Topics Covered
Find Previous Record9 minutes Step 1: Sort the data in the correct orderStep 2: Add an Index ColumnStep 3: Add an Index Column that indicates the previous rowStep 4: Join the table to itself using Internal JoinStep 5: Write a custom formula that compares opening and closing dates
Extract Values5 minutes Using Combine & EditExtracting a Value using UICombining the Values into a Table
Pivot Text Data4 minutes Demo to show why pivot text data does not workDemo of the trick to pivot text data
Manage Messy Headers5 minutes Sort out the first two columnsTranspose the tableSort out the Headers
Cleanse Dirty Data9 minutes Excel table for Cleanse dataLoading multiple tables on an Excel worksheet to Power QueryEnsuring to get the data from the right tableUsing M function Text.RemoveDemo 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.
  • Certificate available on request after completion of the course.

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.