{Big} Data Analytics with Advanced Excel Masterclass:.
Automate data transformation and unlock the power of data for strategic insights.
Course overview.
ADVANCED EXCEL TRAININGData is everywhere. With the explosion of data that businesses collect comes new challenges and opportunities. Business leaders at every level need to be data literate and turn complex datasets into actionable insights and make important business decisions. Excel Power Query is the answer to big data that doesn't fit in 'normal' Excel. Use Power Query to connect to hundreds of data sources, simplify data preparation and drive complex analysis. Power Pivot is a powerful data analysis and data modeling tool. This advanced class covers the use of Excel built-ins Power Query and Power Pivot as tools for analysing and organising large amounts of data.
We will show you how, without the need of former data analysis training, you can be analysing data, transforming information into meaningful insights for better decision making. Use Power Query and Power Pivot to mold your data in Excel so you can explore and visualise it with PivotTables and PivotCharts
Key takeaways.
ADVANCED EXCEL TRAINING- Confidently & productively analyse large, complex datasets to gain actionable insights
- Automate routine complex data cleaning and transformation with Power Query
- Combine multiple data sources into one dataset, model it and build KPIs and metrics that answer specific business questions
- Actualize a data model from multiple sources
- Mine and uncover hidden insights from big data with basic DAX
Who should attend.
This workshop is crucial for analysts, managers, business owners, executives, and anyone responsible for analysing big data to gain business insights. This training is highly recommended for:
Excel Users: If you use Excel daily and want to exploit the full power of the Excel to improve reporting capabilities.
IT and BI People: Professionals that are in charge of the data warehouse management of their company and need to understand how to provide support to analysts who need to extract data from Power Query and Power Pivot.
Prerequisites.
No prior knowledge of programming or data analysis is required.
The programme.
ADVANCED EXCEL TRAININGFull Power Query and DAX programme — 2 days
SESSION 1Excel As A Big Data Analytics Tool
- Overview of training aim, objectives and agenda
- Using Excel for big data analytics
SESSION 2What Is Power Query? >>> Engine To Extract, Transform & Load (ETL) Data
- Using Power Query as an ETL tool
- Power Query M language: Do you need to learn M?
- Importing data from single or multiple sources
- File: Excel, CSV, Folder
- Database (SQL, SAP, Oracle, etc)
- Online services
- Other: Web, OData Feed
- Unstructured vs structured data
- Query Editor ribbon, Navigator pane & Preview grid
- Query settings panel - recording of transformation steps
SESSION 3Data 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 4Data Transformation Techniques
- Converting (selected) columns to rows and vice versa:
- Transpose
- Unpivot columns
- Pivot column
- What are the differences? When to use them?
- Transforming text columns:
- Format: trim, clean, uppercase, lowercase, add prefix
- Split column & merge columns
- Extract texts before/after delimiter
SESSION 5Data Aggregation & Calculation
- Summarise data with Group By (Sum, Median, Min, Max, etc)
- Custom Columns: Create formulas for computations
- Index Columns (row counters/sort orders)
- Number columns statistical calculations
SESSION 6Combining 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 7Organising 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
- Disabling load of redundant queries to enhance performance
SESSION 8Power Query Time Saving Tips
- Copy queries to another Excel workbook or a Power BI file
- Duplicate and reference queries
- Preview or navigate to table objects
- Navigate through complex query dependencies
SESSION 9DAX (data Analysis Expressions) Basics
- Using DAX to perform advanced analysis to gain insights in Power Pivot
- Understanding DAX - tables/columns (DAX) vs cells (Excel)
- Understanding the difference between:
- Implicit measure
- Explicit measure
- Understanding the difference between:
- Calculated column
- Measure
- Power Query vs DAX: How do you decide?
SESSION 10DAX Context
- Understanding context as the key to:
- Building high-performing, dynamic analyses
- Troubleshooting problems in formulas
- Types of evaluation context:
- Filter context
- Row context
- Types of filters:
- Implicit filter (from the current query)
- Explicit filter (external filters outside the visuals)
- Context transition in calculated columns
SESSION 11DAX Calculation Engines - The Aggregators And Iterators
- How DAX aggregators and iterators work differently
- Aggregating functions:
- SUM, AVERAGE, MIN, MAX
- COUNT/COUNTA/COUNTBLANK/COUNTROWS
- Iterating functions:
- SUMX, AVERAGEX, MINX, MAXX
SESSION 12Commonly Used DAX Functions
- Filter functions: CALCULATE, FILTER
- CALCULATE: The most used function in DAX
- Using CALCULATE to add, modify or remove filters
- Contex transition with CALCULATE
- Using FILTER to reduce the number of rows in the table
- Safe DIVIDE function
- Logical functions:
- IF
- SWITCH (Nested IFs)
- AND
- OR
- Logical operators: AND (&&) and OR (II)
SESSION 13DAX Tips And Tricks
- Simple to advanced: Start with simple formulas, then branch out to the advanced calculations
- Variable: Using variables in DAX formulas to help write complex and efficient calculations
SESSION 14Data Modelling
- Creating and editing relationships
- Active vs inactive relationships
- Fact table vs dimension table
- Relationship cardinality
- One-to-one
- One-to-many
- Many-to-many
SESSION 15Using 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 16Hands-on Excel Data Analytics Project
- Applying Excel data analytical skills
- Choice of dataset:
- Sales/Finance dataset
- Predictive maintenance dataset
- Bring Your Own Data (BYOD)
- Throughout the workshop, participants will work through practical exercises in Power Query, Data Modelling and Power Pivot
Focused Power Query and PivotTable programme
SESSION 1Excel As A Big Data Analytics Tool
- Overview of training aim, objectives and agenda
- Using Excel for big data analytics
SESSION 2What Is Power Query? >>> Engine To Extract, Transform & Load (ETL) Data
- Using Power Query as an ETL tool
- Power Query M language: Do you need to learn M?
- Importing data from single or multiple sources
- File: Excel, CSV, Folder
- Database (SQL, SAP, Oracle, etc)
- Online services
- Other: Web, OData Feed
- Unstructured vs structured data
- Query Editor ribbon, Navigator pane & Preview grid
- Query settings panel - recording of transformation steps
SESSION 3Data 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 4Data Transformation Techniques
- Converting (selected) columns to rows and vice versa:
- Transpose
- Unpivot columns
- Pivot column What are the differences? When to use them?
- Transforming text columns:
- Format: trim, clean, uppercase, lowercase, add prefix
- Split column & merge columns
- Extract texts before/after delimiter
SESSION 5Data Aggregation & Calculation
- Summarise data with Group By (Sum, Median, Min, Max, etc)
- Custom Columns: Create formulas for computations
- Index Columns (row counters/sort orders)
- Number columns statistical calculations
SESSION 6Combining 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 7Organising 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
- Disabling load of redundant queries to enhance performance
SESSION 8Power Query Time Saving Tips
- Copy queries to another Excel workbook or a Power BI file
- Duplicate and reference queries
- Preview or navigate to table objects
- Navigate through complex query dependencies
SESSION 9Using 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 10Hands-on Excel Data Analytics Project
- Applying Excel data analytical skills
- Choice of dataset:
- Sales/Finance dataset
- Predictive maintenance dataset
- Bring Your Own Data (BYOD)
Throughout the workshop, participants will work through practical exercises in Power Query, Data Mod elling and Power Pivot
Your trainer.
ADVANCED EXCEL TRAININGAlvin Yong is a UK-registered Fellow Chartered and Certified Accountant (FCCA). He obtained his Master of Science in Professional Accountancy (Merit) in 2017 from the University of London.
To bring.
ADVANCED EXCEL TRAININGReviews from our clients.
ADVANCED EXCEL TRAINING“Content is very good, notes are sufficiently detailed, presentation is clear and pace is good. Alvin entertained questions calmly, clearly, and with sufficient depth. Very knowledgeable and qualified for the training subject.”
“Alvin is an excellent trainer. He led interesting, interactive and focused discussions during the workshop.”
“Alvin was well prepared, knowledgeable and easy to follow. Level of detail was where it needed to be and the documentation was very thorough. The course went quickly which was always a good sign.”
“Fantastic trainer in terms of knowledge and skills. Extremely friendly, helpful and patient.”
“Good understanding and knowledge of the subject. Attentive and responsive to participants' queries. Presented the subject very clearly.”
“The trainer is knowledgeable. He was helpful. He handled the class material well.”
“Very informative. Gave an excellent count of how-to's.”
Related courses.
EXCEL · POWER QUERY · AUTOMATIONReady when your team is.
Public runs across the region and live online — or we scope an internal cohort around your actual reports.
Enquire about in-house training