Excel Power User Level 3

This course is designed for experienced users of Excel’s Power Pivot and Power Query tools and provides insights into the advanced aspects of these powerful technologies. Delegates will be shown how to write their own functions in the M language which underlies Power Query; and to create complex DAX formulas using advanced time intelligence functions.

Advanced Data Transformation

Grouping and aggregating rows of data; Creating conditional columns, Creating columns by referencing existing columns; Appending data with common column headers; Merging data with common rows; Combining all files in a folder

The Power Query Formula Language

Using The Formula Bar; Using The Advanced Editor; Overview Of The M Language; Exploring M using #shared

Understanding Automatically Generated M Code

Excel.Workbook; File.Contents; Table.PromoteHeaders; Table.TransformColumnTypes; Table.AddColumn; Table.ReplaceValue; Table.Skip; Table.RemoveLastN

Creating custom functions in M

Defining a function; Defining input parameters; The goes-to operator; Defining the function body; Using optional parameters; Calling functions

Iteration Techniques

Benefit of generating lists; Generating lists of numbers; Generating lists of dates; Generating alphanumeric lists; Using the Each function; Applying a function to a list of files

Advanced DAX

Using disconnected parameter tables; Creating parameter measures; Overview of DAX time intelligence; Creating a date table; Using DATEYTD and its variations; Using SAMEPERIODLASTYEAR; Using PARALLELPERIOD; Using FIRSTDATE and LASTDATE; Using DATEADD; Using DATESBETWEEN

Hierarchies

Creating hiearchies; Using RELATED to assemble columns; Adding, removing and reordering columns; Using a hierarchy on a pivot table

KPIs

Overview of KPIs; Creating KPIs; Base value; Target value; Status threshold; Creating measures for KPIs; Displaying a KPI on a pivot table

Creating 3D Map Reports

3D Map data requirements; Understanding scenes and tours; Adding fields to a report; Adding a timeline; Using themes; Creating scenes; Setting scene effect options; Using the Tour Editor; Creating heat maps