Power Query Training
Automate Your Data Cleaning Workflow
A FREE Microsoft Excel tool that:
Prepares your data accurately for reporting and analysis.
Automates data updating, cleaning, and shaping processes extensively.
Does not need macros or VBA programming.
Can be applied to Power BI.
But first, What's in it for You?
Save HOURS of self study. We give you the boost you need.
Learn exciting ways to add value to your data. We’ve carefully tailored our courses to feature all important aspects of Excel’s Power Query. This includes learning how to build speedy, automated data cleaning workflows. In just 2 days, you’ll gain useful skills to help you achieve maximum efficiency.
Get inspired through personalised engagement.
Our Power Query training class focuses on interaction, coupled with a hands-on approach. Discuss your Excel pains with us and address your challenges. We’ll guide you to sustainable solutions as you engage in class and with our coach. You'll have access to expert advice on how to get your data talking for you.
Free consultation to ensure you're on the right track.
We care about your success: it’s important to us that you show off those new Excel ninja skills at work. Learning is a continuous process, so if you need extra advice or have more questions on Power Query, we've got you covered. Reach out to us anytime for a no-frills 90 mins post class session.
(Upcoming classes 6 & 7 June 2019, 20 & 21 June 2019, 19 & 20 September 2019) - Singapore
What you learn:
Effective use of Power Query editor to:
Connect and combine multiple data sources without VLOOKUP(), INDEX() or MATCH().
Clean, shape, and transform raw data into flexible and easy-to-manipulate forms.
Group and summarise your raw data easily.
Add calculated columns.
Create custom functions that lets you automatically apply the same data preparation steps to separate files and combine them.
|Topic||Learn how to...||Power Query tools|
|1. What is Power Query?||◾ Unlock possibilities with Power Query
◾ Appreciate implications of starting with the right questions
|◾ Excel versions with Power Query
◾ Test drive an automated workflow
|2. How to import data?||◾ Connect to data sources
◾ Load data to destinations based on use case
|◾ Create Query to get data from CSV / Text, Excel Data Table
◾ Set data type; set date based on locale setting
◾ Load data; Add to Data Model
|3. How to automatically add new data to existing data?||◾ Consolidate data from different worksheets and / or files into one place
◾ Append new data to existing
|◾ Append Queries
◾ Get data From Folder
◾ Aggregate Excel worksheets
|4. How to painlessly clean dirty data?||◾ Bring in only what you need
◾ Get your data into Tabular Form
◾ Make your data usable, reliable, and relevant for a solid analysis
◾ Future-proof your data cleaning steps
|◾ TRIM & CLEAN
◾ Split Column / Extract Text
◾ Replace Values & Fill
◾ Remove Duplicates
◾ Merge Columns
◾ Reference Queries
◾ Unpivot & Transpose
|5. How to integrate data without VLOOKUP(), INDEX() and MATCH()?||◾ Join data tables together
◾ Summarise data at an aggregate level suitable for reporting
|◾ Merge Queries
◾ Group By and perform Sum, Average, Min, Max, Count Rows, Count Distinct Rows
|6. How to enrich data?||◾ Categorise data conditionally (OR, AND, IFERROR)
◾ Understand basics of M language
|◾ Add custom calculated column
◾ Add custom conditional column
|7. How to make (query) life easy 01 – Organise Queries||◾ Keep queries organised for easy reference and access with best practices||◾ Name, order, and group query|
|8. How to make (query) life easy 02 – Custom Function||◾ Read and edit M language
◾ Turn a set of ready data preparation steps into a re-usable function
◾ Debug custom function
|◾ M language
◾ Create custom function
|9. How to make (query) life easy 03 – Dynamic Date Table||◾ Automatically generate date table given transaction data date range (for use in Power Pivot for time intelligence DAX functions)||◾ M language
◾ Create dynamic date table
What you get :
FREE 90-minute after-class consultation session. If you need some extra help to implement what you have learned in our classes, we've got you covered! You'll receive customised advice on which specific functions / set-up you should consider applying to your report-building process.