Course Code: 19102

Power Pivot, Power BI in Excel 2016 - 2010

Class Dates:
5/11/2023
Length:
2 Days
Cost:
$645.00
Class Time:
Technology:
Office
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • Course Overview
  • This 2 day course is intended to train you on Power Pivot and Power BI. Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft's tightly-related pair of revolutionary analytical tools - tools that are fundamentally changing the way organizations work with data. Their shared state of the art calculation engines (DAZ and M) turn any PC into an analytical supercomputer, but the real secret is people - these tools are 100% learnable by today's users of Excel.

    This course will discuss the various methods and best practices that are in line with business and technical requirements for modeling, visualizing, and analyzing data with Power BI. The course will also show how to access and process data from a range of data sources including both relational and non-relational data.his course will also explore how to implement proper security standards and policies across the Power BI spectrum including datasets and groups.
  • Audience
  • The audience for this course are data professionals and business intelligence professionals who want to learn how to accurately perform data analysis using Power BI. This course is also targeted toward those individuals who develop reports that visualize data from the data platform technologies that exist on both in the cloud and on-premises.

Prerequisites

  • Before attending this course, students must have:

    Basic knowledge of the Microsoft Windows operating system and its core functionality.
    Advanced working knowledge of Excel spreadsheets including formulas..

Course Disclaimer

*Course Cost listed does not include the cost of courseware or exam. Course is subject to a minimum enrollment to run. Course may run virtually as a Virtual Instructor-Led (VILT) class if the minimum enrollment is not met. If the course is under the minimum enrollment the course may run as 1 day class (Bootcamp Style). For more information, please contact learn@vtec.org or call 207-775-0244.

Course Details

  • Introduction
  • Power Pivot and the Power BI Family
  • Raw Data
  • Power Query
  • Power Pivot
  • Power View
  • Excel Pivots, Charts & Grid
  • Power Map
  • Power Pivot Versions
  • Interface
  • Learning Power Pivot
  • When to use Power Pivot
  • Normal Pivot
  • Grab data
  • Calculate Columns and Measures
  • Loading Data Into Power Pivot
  • Launching the Power Pivot Window
  • Edit Cells in the Power Pivot Window
  • Save - Same XLSX File
  • Different Sources
  • Linked Tables
  • Add to Data Model
  • Advantages and Limitations
  • Pasting Data Into Power Pivot
  • Importing From Text Files (Data Source Type)
  • Databases
  • Reporting Services (SSRS) Reports
  • Data Feeds, Table Properties
  • Introduction to Calculated Columns
  • Two Kinds of Power Pivot Formulas
  • Adding Your First Calculated Colum
  • Starting a Formula
  • Referencing a Column, Renaming, Another Calculation
  • Properties of Calculated Columns
  • Excel Functions Power Pivot
  • Introduction to DAX measures
  • Formula Engines
  • Adding Your First Mesaure
  • Create a Pivot
  • Add a Measure
  • Implicit Versus Explicit Measure
  • Referencing Measures in Other Measures
  • Dependent Measures
  • Centrally-Defined Number Formatting
  • Slicers
  • Portable Formulas
  • Rules of DAX Measures
  • DAX Engine
  • DAX Measure
  • Measure Cell
  • Evaluate
  • Calculate Alters Filter
  • Filter Coordinates
  • Relationships
  • Best Practice
  • Calculate ( )
  • SUMIF( )
  • Syntax and Action
  • How Calculate ( ) Works
  • Alternatives
  • Filter Context
  • ALL ( ) Remove a Filter Function
  • Crisp Basics
  • Practical Basics
  • Negating a Slicer
  • Variations
  • ALLEXCEPT ( )
  • ALLSELECTED ( )
  • Multiple Tables
  • Relationships
  • Lookup Tables
  • Diagram View
  • Related Tables in a Pivot
  • Filters
  • Disconnected Tables
  • Parameterized Report
  • Field List
  • Parameter Measure
  • Disconnected Table Variation Thresholds
  • Intoduction to FILTER ( ) Function and Disconnected Tables
  • Why is FILTER ( ) Necessary?
  • How to Use FILTER ( )
  • Applying FILTER ( )
  • Shared Pattern
  • Variations on Disconnected Tables
  • Sort Order - Slicer
  • Introduction to Time Intelligence
  • Standard Calendar
  • Custom Calendar
  • Special Lookup Table
  • Properties
  • Enable Date Filtering
  • Special Feature
  • Anatomy of DATESYTD ( )
  • Calculations
  • IF ( ), SWITCH ( ), BLANK ( ), and Other Conditional Fun
  • Using IF ( ) in Measures
  • BLANK ( ) Function
  • DIVIDE ( ) Function
  • ISBLANK ( ) Function
  • HASONEVALUE ( )
  • SUMX ( ) and Ohter X Functions
  • Anatomy of SUMX ( )
  • SUMX ( ) Action
  • Using the X Functions on Fields
  • Optional Parameters
  • TOPN ( )
  • Arguments to the X Functions
  • Multiple Data Tables
  • Service Calls
  • Measures from Different Data Tables in the Same Pivot
  • Hybrid Measures
  • Multiple Data Table Rules
  • Data Table Connected
  • Creating Relationships
  • Repeating the "New Table"
  • Integrated Pivot
  • Hybrid Measures
  • RANKX ( ) Third Argument
  • Performance - Keep Things Running Fast
  • Slicers
  • Cross-Filtering
  • How to turn off Cross-Filtering
  • Narrower Tables
  • Imported Columns
  • Star Schema
  • Measure Performance
  • DISTINCTCOUNT ( )
  • FILTER ( )
  • Loops
  • Power Query
  • Creating a Single Power Pivot Table
  • Connecting to CVS Files
  • Adding a Custom Column, Tag, Loading Data
  • Combine Multiple Files from a Folder
  • Headers
  • Remove Errors
  • Adding Custom Columns to Your Lookup Table
  • Define Custom Formula
  • Unpivot a Table
  • Create Lookup Table
  • Create Data Table
  • Create a Calendar Table
  • Power BI Desktop
  • Creating Reports
  • Fully - Interactive Reports
  • Get Data
  • Data Model - Power Pivot
  • Manage Relationships
  • Create New Measures
  • Create Calculated Columns
  • Reports - Power View
  • Importing Existing Excel Power Pivot Models
  • Sharing Power BI Desktop Files
  • Multiple Relationships
  • Bridge Table
  • Row and Filter Context Demystified
  • Row Context
  • Filter Context
  • Relationships and Filter Context
  • Relationships and Row Context
  • Iterator Functions Create Row Context During Measure Calculation
  • CALCULATE Creates Filter Context in Calc Columns
  • Using Measures Within a Row Context
  • CALCULATE and FILTER
  • Override Pivot Filters
  • Purpose of ALL ( ), FILTER ( )
  • Remove Filters
  • Nesting Tables
  • Time Intelligence with Custom Calendars
  • Custom Calendars
  • The Periods Table
  • Connecting the Periods Table
  • Sales in Period
  • Sales per Day
  • Clear Filters
  • Re-Filter - Navigation Arithmetic
  • MAX ( )
  • GFITW Measures
  • Fixing Measures
  • Fixing YOY
  • Percent Growth Formulas
  • Advanced Calculated Columns
  • Grouping Columns
  • Columns for Sorting
  • EARLIER ( ) Function
  • Calculated Columns are Static
  • New DAX Functions and Variables
  • New Functions
  • DATEDIFF ( )
  • MEDIAN ( ) and PERCENTILE
  • PRODUCT ( )
  • GEOMEAN ( ) and GEOMEANX ( )
  • X Functions
  • CONCATENATEX
  • ISEMPTY ( )
  • INTERSECT ( ) and UNION ( )
  • DAX Variables
  • VAR
  • Server, Power Pivot and SSAS Tabular - Cube Formulas
  • Network Distribution via File Shares
  • Importance of Web/Mobile
  • Upload XLSX/PBIX File
  • Sharing Your Dashboard
  • Cloud/Server Option Comparison
  • Cloud/Server Sharing Option
  • SSAS Tabular Features
  • Power Pivot to SSAS TAbular
  • Connect to SSAS Tabular from Excel
  • SSAS Tabular - Visual Studio
  • CUBEVALUE ( )