Excel Power User Level 2

    This one day course is designed for users who are already familiar with Excel’s Power Query and Power Pivot tools. It shows users how to connect to a wide variety of data sources and create data models which include DAX calculated columns and measures. Delegates will also be shown how to create rules for transforming data as it is added to the data model and how to create Power View reports.

    Connecting to Data from outside Excel

    Getting native Excel data; Getting data From relational databases; Getting data From .CSV And .TXT files; Getting tabular data from a web page

    Transforming Columns of Data

    Removing unwanted columns; Renaming columns; Reordering columns; Changing the case of columns; Trimming and cleansing columns; Extracting characters from columns; Performing mathematcial operations on columns; Splitting columns; Concatenating columns; Converting columns to Boolean values; Transforming date columns

    Transforming Rows

    Promoting row headers; Removing unwanted rows of data; Removing duplicate rows; Removing rows which contain error values; Replacing error values; Counting rows

    Performing other Data Transformations

    Using Find and Replace; Filtering out unwanted rows; Changing data types; Unpivotting columns of data; Pivotting rows of data

    Creating Calculated Columns

    DAX and Excel formulas compared; When to create calculated columns; User-friendly DAX functions; Using IF and SWITCH; Using the RELATED function

    Creating Measures

    Implicit and explicit measures; Creating measures best practices; Referring to measures in other measures; How measures are stored; Using DAX helper measures

    Understanding the DAX Language

    Understanding the DAX formula evaluation context; Understanding the Row context; Understanding the filter context; Using SUMX and COUNTX; Using RELATEDTABLE; Using COUNTROWS and DISTINCTCOUNT

    Understanding the CALCULATE function

    CALCULATE and SUMIFS compared; Why CALCULATE is so important in DAX; Understanding the CALCULATE syntax; How CALCULATE alters the filter context; Using CALCULATE with boolean filter statements; Using CALCULATE with the ALL, ALLEXCEPT and ALLSELECTED functions; Using CALCULATE with the FILTER function

    Creating Power View Reports

    Power View basics; Using the filters pane; Using cards for big picture insights; Tables and matrixes; Creating line charts; Creating pie charts; Creating scatter charts; Using Power View maps