Power Query
Power Query Course (myOnlineTraininghub)
Syllabus
1. Introduction
a. What is Power Query
b. Installing Power Query
c. Interface
2. Data Sources
a. From the Web
b. From Excel Files
c. From Current Workbook
d. From CSV or Text File
e. From Folder
f. From Database (SQL)
g. From Other Sources (OData feed)
h. Online Search/Data Catalog Search
3. Combining Multiple Sources
a. Combine multiple Excel Tables or Named Ranges from current Table
b. Combine multiple worksheets not formatted as a Table or a named range
c. Appending Queries
d. Query Dependencies View
4. Transforming & Cleaning Data
a. Editing Queries
b. Data Types
c. Fixing Dates with Local Setting
d. Columns – Move, Remove, Rename & Duplicate
e. Splitting Columns
f. Merging Columns
g. Filtering Rows
h. Remove Duplicates & Errors
i. Sorting
j. Replace and Fill
k. Transforming Text & Numbers
l. Transforming Dates
m. Adding Custom & Index Columns (Create a Dates Table)
n. Adding Custom Date & Time Columns
o. Grouping
p. Unpivot
q. Transpose
r. Duplicating and Referencing Another Query
s. Add Column from Examples
t. Reuse and Share Queries
u. Add Column from Examples
5. Data Destinations & Refreshing Queries
a. Loading to the Workbook Options
b. Loading to the Data Model or Power Pivot
c. Load Settings & Automatic Refresh
d. Load Directly into Power Pivot in Excel 2010
6. M Language
a. Introduction to M
b. Where to Edit & Write M
c. Using Function to add a Custom Column
d. Reading M
e. Writing M in the Formula bar & Advanced Editor
f. Type System
g. Lists and Records
h. Splitting Text
i. Tips for Writing M (Notepad++ & #shared)
j. Commenting Code
k. Error Trapping
l. If and Nested if Function
m. Replicating IF OR and IF AND in Power Query
n. Conditional Columns
7. Parameter Tables & Custom Functions
a. Parameter Tables for Filtering
b. Scrape Data from Multiple Web Pages
c. Dynamic Folder Paths
d. Formula Firewall Errors
e. Manage Parameters
f. Variables
g. Add or Expand Rows
h. Referencing Another Query in M Code