Transforming Data Analysis with Power BI

Day 1

9.00am

What Is Excel Power Query?

·      Extracting, Transforming and Loading (ETL) data

·      Power Query vs “normal” Excel

·      Importing data with Power Query

·      Sources of data:

¨     Excel

¨     CSV/Text

¨     SQL Database

¨     Web

¨     Others

·      Query Editor ribbon, Navigator pane & Preview grid

·      Query settings panel - recording of transformation steps

Excel Data Cleaning & Formatting

·      Data types: Number, Date, Text, True/False

·      Remove Columns, Remove Other Columns & Choose Columns

·      Removing duplicates from a data set

·      Replacing values and errors

·      Null values: Replace, fill or filter out

·      Removing whitespaces and non-printable characters

Excel Data Transformation Techniques

·      Transpose, Unpivot Columns and Pivot Columns:

¨     What are the differences? When to use them?

·      Text transformations:

¨     Extract texts before delimiter

¨     Extract texts after delimiter

¨     Add Prefix/Suffix

¨     Convert texts to uppercase/lowercase

Overview of Power BI Desktop

·      Intro to the Power BI Desktop interface

·      Review of Power BI Desktop visuals

·      Choong the right visual

·      Building your first report

·      The importance of implementing a plan on paper first Draft your first Power BI Data Model Build Plan.

Framework for Building a Data Model

·      What is a Data Model

·      Big picture view of your reporting needs

·      Reviewing Step-by-step Methodology

·      How to create a relationship

·      Secrets for optimizing your report model

Building a Data Model in Power BI Desktop

·      Implementing your Data Model Build Plan

·      Loading data into Power BI Desktop

·      How to create relationships

·      Review of Facts, dimensions and Relationships

·      Use of implicit DAX measures

·      Build reports in Power BI (Simple Visualizations using Implicit Measures)

Day 2

9.00am

Review of the DAX Formula Language

·      Why is DAX so great for Analysts

·      How DAX Works, the Vertipaq Engine

·      The 3 Core DAX Measures

·      DAX functions vs Excel Functions

·      Introduction to CALCULATE - Why it is the most important DAX Function

·      Overview of the Filter Context

Time Intelligence Calculation in DAX

·      Examples The concept of a calendar

·      Year to Date (YTD)

·      Same Period Last Year (SPLY)

·      Same Period Last Month (SPLM)

Utilizing Quick Measures

·      Using Quick Measures

·      Interpreting Quick Measures

·      Modifying Quick Measures

Power BI Standard & Custom Visualisations

·      Building Standard Visualisations

·      Techniques for deciding on a Visual

·      Introduction to Custom Visualisations

·      Formatting Visuals

·      Comparative Analysis

Practice - Case Study Application

·      Reviewing your Case Studies

·      Drawing up your Data Model paper Plans

·      Transforming your Data with Power Query

·      Building your Data Model

·      Incorporating simple DAX Formulas to your Data

·      Incorporating Quick Measures to your Reports

·      Connecting budget information into your model

·      Carrying our Variance Reporting and Analytics

·      Building a Dashboard