Table.Combine : How to handle null values?

[gtaranti]

Hi all!

I want to merge ALL columns except the first one on the depicted table.

The column count is dynamic. There maybe more than these.

I'm using the code :

= Table.CombineColumns(TransposedTable , List.Skip(Table.ColumnNames(TransposedTable ),1),  Combiner.CombineTextByDelimiter(" / ", QuoteStyle.None),"Merged")

to merge all columns except the 1st.

My probem is how to tackle null values, because in these rows I get :

Συγκατάθεση: Μέσω On-line φόρμας /  /  / Επεξεργασία απαραίτητη για την εκτέλεση σύμβασης 

(Sorry for the language, this is greek!)

Is there a function I can write to modify Combiner.CombinTextByDelimiter() in order to OMIT null values?

[gtaranti · Accepted solution]

A post in an other thread helped me to find my own solution.

Instead of Merging the columns at once, I added a custom column first that uses Text.Combine and at the same time removing NULLs, like that :

    AddedCustom = Table.AddColumn(Source ,  "Merged", (row) => Text.Combine( List.RemoveNulls(List.Skip(Record.FieldValues(row),1)), " / " )),

The community here is very helpful! 

[stretcharm]

Nice. I didn't know about that function.

[stretcharm]

You can use replace to change to a blank string

Enter null in the value to find.

= Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"YourColumn"})

[gtaranti]

Unfortunately replacing  null  with  blank string has the same effect.  

 "String1"  |  null  | "String2"   =>  String1 / / String2
 "String1"  |  ""  | "String2"   =>  String1 / / String2

[stretcharm]

Ah sorry, misread your request.

You can replace duplicate delimiters with a single one. If there is a chance you can have 3 then repeat the replace,

E.g.

/ /   to  /

If you have lots of colums and potential nulls you could pivot all but the first row, filter out nulls then unpivot back. However this is probably slower tha just replacing the duplicate delimiters.

[gtaranti]

Yes, I tried that, but there is also the problem with null in the first column.   (or multiple consecutive nulls).

This could be :  

null  |  "String1" | "String2"  =>   / String1 / String2

It's tough to get them all with replacements afterwards.

If I can't find a solution for omission of nulls DURING Table.Combine, I will go this path (i.e. replace all multiple delimeters and remove first & last ones)

Thanks..