Power BI MCSA Certification Training Tips – Part 7: If statements and Conditional Columns

To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.

We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.

In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.

This topic applies equally to both Power BI and Excel Power Query and examines Power Query’s Conditional Column command and the use of if statements in the M language.

If Statements and Conditional Columns

In the M language, logical conditions are expressed by using if statements. (There is no IF() function in M.)

Syntax

The generic syntax of if statements is as follows:

If condition1 and/or condition2 then

Statements_if_true

else if condition3 and/or condition4 then

Statements_if_true

…

else

Statements_if_false

Points to note:

·       All the keywords (if, and, or, then, else) are lower case

·       Else and if are two separate words

·       No parentheses or commas are required

·       Whitespace is used purely for readability and is not required

Example

Imagine a scenario in which we want to connect to a file which is updated daily. However, on Saturdays, Sundays, Mondays, Tuesdays and Wednesdays it is saved as an Excel file, while on Thursdays and Fridays, it is saved as a CSV. (For the sake of simplicity, let us say that the path to the file will either be “C:\data.xlsx” or “C:\data.csv” and that the Excel file contains a single worksheet called “data”.

We could use the following if statement to cater for these two possibilities:

let

Routine = if Date.DayOfWeek(DateTime.Date(DateTime.LocalNow())) = 3 or

Date.DayOfWeek(DateTime.Date(DateTime.LocalNow())) = 4

then

let

Source = Excel.Workbook(File.Contents(“C:\data.xlsx”), null, true),

data_Sheet = Source{[Item=”data”,Kind=”Sheet”]}[Data]

in

data_Sheet

else

Csv.Document(File.Contents(“c:\data.csv”),[Delimiter=”,”]),

#”Promoted Headers” = Table.PromoteHeaders(Routine, [PromoteAllScalars=true, Culture=”en-GB”])

in

#”Promoted Headers”

Table.AddColumn

As well as entering if statements manually, the command Add Column > General > Conditional Column also generates an if statement. This command can be used where the business rules for creating the new column are fairly simple and do not warrant the use of a separate dimension table.

Like many of the options in the Add Column tab of the Ribbon, the Conditional Column command uses the Table.AddColumn function to create the new column.

Syntax

Table.AddColumn(

table as table,

newColumnName as text,

columnGenerator as function,

optional columnType as nullable type

) as table

·       Table
The table to which the new column is being added.

·       ColumnName
The name of the new column being created.

·       ColumnGenerator
The function which will be used to generate the values in the new column.

·       ColumnType
The optional specification of the data type of the new column.

Example

In the following illustration, a new column called “ISO” is being created, based on the possible values in the “Country” column.

And let us look at the syntax that is generated.

#”Added Conditional Column” =

Table.AddColumn(

#”Changed Type”,

“ISO”,

each if [Country] = “United Kingdom” then “GBP”

else if [Country] = “United States” then “USD” else if [Country] = “Canada” then “CAD”

else if [Country] = “Australia” then “AUD”

else if [Country] = “New Zealand” then “NZD” else null

)

·       Table
#”Changed Type” is a variable which contains a table reference.

·       ColumnName
The name of the new column will be “ISO”.

·       ColumnGenerator
The each keyword is used together with an if statement to provide an inline function.