BI and Data Analysis using Excel Power Tools Training

Delegates must be existing Excel users who have attended our Excel Advanced (QAEXADVC) course or have a similar level of knowledge and are able to:

• Create, format, and save worksheets and workbooks
• Create formulas using standard aggregate functions such as SUM, AVERAGE and COUNT
• Experience of working with list related tools and features such as sorting and filtering would be beneficial
• Create and modify PivotTables

NOTE: This course is suitable for users of Excel 2016, 2019, 2021 and 365 only.

• Understand the concepts of data
• Import and connect to data
• Prepare data using Power Query Editor
• Connect to and model data using Power Pivot
• Use DAX formulas in Power Pivot to analyse data
• Visualise and present data using PivotTables, PivotCharts and conditional formatting

Module 1: What is data?

• Different types of data
• Types of analysis
• Excel’s data tools
• Data analysis framework

Module 2: Connecting to data sources

• Working with common data sources
• Importing and connecting to data
• Working with queries and connections

Module 3: Transforming data using Power Query Editor

• Query Editor overview
• Power Query data types
• Combining data sources
• Cleaning and shaping data
• Creating calculated columns

Module 4: Data modelling with Power Pivot

• Introduction to Power Pivot
• Getting data into the Data Model
• Creating and editing data relationships in Power Pivot

Module 5: Analysing data using DAX in Power Pivot

• DAX fundamentals
• Creating calculated columns
• Creating measures
• Working with date tables

Module 6: PivotTables

• PivotTables overview
• Using the Data Model to create PivotTables
• Dynamic filters with Slicers and Timelines

Module 7: Visualising data

• PivotCharts
• Hierarchies and drilling
• Other visualisation types
• Conditional formatting
• KPIs