Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago

Merge columns with null

Hi experts!

I have a 3 columns with text values. Some cells are empty / value = null.

I added a merged column with a seperator "_". Now, if a column is null I don't get the seperator "_".

Instead of Value1__ (2 x "_") I just get Value1.

 

How can I get a seperator "_" if the column / value is null?

Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    If separator is same between all the columns, then Text.Combine will work

    Text.Combine({[Column1],[Column2],[Column3]}, "_")

    if separators are different between columns, then use Replacer.ReplaceValue. In below example, I am using _ and ** as separators

    Replacer.ReplaceValue([Column1], null, "") & "_" & Replacer.ReplaceValue([Column2], null, "") & "**" & Replacer.ReplaceValue([Column3], null, "")