Course Duration:
2 days
Prerequisites:
You will be need to be working with Excel 2016 Professional Plus or Office 365 ProPlus
Objectives:
The course will show you how to:-
- create simple, elegant data models in PowerPivot;
- use these data models to create pivot tables based on multiple tables;
- use Power Query to build queries to extract, transform and load data;
- start to use the DAX language for more in-depth analysis of data;
- analyse time series data (“compare this month’s sales with the same period in the previous quarter”);
- create and display KPIs in Excel;
- create Power View reports within Excel based on your data models; and
- create and show Power Maps (including making videos of trends over time).
Subject Areas:
Preparing for Power BI
- Version of Excel needed
- Installing add-ins
- Basic multi-table pivot tables
PowerPivot data models
- Importing SQL Server tables
- Filtering rows/columns
- Data and diagram view
- Hiding from client view
- Relationships
Pivot tables using PowerPivot
- Drill-down vs Quick Explore
- Slicers and timelines
- Controlling what you see
Using Excel tables
- Creating and naming tables
- Adding to data models
Using other data sources
- Importing Access and Excel
- Importing cubes
- Pasting from the clipboard
Power Query sources
- Querying databases
- Web pages and XML files
Transforming data in Power Query
- Splitting columns
- Merging and inserting columns
- Other transforms
Calculated columns
- Creating calculated columns
- Using the RELATED function
- BLANK and SWITCH
Calculated fields (measures)
- The all-important query context
- Implicit calculated fields
- AutoSum fields
- Creating in Excel and PowerPivot
DAX basics
- The DAX Studio add-in
- Calculating ratios
- Aggregate X functions
The CALCULATE function
- Replacing filters
- Using ALL
- Using VALUES to edit filters
More advanced DAX functions (*)
- Using FILTER to filter tables
- Ranking
- The EARLIER function
Calendars
- Creating in Excel or SQL
- Linking to calendars
- Sorting months
- Multiple date tables
If you would like to book onto a course or find out more information please fill out our contact form or call us on 01454 203 355.
