Course content

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 Report Server, Power BI Service, Power BI Mobile
• Flow of Work in Power BI / Power BI Architecture
• A Brief History of Power BI

Desktop Installation & Service Overview

• Power BI Desktop Installation
• Building Blocks of Power BI
• Datasets, Visualizations, Reports, Dashboards, Tiles
• Power BI Desktop User Interface
• Fields Pane, Visualizations pane, Ribbon, Views, Pages Tab, Canvas
• Overview of Power Query / Query Editor – Extract, Transform & Load Data
• Connecting to Data Sources, Establish connection to the Excel source
• Importing Data into Power BI or Query Editor
• Transforming the Data using Power Query
• Model the Data using Power Pivot – Relationship View
• Creating New Measures and New Columns using DAX – Data View
• Visualizing the Data using Power View and Power Maps – Report View
• Saving and Publishing the Visuals or Reports
• Logon to Power BI Service
• View the Reports in PBI Service and Share the reports

Introduction to Power Query – Extract, Transform & Load

• Data Transformation, Benefits of Data Transformation
• Shape or Transform Data using Power Query
• Overview of Power Query / Query Editor, Query Editor User Interface
• The Ribbon (Home, Transform, Add Column, View Tabs)
• The Queries Pane, The Data View / Results Pane, The Query Settings Pane,
• Formula Bar
• Advanced Editor – Complete ETL Code
• Saving Your Work – Appling ETL Changes – Loading into Power Pivot Model
• Power Query Conceptually

Datatypes and Filters in Power Query

• Datatypes, Changing the Datatype of a Column
• Filters in Power Query
• Auto Filter / Basic Filtering
• Filter a Column using Text Filters
• Filter a Column using Number Filters
• Filter a Column using Date Filters
• Filter Multiple Columns

Inbuilt Column Transformations

• Remove Columns / Remove Other Columns
• Name / Rename a Column
• Reorder Columns or Sort Columns
• Add Column / Custom Column
• Split Columns
• Merge Columns
• PIVOT, UNPIVOT Columns
• Transpose Columns

In built Row Transformations

• Header Row or Use First Row as Headers
• 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

Append Queries / Union Queries

• Append Queries
• Append Queries as New
• Append 2 or more files individually with different queries
• Appending multiple files of same type from a folder using single query
• 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

Merge Queries / Join Queries

• Merge Queries, Merge Queries as New
• Default 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

Power BI Data Modeling – Relationship View

• Data Modeling Introduction
• Relationship, Need of Relationship
• 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
• Make Relationship Active or Inactive
• Delete a relationship
• Cross filter direction (Single, Both), Assume Referential Integrity

Enhancing the Data Model – DAX

• What is DAX, Calculated Column, Measures
• DAX Table and Column Name Syntax
• Creating Calculated Columns, Creating Measures
• Implicit Vs Explicit Measures
• Calculated Columns Vs Measures
• DAX Syntax & Operators
• DAX Operators
• Types of Operators
• Arithmetic Operators, Comparison Operators, Text Concatenation Operator, Logical Operators

DAX Functions Categories

• Date and Time Functions
• Logical Functions
• Text Functions
• Math & Statistical Functions
• Filter 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

DAXMath 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
• 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 theTree 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

Power BI Service Introduction

• Power BI Service Introduction, Power BI Cloud Architecture
• Creating Power BI Service Account, SIGN IN to Power BI Service Account
• Publishing Reports to the Power BI service, Import / Getting the Report to PBI Service
• My Workspace / App Workspaces Tabs
• DATASETS, WORKBOOKS, REPORTS, DASHBOARDS
• Working with Datasets, Creating Reports in Cloud using Published Datasets
• Power BI Datasets Live Connection using Desktop
• Working with Workbooks, working with Reports, Sharing Reports

Dashboards Development

• Creating Dashboards
• Pin Visuals and Pin LIVE Report Pages to Dashboard
• Advantages of Dashboards
• Interacting with Dashboards
• Adding Tiles to Dashboards
• Web Content, Image, Text Box,Video
• Formatting Dashboard
• Sharing Dashboard

Data Gateways

• Introduction to Data Gateways
• How Data Gateways work
• Connect to an on premise Data Source by using a Data Gateway
• Download Data Gateway
• Installing a Data Gateway
• Types of Gateways
• On-premises Data Gateway, On-premises Data Gateway (personal mode)
• Manage Data Gateway
• Add and Remove Administrators
• Add Data Source, Add or Remove Users to a Data Source
• Refresh On Premise Data
• Configuring Automatic Refresh using Schedules

Collaboration in Power BI using App Workspace

• Introduction to App Workspaces
• Create an App Workspace
• Add Members to App Workspace to Collaborate
• App Workspace Access
• Admin, Member, Contributor

Sharing Power BI Content using Apps and Content Packs

• Introduction to App
• Publish an App
• Update a Published App
• Manage Content in App
• Include in App, Exclude in App
• Sharing App
• Entire Organization, Specific individuals or group
• Un publishing an App
• Content Pack Introduction
• Create Content Pack
• Sharing Content Packs
• Specific Groups, My Entire Organization
• Selecting the Content / Items to Publish or Share

Row Level Security in Power BI

• Introduction to Row Level Security
• Row Level Security (RLS) with DAX
• Manage Roles, Creating Roles, Testing Roles
• Adding Members to Roles in Power BI Service
• Dynamic Row Level Security
• Creating Users Table and Adding to the Model
• Capturing users using UserName () or UserPrincipalName () DAX Functions

To see the full course content Download now