Forum Discussion
Checking multiple columns for value, ignoring null
Hello,
I am trying to replace the values of a column from the values of another column (if not null). I have 3 columns [Zip Code], [Zip Code.1], and [ZipCode] -- no row has null across for the 3 values. I want to get 1 column [NewPostal]. I am testing this first by creating a conditional column then will put that formula into the Replace.Values so i can replace the null values in the [Zip Code] column
My Test for the new column = Table.AddColumn(#"Expanded Zip Code to MSA", "NewPostal", each if [Zip Code] = null then [Zip Code.1] else if [Zip Code.1] = null then [ZipCode] else if [ZipCode] = null then [Zip Code] else [Zip Code])
Here i filtered to where [NewPostal] = null and you can see [ZipCode] has a value/not null
The first part of the if is probably the problem. If Zip Code and Zip Code.1 are both null, you will get null. You can use the Coalesce syntax to get your desired result by updating the each part with the below syntax
each [Zip Code] ?? [Zip Code.1] ?? [ZipCode]
It will return the first non-null value.
Pat
1 Reply
The first part of the if is probably the problem. If Zip Code and Zip Code.1 are both null, you will get null. You can use the Coalesce syntax to get your desired result by updating the each part with the below syntax
each [Zip Code] ?? [Zip Code.1] ?? [ZipCode]
It will return the first non-null value.
Pat