Forum Discussion
Combine columns if not null or empty
Hi there,
I have some difficulties to combine text columns with the result I want. If someone could help me to point me to the right direction :)
So I have this table :
Col1 Col2 Col3 Col4 Std Combine Wanted
| A | B | C | D | A & B & C & D | A & B & C & D |
| B | & B & & | B | |||
| A | C | A & & C & | A & C | ||
| A | A & & & | A | |||
| B | D | & B & & D | B & D |
if I combine the columns with the std feature , you can see that empty ones are surrounded by unnecessary "&" char.
what I would like is the "Wanted" column result.
My ideas were to create a list of not empty columns and combine them but I'm not good enough to create a custom function for that...
Thanks.
Good catch Marcel but I have other columns :)
Thanks for your help, you pointed me to the right way !
here the solution :
= Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))
Thanks again
Pleased you solved your own issue.
I'd rather had shortened the code a bit:
= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
19 Replies
If you have no other columns, you use the query editor to add a custom column with formula:
= Text.Combine(List.Select(Record.FieldValues(_), each _<> "" and _ <> null)," & ")
Good catch Marcel but I have other columns :)
Thanks for your help, you pointed me to the right way !
here the solution :
= Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))
Thanks again
Pleased you solved your own issue.
I'd rather had shortened the code a bit:
= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
My solution
Text.Combine(List.Select({[ddd_1],[telefone_1]}, each _<> "" and _ <> null)," - ")
