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