Shape Your Data into a Data Model with M

Power BI Advanced
© 2019 Microsoft. All rights reserved.

Please message Sami with any questions, concerns or if you
need assistance during this workshop.
Housekeeping
SEND QUESTIONS TO
SAMI. SHE WILL SEND
TO NORM TO REVIEW
DURING BREAKS.
PLEASE MUTE YOUR
LINE!
WE WILL BE APPLYING
MUTE.
THIS SESSION WILL BE
RECORDED.
WE WILL SHARE
SLIDES WITH YOU.
TO MAKE
PRESENTATION
LARGER, DRAW THE
BOTTOM HALF OF
SCREEN ‘UP’.

CCG Analytics
We bring great People together to do
extraordinary Things
DATA ANALYTICS STRATEGY
Working with CCG is like working with extended team members. Consultants become an
integral part of the work bringing expertise for cutting edge design and development.
- CIO, HCPS

AGENDA
Introductions and Overview
Basic Data Modeling
Getting Started with M (Power Query Language)
Demo: Import and Basic Transform
Advanced M Transformations
Demo:Complex Transformation
Variable, Parameters and Functions
Demo:Creating Parameters and Paths
Optimization Techniques
Wrap-up & Questions

OBJECTIVES
By the end of this course, you will be able to use Power BI Desktop to import and
shape data from a variety of different sources. Specifically you will be able to:
• Understand the Power BI Desktop
data model, its components and
most effective schemas
• Gain an understanding of the Power
Query M language
• Import data from a variety of
sources (including XLS and CSV
files)
• Create queries using toolbar
navigations and Advanced Editor
• Understand parameters
• Organize queries using folders

MODULE OBJECTIVES
Objectives:
• Understand the basic architecture of PBI Desktop
• Understand Power BI modeling terminology

(Prep data for Data Model)
Power BI Desktop Data Flow
Close &
Apply

• Improves understandability of the data
• Increases performance of dependent processes and systems
• Increases resilience to change
What is a Data model?

Components of a data model – Fact Table
Basic Data Modeling
Fact
Table

Components of a data model – Dim Table
Basic Data Modeling
Dim TableDim
Table
Dim
Tables
Dim
Table
Dim
Tables

Components of a data model - Relationships
Basic Data Modeling
Relationships
Relationships
Relationships

Data Model Brings Facts and Dimensions Together
Basic Data Modeling

Flat or Denormalized schema
Basic Data Modeling

Star Schema
Basic Data Modeling
1Many1 1

Snowflake Schema
Basic Data Modeling
• Center is a Star schema
• Fact table in middle
• Surrounded by Dims
• Dims “snowflake” off of
other Dims
• If you have many, it looks
like a ‘Snowflake’
• Dim or Fact tables can be
the “Many” side of the
relationship
Snowflake
Facts DimsDims Flake

Granularity & Multiple Fact
Basic Data Modeling

Data Types
• Any – You should never see this in a data model. Bad things can happen!!
Set your
Data Types
in the
Query Editor
Set your
Data Formats
($ %, etc)
in the Data Model

Designing good data models
RAM is precious !!!!!
remove it
• Sort columns

Getting Started with M
(Power Query Language)

MODULE OBJECTIVES
Objectives
• Understand the basics syntax of the M Language
• Understand function & keyword structure
• Understand how to connect to data
• Understand the basic structure of the Advanced Editor
Agenda
• Power BI M Language Overview
• Basic Data Import
• Introduction to Text functions
• Create and Apply Degenerate Dimensions

M has amazing capabilities to transform data to optimize it for
the data model
Why M?

How do I use M?
Three ways to
write M
Simple Advanced

Key Concepts of M syntax
M is the key to data
transformation in Power BI
Many transforms are just a
click away on the Ribbons
Turn on Formula Bar to see
M syntax generated by clicks
View full syntax in Advanced
Editor
M is Column Based
M is CaSe SeNsItIvE!

Key Concepts of M syntax
M Formulas
 Syntax:
 Objects:
 Actions (Camel Case):
M helper Words
 let in each
 if then else
 true false error
 and or not
 try otherwise
 as is meta section shared type
M # Key Words
 #date(2016,01,01)
#date([Year],[Month],[Day])
 #datetime(2016,02,26, 09,15,00)
 #time(09,15,00)
 #datetimezone(2013,02,26, 09,15,00, 09,00)
 #table({"X","Y"},{{0,1},{1,0}})
 #duration(0,1,30,0)
 #binary #infinity #nan #sections
#shared
Key Punctuation
 [Column] - To reference a Column
M is CaSe SeNsItIvE! Power Query Formula Language

Text Functions
M Excel
Position 0 1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10 11
My Column A B C 1 2 3 4 5 x y z A B C 1 2 3 4 5 x y z
Return "ABC" Text.Start([MyColumn], 3) LEFT([MyColumn], 3)
Return "234" Text.Range([MyColumn], 4, 3) MID([MyColumn, 5, 3)
Find position of "5" PositionOf([MyColumn], "5") = 7 SEARCH("5", [MyColumn]) = 8

Text Functions
M Function Syntax Excel Syntax M Output
Text.Start([MyColumn], 3) LEFT([MyColumn], 3) ABC
Text.End([MyColumn], 4) RIGHT([MyColumn], 4) 5xyz
Text.Range([MyColumn], 4, 3) * MID([MyColumn], 5, 3) ** 234
Text.Range([MyColumn], 4) * MID([MyColumn], 5) ** 2345xyz
Text.StartsWith ([MyColumn], "abc") IF(LEFT([MyColumn], 3)=“abc” , TRUE(), FALSE()) FALSE (Excel -> TRUE)
Text.StartsWith ([MyColumn], “ABC") IF(LEFT([MyColumn], 3)=“ABC”, TRUE(), FALSE()) TRUE
Text.Length([MyColumn]) LEN([MyColumn]) 11
Text.Contains ([MyColumn], "123") IF(ISNUMBER(SEARCH(“123", [MyColumn])),TRUE(), FALSE()) ** TRUE
Text.PositionOf ([MyColumn], "5" ) * SEARCH(“5", [MyColumn]) ** 7 (Excel -> 8)
*
**

if … then … else
• Basic Syntax:
if <test if true> then <result if true> else
<result if false>
• if’s Can be chained or nested
• No parentheses () are required, except
• When if test or results have multiple conditions:
(condition 1 and condition 2)
(condition 1 or condition 2)
(condition 1 or (condition 2 and condition 3))
• If multiple if’s are used ensure you have the
same number of “if”, “then”, “else”
Chained example (3 each of if, then & else)
=if Text.EndsWith([Column2],"Key") then [Column2] else
if [Column2] = "TotalCost" then "Cost $" else
if [Column2] = "SalesQuantity" then "Sales #" else null
Nested example (3 each of if, then & else)
=if Text.EndsWith([Column2],"Key") then
if [Column2] = "TotalCost" then "Cost $" else
if [Column2] = "SalesQuantity" then "Sales #" else null
else “Item #"
Multiple conditions example (1 each of if, then & else)
=if (Text.Length([Column2]) = 3 or Text.Length([Column2]) = 2 )
then true else false
Simple example (1 each of if, then & else)
=if [Column2] = "Key" then [Column2] else “Other”

if … then … else using Conditional and Custom

if … then … else using Columns From Examples

Calculated Column in DAX
Calculated Column

Demo:
Import and basic
transform

We report on Actual & Budget Revenue; Units, Cost & Gross
Profit
• VtB – Actual Variance to Budget
• YoY – Year over Year
• MoM – Current Month vs Prior Month
• One Division percent of Total
• By Product Category
• By Product Segment
• By Campaign
• By Customer
Who are we?

How do you turn a common Excel report into a Data Model
• One Excel sheet with multiple columns
• What am I counting or aggregating (FactTable)
• By Product Category
• By Product Segment
• By Campaign
• By Customer
Denormalized to a Data Model

You have access to the data for your model, but it is not in the
right “Shape”
Import Data Walkthrough

If the Source mirrors the Table required by the Model, import
multiple tables at once
Demo: Import Multiple Tables from a Single Source
CampaignDim
GeoDi
m
DateDi
m
Sales
ProductDim

This is the current state of our data model
Demo Exercise

Extract unique combinations of Categories & Segments from the
Product Dimension
Demo: Create CatSegDim (Category Segment)
Add Index

Apply new CatSegID field to the Product Dimension
Demo: Update Product Dim
Replace with
Index

Using a garbage column to fill out CustomerDim
Demo: Create Customer Dimension

Demo: Advanced Editor

MODULE OBJECTIVES
Objectives
• Understand M transformations
• Understand Merge and other combining queries
• Understand how to use multiple queries in an advanced transformation
• Using Data Profiling
Agenda
• Power BI M Key Transformations
• Power BI M Merge Types
• Create BudgetFact using multi-query approach

Key Transformations
Transpose
 Syntax:
Previous
Pivot Column
 Syntax:
Previous Previous
 The values in [Color] column are
converted to headers
 The values in the [Value] column are
filled in where applicable
Unpivot Columns
 Syntax:
Previous
 The unpivoted column headers
become the column [Attribute]
 The unpivoted values become the
column [Value]
Note: Previous => Name of the previous step in the query

Join Kinds – Merge types
JoinKind.LeftOuter
JoinKind.RightOuterJoinKind.Inner JoinKind.RightAnti
JoinKind.LeftAnti
Note: One or Multiple columns can be used to create the join

Join Kinds – Other Combining Queries
• Both queries rerun with each execution
“Stack” records of two (or more) queries
• Both (all) queries rerun with each execution
• Like column headers must be named the same
• Non-matching columns will be added to the right as extra
colunns

Fuzzy Merge allows you to apply Fuzzy
Matching algorithms when comparing
columns and try to find matches across
tables being merged.
Fuzzy Merge

Data Profiling allows you to easily and
quickly understand data distribution
Data Profiling

Objective: Transform a “wide” Budget file with three extra rows on top and
a three row header into a usable BudgetFact table
Demo: Create Budget Fact

Import CSV, remove blank rows, and rename query to BudgetFact_Data

Create a Duplicate, and Extract just the Header Rows into a separate query

Transpose headers, combine into a single column, and transpose back

Append BudgetFact_Data to the bottom of the headers, then remove the
extraneous header rows, finally set first row as header

Unpivot Forecast Columns, Split and Rename them, then set data types

Merge in CatSegID and remove extraneous columns

Variable, Parameters and
Functions

MODULE OBJECTIVES
Objectives
• Understand what Variables and Parameters
• Understand Custom Functions
• Understand the benefits to parameterizing your queries
• Understand what “Enter Data” is and how it can be used
• Understand how to organize your queries using folder groups
Agenda
• Power BI M Variables & Parameters
• Enter Data
• Leveraging Variables & Parameters
• Create Change Log

Variable and Parameters
M Variables
 Create with a new blank query
 Query name is the Variable name to
use in other queries
M Parameters
 Parameters can be any VALID data
type
 The most common are hard keyed
single, switchable values
 The values list for a parameter can
also come from a query that is
formatted as a LIST
 Parameter used as Source Input
Parameter Usage
 SQL Server
 For Database Name
 For Server (Switch Dev, Test, Prod)
 “Where Clauses” in SQL Queries
 To pull all Orgs, or just a single
one
 SharePoint Team site names
 If they are based on a template,
you can easily share queries
 File Paths and File Names
 Have a single place where you
need to make a change

Custom Functions
Custom Functions help you re-
use code

Enter Data
User Enter Data to add a table on the fly. They make great mapping tables.
<- To add additional rows or columns
later, click the gear icon.

Demo:
Creating Parameters and
Paths

All of the queries in our file are dependent on the file being in a specified path
Demo: File Source considerations

Create Parameters
Demo: Create Parameters

Use blank query to create Dynamic Path
Demo: Dynamic Path to Excel Source File

Use the Actuals_Path query as a variable in other queries
Demo: Dynamic Path to Excel Source File

Follow a similar patter to make the Budget CSV file dynamic
Demo: Dynamic Path to CSV Source File

Create a function to get number of days from start of year
Demo: Custom Function

Use Enter Data to create a Change Log
Demo: Create a Change Log

Use Folders to Group Queries
Demo: Organize your Queries

Contact Support
Report Errors, Issues – Support.PowerBI.com
Resources use presentation mode to click the hyperlinks
 Community.PowerBI.com – Community Forum
 Data Stories Gallery – Get inspired with Data Stories by other Power BI users
 R-Visuals Gallery – Get inspired by others use of R for analyzing their data
 Visuals.PowerBI.com – Custom PBI visuals and R visuals you can download and use in your story
 Power BI Blog - weekly updates
 User Voice for Power BI – Vote on (or submit) your favorite new ideas for Power BI
 Issues.PowerBI.Com – log issues with the community
 Guided Learning Self Service Power BI training
 DAX Formula Language – syntax for DAX
 DAX Patterns – Great website to learn new patterns for the DAX Language
 Power Query Formula Language – syntax for the “Query” language
Power BI Support Resources
Instructors:

Shape Your Data into a Data Model with M

More Related Content

What's hot

Similar to Shape Your Data into a Data Model with M

More from CCG

Recently uploaded

In this document

Shape Your Data into a Data Model with M