Custom column to combine two Colum returns null if either of the Colum contain null value

[kannanAhammed]

Hi,

I created a custom column to concatinate two fields from transformation -> Add column -> custom column

The formula is  custom = [A] & [B]. If either if the colums value is null the result is null.

The null should be allowed only if both are null. how do i do this ?

[v-cazheng-msft]

Hi @kannanAhammed ,

You can directly use the expression custom=[A]&[B] to create a Calculated column, and you can get a column concatenating these two fields. If both of the fields are null, the result will be null.

The result looks like this:

Best Regards,

Caiyun Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @kannanAhammed 

Your question isn't clear because you say 

• If either if the colums value is null the result is null.
• The null should be allowed only if both are null.

The first one means the result is null if either A or B is null.

The second one means the result is null only if A and B are null.

You can't have both.  If it's null only when A and B are null then the first condition is invalid.  Which is it?

I imagine the other answers will give you a solution but just needed to point out the question isn't logically clear.

Regards

Phil

[CNENFRNL · Accepted solution]

Hi, @kannanAhammed , Null Coalescing Operator (??) does the trick

Table.AddColumn(#"Replaced Value", "Custom", each ([A]??"") & ([B]??"") & ([C]??""))

Just for fun, let's say, if you want to combine all columns (more than 10 columns) in a row, you might use the following code,

Table.AddColumn(#"Replaced Value", "Custom", each List.Accumulate(Record.ToList(_), "", (s,c)=>s&(c??"")))

[amitchandak]

@kannanAhammed , Try like

if [A] <> null && [B] <> null then [A] & [B] else if [A] <> null then [A] else [B]