Forum Discussion
Table.Combine : How to handle null values?
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?
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! :smileyvery-happy:
6 Replies
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! :smileyvery-happy:
Nice. I didn't know about that function.
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"})Unfortunately replacing null with blank string has the same effect.
"String1" | null | "String2" => String1 / / String2 "String1" | "" | "String2" => String1 / / String2
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.
