Microsoft Power BI Training

Power BI Complete Introduction

 Power BI Introduction
 Data Visualization, Reporting
 Business Intelligence(BI), Traditional BI, Self-Serviced BI
 Cloud Based BI, On Premise BI
 Power BI Products
 Power BI Desktop (Power Query, Power Pivot, Power View)
 Flow of Work in Power BI Desktop
 Power BI Service, Power BI Report Server, Power BI Mobile
 Flow of Work in Power BI / Power BI Architecture
 A Brief History of Power BI

Power BI Desktop Software Installation

 How to Download Power BI Desktop Software
 What are the Prerequisites to Install Power BI Desktop Software
 Power BI Desktop Installation procedure
 How to Open Power BI Desktop

Power Query Software Overview

 Overview of Power Query / Power Query Editor
 How to Open Power Query / Power Query Editor Software
 Connecting to Data Sources and Extracting the Data
 Establish connection to the Excel source
 Establish connection to the SQL Server
 Importing Data into Power Query Editor
 Transforming the Data in Power Query Editor using GUI Options
 Loading the Data into Power Pivot
 Understanding the M(Mashup) Language behind the GUI Options

Power Pivot Software Overview

 Understanding about Power Pivot – A In Memory Columnar Database
 Advantage of In Memory Columnar Database
 Power Pivot – xVelocity In-memory Analytics Engine (Previously called Vertipaq)
 Overview of Data Modeling – Model View(Previously Relationship View)
 Enhancing the Data Model – DAX(Data Analysis eXpressions) Language – Data
View

Power View Software Overview

 Overview of Visualizations, Fields, Field Wells
 Understanding Dimension Columns & Fact Columns
 Visualizing the Data using Power View and Power Maps – Report View
 Saving Report
 Publishing the Report to Power BI Service

Power BI Service Overview

 Power BI Service Overview
 Logon to Power BI Service
 Share the Reports with Clients

Power BI Desktop User Interface

 Understanding Power BI Desktop User Interface
 Fields Pane, Visualizations Pane, Filters Pane
 Ribbon, Multiple Views (Report View, Data View & Model View)
 Report Canvas, Pages Section

Building Blocks of Power BI

 Datasets, Visualizations, Reports, Dashboards & Tiles

Introduction to Power Query & Power Query Editor User Interface

 Overview of Power Query / Query Editor
 How to Open Power Query / Query Editor
 Extracting the Data from Data Sources
 Transforming the Data using GUI Options
 Loading the Data in Power Pivot
 Query Editor UI(Queries Pane, Data Pane / Results Pane, Power Query Ribbon)
 Query Settings Pane, Applied Steps, Formula Bar, Advanced Editor
 Understanding Power Query Conceptually

Data types and Filters in Power Query

 Data types, Changing the Data type of a Column, Detect Data Type
 Filters in Power Query and their Purpose
 Types of Filters in Power Query(Basic Filtering, Advanced Filtering)
 Auto Filter / Basic Filtering
 Filter a Column using Advanced Text Filters

 Filter a Column using Advanced Number Filters
 Filter a Column using Advanced Date Filters
 Filter Multiple Columns

Inbuilt Column Transformations

 Remove Columns / Remove Other Columns
 Choose Columns, Go To Column
 Name or Rename a Column
 Reorder Columns or Sort Columns(Move Left, Right, To Beginning, To End)
 Column from Examples( From All Columns, From Selection)
 Add Column / Custom Column
 Duplicate Column, Split Column, Merge Columns
 PIVOT, UNPIVOT Columns, Transpose Columns
 Replace Values, Remove Empty

In built Row Transformations

 Header Row or Use First Row as Headers, Use Headers as First Row
 Keep Top Rows, Keep Bottom Rows, Keep Range of Rows
 Keep Duplicates, Keep Errors
 Remove Top Rows, Remove Bottom Rows, Remove Alternative Rows
 Remove Duplicates, Remove Blank Rows, Remove Errors
 Group Rows / Group By

Merge Queries / Join Tables

 Merge Queries, Merge Queries as New
 Need of Merge Queries
 Minimum requirements to Perform Merge Queries
 Types of Joins / Join Kinds / Merge Type

 Left Outer (all from first, matching from second)
 Right Outer (all from second, matching from first)
 Full Outer (all rows from both)
 Inner (only matching rows)
 Left Anti (rows only in first)
 Right Anti (rows only in second)
 Cartesian Join or Cross Join, Self-Join
 How to Merge 3 Tables
 Use Fuzzy Matching Option

Append Queries / Union ALL Tables

 Append Queries, Append Queries as New
 Minimum requirements to perform Append Queries
 Append Two Tables , Append Three or More Tables
 Folder as Source
 Appending Multiple Files of same type from a folder using Single Query
 Appending Multiple Excel Files and Multiple Sheets using Single Query
 Union All, How to get Union Result

Query Options

 Copy Query, Paste Query, Delete Query, Rename Query
 Enable Load, Include in report Refresh
 Duplicate Query, Reference Query
 Move to Group, Move Up, Move Down
 Create Function, Convert To Parameter
 Advanced Editor, Properties

Home Tab Options

 Close & Apply, Apply, Close
 New Source, Recent Sources, Enter Data, Data Source Settings
 Manage Parameters( Manage Parameters, Edit Parameters, New Parameter)
 Suggested Values(Any Value, List of Values, Query)
 Convert to List
 Refresh Preview, Refresh All, Cancel Refresh
 Sorting Data

Transform Tab Options

 Reverse Rows, Count Rows, Replace Values, Replace Errors, Fill Down, Fill Up
 Text Column, Number Column, Date & Time Column Options
 Structured Column(Expand, Aggregate, Extract Values)
 Run R Script, Run Python Script

Add Column Tab Options

 Create Custom Function, Invoke Custom Function
 Conditional Column, Index Column, Duplicate Column
 From Text, From Number, From Date & Time Column Options

View Tab, Tools Tab & Help Tab Options

Power Pivot Software Overview

 Understanding about Power Pivot – A In Memory Columnar Database
 Advantage of In Memory Columnar Database
 Power Pivot – xVelocity In-memory Analytics Engine (Previously called Vertipaq)
 Overview of Data Modeling – Model View(Previously Relationship View)
 Enhancing the Data Model – DAX(Data Analysis eXpressions) Language – Data
View

Power BI Data Modelling – Model View (Previously Relationship View)

 Data Modeling Introduction
 Relationships, Need of Relationship Between Tables
 Dimension Column, Fact Column
 Dimension Table, Fact Table
 Star Schema, Snowflake Schema
 Galaxy Schema or Hybrid Schema or Fact Constellation Schema
 Relationship Types / Cardinality in General
 One-to-One, One-to-Many (or Many-to-One), Many-to-Many
 AutoDetect the relationship, Create a new relationship
 Edit existing relationships, Delete a relationship
 Make Relationship Active or Inactive
 Cross filter direction (Single, Both), Apply Security Filter in Both Directions
 Assume Referential Integrity

Enhancing the Data Model – DAX

 What is DAX(Data Analysis eXpressions) Language – Functional Language
 New Column, New Measure / Quick Measure, New Table
 DAX Table and Column Name Syntax
 Creating New Column, Creating New Measure
 Implicit Measures, Explicit Measures
 Difference Between New Columns & New Measures
 DAX Operators, Types of Operators
 Arithmetic Operators, Comparison Operators
 Text Concatenation Operator, Logical Operators

DAX Functions – Categories

 Text Functions
 Logical Functions
 Date and Time Functions
 Filter Functions

 Math & Statistical Functions
 Time Intelligence Functions

DAX Date & Time Functions

 YEAR, MONTH, DAY
 WEEKDAY, WEEKNUM
 FORMAT (Text Function)  Month Name, Weekday Name
 DATE, TODAY, NOW
 HOUR, MINUTE, SECOND, TIME
 DATEDIFF, CALENDAR
 Creating Date Dimension Table

DAX Text Functions

 LEN, CONCATENATE (&)
 LEFT, RIGHT, MID
 UPPER, LOWER
 TRIM, SUBSTITUTE, BLANK

DAX Logical Functions

 IF
 TRUE, FALSE
 NOT, OR, IN, AND
 IFERROR
 SWITCH

DAX Math and Statistical Functions

 INT
 ROUND, ROUNDUP, ROUNDDOWN
 DIVIDE
 EVEN, ODD
 POWER, SIGN
 SQRT, FACT
 SUM, SUMX
 MIN, MINX
 MAX, MAXX
 COUNT, COUNTX
 AVERAGE, AVERAGEX
 COUNTROWS, COUNTBLANK

DAX Filter Functions

 CALCULATE
 ALL

 RELATED

DAX Time Intelligence Functions

 Time Intelligence Introduction
 TOTALMTD, TOTALQTD, TOTALYTD
 PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR
 NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR
 SAMEPERIODLASTYEAR
 YOY Growth, QOQ Growth, MOM Growth

Report View / Power View

 Report View User Interface
 Fields Pane, Visualizations pane, Ribbon, Views, Pages Tab, Canvas
 Visual Interactions
 Interaction Type (Filter, Highlight, None)
 Visual Interactions Default Behavior, Changing the Interaction

Filters in Power View

 Filter Types
 Visual Level Filters, Page Level Filters, Report Level Filters, Drill Through
Filters
 Filter Sub Types
 Basic Filtering, Advanced Filtering, Top N, Relative Date Filtering
 Numeric field filters, Text field filters, Date and Time field Filters

Grouping, Binning & Sorting

 Grouping and Binning Introduction
 Using grouping, Creating Groups on Text Columns
 Using binning, Creating Bins on Number Column and Date Columns
 Sorting Data in Visuals
 Changing the Sort Column, Changing the Sort Order
 Sort using column that is not used in the Visualization
 Sort using the Sort by Column button

Hierarchies and Drill-Down Reports

 Hierarchy Introduction, Default Date Hierarchy
 Creating Hierarchy, Creating Custom Date Hierarchy
 Change Hierarchy Levels
 Drill-Up and Drill-Down Reports
 Data Actions, Drill Down, Drill Up, Show Next Level, Expand Next Level

 Drilling filters other visuals option

Power BI Visualizations

 Visualizing Data, Why Visualizations
 Visualization types, Field Wells
 Visuals for Filtering, Visualizing Categorical Data, Visualizing Trend Data
 Visualizing KPI Data, Visualizing Tabular Data, Visualizing Geographical Data
 Leveraging Power BI Custom Visuals

Visuals for Filtering

 Slicer Visualization
 When to use a Slicer
 Create and format Slicer Visualization
 Hierarchy Slicer (Custom Visualization)
 When to use a Hierarchy Slicer
 Create and format Hierarchy Slicer
 Advantages of Hierarchy Slicer

Visualizing Categorical Data

 Create and Format Bar and Column Charts
 Create and Format Stacked Bar Chart Stacked Column Chart
 Create and Format Clustered Bar Chart, Clustered Column Chart
 Create and Format 100% Stacked Bar Chart, 100% Stacked Column Chart
 Create and Format Pie and Donut Charts
 Create and Format the Tree Map Visual
 Create and Format Scatter Charts

Visualizing Trend Data

 Line and Area Charts
 Create and Format Line Chart, Area Chart, Stacked Area Chart
 Combo Charts
 Create and Format Line and Stacked Column Chart, Line and Clustered Column
Chart
 Create and Format Ribbon Chart, Waterfall Chart, Funnel Chart

Visualizing KPI Data

 Create and Format Gauge Visual, KPI Visual
 Create and Format Card Visualization, Multi Row Card

Visualizing Tabular Data, Geographical Data & R Script Visual

 Create and Format Table Visual, Matrix Visualization

 Create and Format Map Visual, Filled Map Visual, Arc GIS Maps Visual
 Create and Format R Script Visual