Power Query: Simply Conditional

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:

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:

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]’:

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.

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!