Replacing blank (unmatched) values in a table with 'N/A' when no match found in lookup table

[sarah2_williams]

Hi all,

Hopeing this is a relativly simple one.

I have a data table with UK post code data included, and a lookup table that matches the Post code with the UK Region - as below:

The postcode in the original data is input by free text, so there are times when the customer has not entered a valid postcode (something we are looking at seperately)

This means that when I create a relationship between the Postcode in my data and my lookup table, to return the UK Region, anything that doesn't match shows as blank in the table in my report:

What I want to do is make it so that anything that doesn't match to the lookup values shows as 'Postcode not supplied by customer'

Any advise on the best way to do this is much appreciated 🙂

Thanks

Sarah

[MFelix · Accepted solution]

Hi @sarah2_williams 

Add a row with a code for that "Postcode not supplied by customer" then on Power Query do a merge between the two tables and where the field is null replace by that new code that you added that will give you a connection to that specific row when you do the relationships.