G Com Solutions Limited

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