ADVANCED EXCEL MASTERCLASS:
Automate repetitive data transformation &
Analyse data for business insights
Download brochure
Click on the locations below to request for Advanced Excel Course brochures
| 11-12 Aug | Live Online Training |
Key Topics Covered
- Power Query
- Power Pivot
- Data Models
- DAX (Data Analysis
Expressions) Basics - Data Cleaning & Transformation
- Automation Techniques
why you should attend? (ADVANCED EXCEL training)
Do you spend hours just to update your daily, weekly or monthly Excel reports?
If so, you are doing it wrong! All you need are a few key techniques to set up your reports, then you can simply click the refresh button and the reports will be updated. Job done!
One of the key techniques is Power Query. Power Query has been a built-in feature since Excel 2016! It is Get & Transform within the Data tab in the ribbon. Unfortunately many people haven’t explored what Power Query has to offer! For examples:
- Do you work with multiple data sources and tables?
- Do you use V-Lookup to create a single table from multiple data sets?
- Do you use copy & paste to combine multiple tables into one?
- Do you spend hours cleaning and processing your data, only to repeat the same steps when the data is updated
If you answered ‘yes’ to any of these, Power Query will be your game-changer.
These are some of the many functions of Power Query:
- Connect to multiple data sources
- Reshape, transform and clean data
- Combine tables using merge or append
- Record and automate transformation steps
Key Takeaways (ADVANCED EXCEL training)
By the end of the course, participants will be able to:
- Automate routine data cleaning and transformation with Power Query
- Analyse and present complex data
- Actualize a data model from multiple sources
- Shape and filter tables to prepare data for analyses
- Mine and uncover hidden information with basic DAX
Workshop overview
Power Query is the recommended experience for discovering, connecting to and importing data. Power Pivot is a powerful data analysis and data modeling tool. Use Power Query and Power Pivot to mold your data in Excel so you can explore and visualise it with PivotTables, PivotCharts and Power Map.
Methodologies
Clear explanation of theories coupled with hands-on exercises for a firm grasp of Excel Power Query through:
- Practical Examples
- Case Studies
- Short Exercises
- Group Discussions
Who Should Attend
This workshop is crucial for analysts, managers, business owners, executives, and anyone responsible for analysing data to gain business insights. This training is highly recommended for:
- Directors
- General Managers
- Chief Financial Officers
- Finance Managers
- Accountants
- Marketing Managers
- Sales Managers
- HR Managers
- Business Owners
- Anyone who deals with business, management and financial reporting
Software Used
The course is demonstrated using Excel for Microsoft 365, but participants can use Excel 2016 or 2019 (Windows)
ProgramME Agenda (ADVANCED EXCEL training)
Session 1: INTRODUCTION
- Overview of training aim, objectives and agenda
Session 2: WHAT IS POWER QUERY?
- Extracting, Transforming and Loading (ETL) data
- Power Query vs “normal” Excel
- Importing data with Power Query
- Sources of data:
- Excel
- CSV/Text
- SQL Database
- Web
- Query Editor ribbon, Navigator pane & Preview grid
- Query settings panel - recording of transformation steps
Session 3: DATA CLEANING & FORMATTING
- Data types: Number, Date, Text, True/False
- Remove Columns, Remove Other Columns & Choose Columns
- Removing duplicates from a data set
- Replacing values and errors
- Null values: Replace, fill or filter out
- Removing whitespaces and non-printable characters
Session 4: DATA TRANSFORMATION TECHNIQUES
- Transpose, Unpivot Columns and Pivot Columns: What are the differences? When to use them?
- Text transformations:
- Extract texts before delimiter
- Extract texts after delimiter
- Add Prefix/Suffix
- Convert texts to uppercase/lowercase
Session 5: DATA AGGREGATION & CALCULATION
- Summarise data with Group By (Sum, Median, Min, Max, etc)
- Custom Columns: Create formulas for computations
- Index Columns
- Number columns statistical calculations
Session 6: COMBINING DATA (QUERIES)
- Difference between merge and append
- Merge queries (the V-Lookup in Power Query)
- Append queries (combining tables with the same headers)
- Use cases of merge and append queries
Session 7: ORGANISING POWER QUERY AUTOMATION
- Best practices in organising your data sources
- How to automate your data refresh
- Importing multiple files located in a folder
- Update Power Query for change in location of source data
- Change the default “Close & Load” options
Session 8: POWER QUERY TIME SAVING TIPS
- Copy and paste queries to another Excel workbook
- Duplicate and reference queries
- Preview or navigate to table objects
- Navigate through complex query dependencies
Session 9: POWER PIVOT CONCEPTS
- Extracting information from data with Power Pivot
- Uncovering data interpretation issues
Session 10: POWER PIVOT DATA MODEL
- Data Terminology
- Defining a consolidated view of data
- Generating a data mashup from structured and unstructured data sources into a data model
- Deriving relationships from data sources with the Relationship tool and the Diagram View
- Acquiring data from related tables
- Create linked data
- Defining calculated columns
Session 11: USING PIVOTTABLE WITH POWER PIVOT
- Create PivotTable with data models
- Analyse and summarise data with PivotTable
- Creating hierarchies in data model
- Data visualisation with PivotCharts
Session 12: IMPLEMENTING DAX FUNCTIONS IN POWER PIVOT
- Expressing information with measures
- Understand the difference between calculated columns and measures
- Understand the difference between implicit and explicit measures
- Exposing hidden information from data
- Quantifying and mining information with DAX functions
- Evaluating expressions with the CALCULATE() function and filter functions