Power BI For Excel 2016

Get and Transform Data Introduction

• Using Power Query to Link to External Data
• Working With the Power Query Editor
• Power Query Settings
• Apply Power Query Options
• Import External Data Into The Data Model
• Editing Existing Queries

Accessing Data Types From Power Query

• Creating A Connection To SQL Server
• Understanding the Database Query Editor
• Downloadind And Reviewing Data From A Database
• Importing Data From A Text File
• Importing Data From A Folder
• Importing Data From An Excel Spreadsheet
• Using The Current Workbook
• Importing Data From An Access Database
• Importing Data Directly From The Web

Transforming Data in Power Query

• Naming Columns
• Removing Columns
• Spliting Columns
• Merging Columns
• Setting A Column Data Type
• Filtering Rows
• Filtering Ranges
• Removing Duplicate Values
• Filtering Out Rows with Errors
• Sorting Columns
• Changing Values in A Table
• Using A Text Transformation
• Using Fill to Replace Missing Values
• Aggregating Values
• Calculating Values Across Columns
• Duplicating Columns
• Pivoting Columns And Rows
• Merging Multiple Queries

Importing Data Into PowerPivot

• Loading Data From A SQL Server Database
• Previewing And Filtering The Results
• Writing A SELECT Query
• Using A View As A Data Source
• Loading Tables From An Access Database
• Using A Text File As A Data Source
• Using Excel As A Data Source
• Loading Data From An Excel Table

Working With Data Model Relationships

• Create A Table Relationship Using A Join
• Managing Table Relationships
• View Table Relationships

Transforming Data In A PowerPivot

• Renaming A Table
• Deleting A Table
• Moving A Table
• Freezing and Unfreezing Columns
• Hiding Columns
• Filtering Columns
• Sorting Columns
• Sort A Column Data From Another Column
• Hiding A Table
• Create A Hierarchy
• Altering Table Behaviour

PowerPivot vs Power Query

• Understand the difference between PowerPivot and Power Query

DAX Columns And Measures

• Concatenating Columns
• Creating A Calculated Column
• The RELATED function
• Complete A Task
• Creating A Hierarchy
• Creating A Calculation Across Tables
• Using the ROWCOUNT And RELATEDTABLE Functions
• Using the IF and ISBLANK Functions

DAX Measures and Metrics

• Creating A Count Measure Using DAX
• Create A Measure Across Multiple Tables
• Use the SUMX function
• Filter using the CALCULATE Function
• Use the FILTER function

DAX Time Intelligences Calculations

• Create A Calendar
• Creating The Calendar Values
• Creating Month to Date Sales Results
• Creating Year to Date Sales Results

Text Based Calculations

• Creating A PivotTable
• Creating A PivotTable Visualisation
• Modify the PivotTable
• Utilise A Card Visualisation
• Utilise A Matrix Visualisation
• Filtering a PivotTable, Card and Matrix Visualisation

Power View Chart Visualisations

• Creating A Single Field Chart
• Sorting Chart Data
• Formatting Data Bar Colours
• Setting A Chart Title, Legend and Data Labels
• Creating Multiple Charts
• Building Multiple Unique Charts on a View
• Filtering Data Using A Chart
• Building A Bubble Chart
• Viewing Specific Data In A Bubble Chart

Power Maps In Excel

• Opening The Power Maps Feature
• Adding Geocode Data To A Power Map
• Navigating A 3D Map
• Adding Values To A 3D Map
• Adding A Category To A 3D Map
• Addding Time Dimensions To A 3D Map
• Filtering A 3D Map
• Playing A Tour