DATA ANALYSIS WITH POWERBI

OBJECTIVES:

After completing this training, you will be able to:

• Describe key concepts in business intelligence
• Describe key concepts in data analysis
• Describe key concepts in data visualization
• Describe the rationale for self-service BI
• Describe considerations for self-service BI
• Import data via queries
• Create a dashboard
• Use queries to transform data into usable, meaningful tables
• Create a data model with relationships, custom sorting, a date dimension table, etc
• Use Excel as a Power BI data source
• Use databases as a Power BI data source and describe the R language
• Create Power BI Desktop queries
• Shape data and Combine data
• Set up interactions between charts
• Write simple DAX queries
• Create calculations and measures
• Use Power BI Desktop to create interactive data visualizations

TARGET AUDIENCE

The Microsoft Power BI Training is for all the professionals who are passionate about business intelligence, data visualization, and data analytics. It is best suited for: Business Analysts, financial analysts, Business Intelligence managers, economists, Statisticians and Analysts, Data Scientists, and Project Managers

COURSE OUTLINES

Introduction

• A history of Business Intelligence
• The development of Self Service
• Business Intelligence
• Introducing Power BI
• How does Power BI fit in?
• Introducing the three main tools – Power BI Service, Power BI desktop & Power BI Mobile
• When to use Power BI Desktop
• Free versus Pro Signing up to the Power BI online service
• Which sources of Data can I use in Power BI Desktop?

Power BI Desktop

Introducing Power BI Desktop

• Which version – 32 or 64 bit
• Starting Power BI Desktop
• Desktop interface
• Ribbon and QAT
• Sign-in and collapse ribbon Navigation
• Getting Data and available sources

The BI Process

• The Business Intelligence process
• How Power BI relates to the standard BI

Data Source and loading

• Introduction to the data source used in this course
• Creating Tables of Data
• Import data from Excel
• Import data from Web
• Renaming Queries
• Grouping Queries
• Including and Excluding Queries in report refresh
• Enabling or disabling Query loading
• Applying Queries
• Viewing Query dependencies
• Append Queries
• Merge Queries
• Merge Query Types
• Cleaning the resulting query

Using Data from an Excel Workbook

• Understanding your data
• Importing data from an Excel worksheet
• Excel Tables
• Importing table data from the Excel file
• Saving in Power BI Desktop

Visualizations

• Overview of Visualizations
• Creating and working with Visualizations
• Creating a Bar Chart Visualization
• Creating a Column Chart Visualization
• Creating a Line Chart Visualization
• Some basic formatting of Visualizations
• Focus Mode
• Creating a Card Visualization
• Giving Cards some impact
• Creating a Table Visualization
• Creating a Matrix Visualization
• Creating a Bubble Map Visualization
• Creating a Filled Map Visualization
• Creating A Gauge Visualization
• Creating a Treemap Visualization
• Choosing the right Visualizations
• Card Visuals and Filters
• Create variance measures
• Using variance measures in a Waterfall chart
• Create drill down time series chart
• Drill down chart interaction
• Create drill down bar chart
• Creating What if Parameters
• Using What if parameters in a chart
• Enabling Q & A in Power BI Desktop
• Create a visual with Q & A
• Using Bookmarks in Power BI Desktop
• Bookmarks to filter data
• Bookmarks to change visuals
• Add buttons to access bookmarks

Working with Formats & Visualizations

• Axes
• Data Labels and titles
• Sizing & Alignment
• Data colours, backgrounds, and Borders

From Visualizations to Reports

• Adding Pages to your Reports
• Renaming Pages
• Re-using Visualizations
• Building a Narrative in your report
• Editing & Reading Modes
• Pie Chart Visualizations

Text B Text boxes Shapes

• Introducing Visual Interaction
• Turning Interaction On or Off
• Interacting by Filtering, Highlighting or None

Working with Filters

• Creating a Slicer
• Filtering in the Visualization pane
• Creating a Visual Field filter
• Creating a Top N Filter
• Adding a page level Filter
• Creating pages from page level filters
• Add a report level Filter

Working with Desktops Files

• Introduction to PBIX files
• Credentials
• Save a Desktop file as pbix
• Power BI Templates
• Refresh data from a template
• Generate a data source error

Q & A

• Making Visualizations with natural language queries
• Enabling Q&A in Power BI Desktop
• Create a Visual using Q&A
• Add categories to the Q&A expression
• Add conditions to Q&A
• Add grouping to the Q&A
• Specify a visual type

Refreshing your Data

• Refreshing your Datasets
• Contents of a Power BI pbix file
• Contents of a template file
• User profile content

Power Query (Query Editor)

• Connecting to a database
• Building a Data Model
• Using Query Editor Formatting and Properties of Data types and their uses
• Loading Individual Tables
• Creating Relationships
• Deleting Relationships

Using Custom Visuals

• Custom Visualizations Gallery
• Installing a Custom Visualization
• Word Cloud
• Chiclet Slicer
• World Flag Slicer
• Count Down Timer Cards with States
• Scroller
• Enlighten Aquarium

Access Database & Power BI

• Importing an Access Database
• Data Relationships
• Stacked Column Chart
• Sorting an Existing Visualisation
• Adding Depth to your Report
• Focusing on Key Points of Interest
• Calculated Columns
• Adding a Calculated Column
• Displaying calculated columns in a visualization
• Using a Calculated column in a calculation

Synchronize Slicers

• Introduction to Slice
• Sychronization
• The Sync Slicers pane
• Slicers Group

Report Themes

• Introduction to Themes
• Theme Gallery
• Do it yourself themes
• Colour codes
• JSON file
• Theme generators

Data Model & Transformation

• The Power Query process
• Data transformation principles
• Manual data clean-up example
• Removing rows from Tables
• Using first row as headers
• Filtering data
• Detect and change data type
• Filling Null areas with values
• Trimming text
• Replacing values
• A demonstration of how Power Query helps with efficient use of data query
• Dimension and Fact Tables
• Data Model considerations
• Common Data Models

Date Transformations

• Disabling Auto Date/Time
• Extracting year
• Extracting quarter
• Extracting month
• Extracting date
• Filter dates

Power Query UI

• Queries Area
• Data section
• The Transformations Ribbons
• Query Settings Area
• M Language Area

Data Modelling

• Introduction to data modelling
• Relationship view
• Auto detecting relationships
• Cardinality and Cross Filtering
• Creating relationships through
• Manage relationships
• Changing Cross Filtering
• Deleing relationships
• Drag and drop to create relationships

Column Transformations

• Adding Custom Columns
• Renaming Query Steps
• Changing Text Data Column Case
• Duplicating Columns
• Renaming Columns
• Splitting Columns
• Creating Conditional Columns
• Creating Index columns
• Reordering Columns
• Using the Navigate to Column tool
• Creating Date columns from
• Date/Time Columns

DAX

• An introduction to DAX
• Comparing M Language to DAX
• Comparing Measures to Calculated
• columns
• Creating a simple SUM measure
• Creating a Calculated column using
• the IF function
• Filter Propagation
• Filter Context
• Advice when creating DAX
• Third party tools to assist
• Measures Table
• Create a Table for Measure

Functions

• Functions like Excel FORMAT function
• The ‘X’ functions
• Row Context
• SUMX function
• AVERAGEX function
• CALCULATE function
• DAX Logical Operators
• CALCULATE with multiple filters
• Using measures in Visuals
• Create a measure using the FILTER function
• ALL and ALL SELECTED functions

Time   Intelligence Functions

• The built-in Date Hierarchy
• Functions that return a single date
• Using the FIRSTDATE function
• Functions that return a table of dates
• Using the DATEADD function to create a Month on Month measure

Power BI Templates & Parameters

• Introduction to parameters in templates
• Enabling parameters in queries and transformations
• Creating a template file based on existing query and report
• Add a transformation parameter to the template

Connect   Excel   and Power BI Desktop

• Introduction to Power BI and Excel
• Ability to work with Desktop and Excel
• Analyse a Power BI Desktop data model in Excel
• Create a Power Pivot data model in Excel
• Add a KPI based measure to the data model
• Import the Excel data model to Powerbi Desktop including the KPI

Extra Processes

• Comparing Multiple Scenarios through Waterfall Charts
• Using the Group By feature

Dynamic Web Queries Using Parameters

• Importing a web query using parameters
• Creating a custom function
• Defining a List to create a new Query
• Invoking custom functions
• Using Error checking processes
• Cleaning queries
• Create measures and visualize queried data

N:B: All exercises and project files used on the course will be available for class use and take home.

Other Dates

22Sep