Detailed below are the various topics that we can include within our Microsoft Excel Power BI Two Day Training Course. Once we fully understand your business’ goals and the general skill level of attendees, we will provide a detailed proposal which will encompass the various elements that we feel would be appropriate.
Pre-requisites: This course can be designed for a wide range of skill sets; however it is recommended that delegates have at least a good understanding of Excel and/or Access.
Day 1 – Introduction to the Course:
Introduction to Self-service BI (Business Intelligence)
What is Power Query?
What is Power Pivot?
How does a Power Pivot table differ from a standard Excel pivot table?
What is DAX?
Introduction to Power Query:
Differences between Excel BI 2010/2016 & 2016
Power Query & Power Pivot working together
When to use Power Query
Importing data from various data sources
Using Power Query to transform & reshape various data types / formats
Transformation steps & the Advanced Editor
Loading data into the Power Pivot data model via Power Query
Learn Power Query & Power Pivot the Excel way
Introduction to Power Pivot & Data Modelling:
Load data directly into the Power Pivot data model
What’s the main difference between an Excel pivot table and Power Pivot?
Difference between ‘dimension’ and ‘fact’ tables
Database tables - multiple tables, relationships & direction of filter flow
Why use a ‘Calendar’ table & how to create one?
What is a ‘calculated column’ and when to use them?
What is a ‘measure’ and when to use them?
Understanding the concept of ‘filter context’
Think like the ‘Power Pivot’ engine
Understand how to and when to override the ‘filter content’
The golden rules of DAX measures
Implicit vs explicit measures
Introduction to some basic DAX functions – ALL(), CALCULATE(), DIVIDE(), MIN(), MAX(), RELATED(), SUM()
Introduction to some DAX ‘iterator’ functions
Introduction to some DAX ‘Time Intelligence’ functions
Using Slicers to filter data
How to improve the end user’s experience of filtering data sets using slicers
Data compression, file size & performance
Row oriented vs column oriented databases
Day 2 - Deep-dive into Power Pivot & Data Modelling:
Using advanced calculated columns
Introduction to the DAX FILTER() & VALUES() functions
How to work with many-to-many table relationships
Resolving duplicate Primary Key data
Working with disconnected tables & slicers
How to keep the data model running fast and smooth
How to build a Power View dashboard
Tell users when the data model was last refreshed
Day 2 will also contain some bespoke content based on feedback from Day 1 and/or due to organisational requirements. Wherever possible, the course will demonstrate new functionality and purpose using commercial like scenarios. These include but not limited to:
How to aggregate values for use in management reporting
How to aggregate values by various business measures – dates, product categories & geographical territories
How to perform calculations with values across multiple tables
Creating various types of ratios & KPI’s
Understanding the importance of using and maintaining good version controls