Excel Power Query: Automating Your Data Analysis and Reporting


Date Time: May 15, 2023 | 10:00 am
Time Zone: GMT+8:00 China Taiwan Time

Total Hours

20 Hrs

Power Query is dubbed as the best thing that happened to Excel in recent years. It can “automate” mundane tasks just by using the Interface.
In summary:
4 sessions x 2 hours
BONUS 1 Hour one on one consultation
25% Discount on the next training (Face-to-Face)
Course Outline
What is Power Query?
Introduction and Expectation
Get and Transform Expert Tool
Cleansing and Prepping your DATA
What is a Tabular Data Format?
Meet Tabular Data Format
Best Definitions
Benefits of using Power Query
Can handle large Data
Capable of AUTO Update or Auto-Refreshes of your Data
Can connect to different data sources
Can quickly transform data without formulas
Get to know the Interface
Data Load Options
Load to Table
Load to PivotTable Report
Load to PivotTable Charts
Load as Connection Only
Load to Data Model
Data Sources Connectors
Excel File
Excel Table
CSV Files
Non-Tabular Excel Files
How to enable Auto-Refresh(Key Topic)
Essential Text Transformation
Auto Detect Data types
Compare Transform vs. Add Column
Different options for Splitting a Column
Other options for Formatting a Column
Other options for Extracting Text
Essential Number Transformation
Create basic Statistical values
Compute mathematical calculations
How to use Rounding Values
Important Date & Time Transformation
Powerful Date Transformation
technique and how to use them
Time Transformation
Duration of Time
Advanced-Data Transformations
How to Unpivot columns and WHY?
How to Pivot columns
Know the difference between Unpivot vs. Pivot
Learn this powerful feature, “Group By.”
Other important data transformations
Replace Values, including error handling
Removing Duplicates
How to Fill values Up/Down
How to use Filter and its Pitfalls
Sorting and the Index Column method
How to handle changes in the location of the data source?
How to troubleshoot errors
Understanding the source code and how to correct it
Other key areas to watch out for!
Appending and Merging Data
What are the critical differences between append and merge
How to combine files in a folder when appending
Learn the different Merge Options and Join kinds
Exercises! When to Merge or Append
Where to Learn More?
Important websites and channels
Meet the Power Query community
Subscribe to monthly updates
Meet the Power Query Experts

About the Event