PowerQuery Custom Column Lookup Calculation that handles blank values

Hi jawnne,

You could probably use 'try' 'otherwise' to get around this.

https://learn.microsoft.com/en-us/power-query/error-handling

Hi jawnne 

You're using an M code custom column in Power Query to replicate a DAX-style lookup, and your current formula works well when the source_column contains values. However, when the source_column is blank (null), the formula throws an error because it still tries to perform the lookup on a null value. To handle this gracefully, you need to add a conditional check before attempting the lookup. The idea is to first check if the value from source_column is null; if it is, the formula should return null (or blank), and if not, it should proceed with the lookup. You can structure your formula like this:

if [source_column] = null then null else (let variable = [source_column] in try (Table.SelectRows(lookup_table, each [lookup_value] = variable)){0}[lookup_value] otherwise null)

This updated expression first checks if source_column is null and returns null immediately if so. If not, it proceeds to perform the lookup using Table.SelectRows as you originally intended. Wrapping the lookup in a try ... otherwise block ensures that even if the value isn't found in the lookup table, it won’t result in an error—it will simply return null. This approach provides a safe and robust way to mimic a DAX lookup in Power Query, handling both filled and optional (blank) fields without failing.