Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Simply Conditional

19 August 2020

Welcome to our Power Query blog. This week I look at a method to conditionally replace values efficiently.

 

I have the following tent data:

I want to add a prefix to the data in my Awning column, which is dependent on the value in that column, viz.

  • Blank: N/A
  • Standard or Dining: Fixed
  • Budget: (No prefix)
  • Package or Kids: Variable.

I begin by extracting my data to Power Query using ‘From Table’ on the ‘New Query’ section of the ‘Get & Transform’ section on the Data tab.

I accept the defaults.

I could use a custom column to achieve my required result:

The M code I have used is:

if [Awning] is null then "N/A "

else if [Awning] = "Package" or [Awning]= "Kids" then "Variable " & [Awning]

else  if [Awning] = "Standard" or [Awning] = "Dining" then "Fixed " & [Awning]

else [Awning]

When I click ‘OK’, I get my new column:

Taking the ‘if’ statement I used, I can do this another way, by using 'Replace values’.

I am going to start by creating a simple ‘Replace values’ statement by replacing null with ‘N/A’:

When I click ‘OK’, the null values are replaced.

More importantly, the M code for this step has been generated:

= Table.ReplaceValue(#"Changed Type",null,"N/A",Replacer.ReplaceValue,{"Awning"})

I need to change this so that I can apply a change to every row, so instead of null, I am going to use ‘each [Awning]’:

= Table.ReplaceValue(#"Changed Type",each [Awning], "N/A",Replacer.ReplaceValue,{"Awning"})

I am now going to replace ‘N/A’ with the ‘if’ statement I used earlier, prefixed by ‘each’ as I am applying it to each row.

= Table.ReplaceValue(#"Changed Type",each [Awning], each if [Awning] is null then "N/A "

else if [Awning] = "Package" or [Awning]= "Kids" then "Variable " & [Awning]

else  if [Awning] = "Standard" or [Awning] = "Dining" then "Fixed " & [Awning]

else [Awning]

,Replacer.ReplaceValue,{"Awning"})

When I click the tick next to my amended step, I can see the results.

I have applied a conditional change in my column without the need for an extra column.

 

Come back next time for more ways to use Power Query!

Newsletter