Course Outline
This course is not for the faint hearted. Learners should have an excellent knowledge of computers and Excel having completed the advanced level Excel or equivalent. Knowledge of charts in Excel and PowerPoint is an advantage.
At the end of the course learners will have an excellent level of knowledge of Power Query and be able to connect to various data sources and complete tasks such as combine and transform Excel files from a folder.
Click the headings below to view topics.
Import data from different sources.
Transforming and Cleaning
Load to
Size of file
File Options
Column Profiler
Status
Data tab, Get & Transform
Get Data, From File, From Workbook,
Transform data
Examples:
• Extract
• Text between delimiters
• Change data types
• Add Column Tab
• Number group
• Standard button
• Multiply from drop down
• Text Before Delimiter
• Value function instead of trim
Data tab – Get & Transform Data From Table / Range
Rename query
Examples:
• Unpivot Columns
• Replace Values
• Close & Load – Close and Load to
• Refresh
Preview pane
Data tab – Get & Transform – From Folder
• Combine & Transform data
• First File
• Removing steps like change data type
• First row as headers
• Remove Columns/rows
• Split Columns
• Detect Data Types
• Future Proof Filtering
• Fill down
• Reference Vs Duplicate
• Grouping
Data Tab – Get Data & Transform - From Web
Query Editor
Navigator Screen
Web View/Table View
Load and Load To
Queries & Connections
Examples:
• Rename query steps
• Close and Load
• Refresh in Table Design
• Connection Properties
Data Tab – Get Data & Transform - From Web
Weather Forecast scenario
Transform data
Delete unnecessary columns - remove columns
Close and load to - Existing worksheet
Refresh
Add emojis
Example:
• Conditional Column
