Forum Discussion
How to Merge Ignoring the NULL cells
This may be a simple one but I am pretty new to M/DAX. I have a dataset like below. I want to merge the columns but when I do that with a Delimeter or Space or etc., It is giving that character even if the cells are null.
I am merging all these columns and the Result is coming as ABC||BCD|EFG|||GHI||
I want the output of merge to be ABC|BCD|EFG|GHI ignoring the null cells.
| Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | Col 6 | Col 7 | Col 8 | Col 9 | Col 10 | Col 11 |
| ABC | BCD | EFG | GHI |
Anonymous
You first Merge the selected columns then, go into the codes of the New Column and add the following:
Text.Combine(List.Select(
{[Col 1], [Col 2], [Col 3], [Col 4], [Col 5], [Col 6], [Col 7], [Col 8], [Col 9]},each _<>""), "|")
You paste the below code in a new blank query in the advanced editor and check the step.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRAiInZxcIw9XNHcKAIHcPT6VYnWgQszglrTgFRBcXg0mIgnIgLoIwK5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Col 2" = _t, #"Col 3" = _t, #"Col 4" = _t, #"Col 5" = _t, #"Col 6" = _t, #"Col 7" = _t, #"Col 8" = _t, #"Col 9" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col 1", type text}, {"Col 2", type text}, {"Col 3", type text}, {"Col 4", type text}, {"Col 5", type text}, {"Col 6", type text}, {"Col 7", type text}, {"Col 8", type text}, {"Col 9", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select( {[Col 1], [Col 2], [Col 3], [Col 4], [Col 5], [Col 6], [Col 7], [Col 8], [Col 9]},each _<>""), "|")) in #"Added Custom"
3 Replies
No this post is different. I just want to merge columns but excluding null cells. How can I do that. Kindly help CNENFRNL
- Fowmy
Super User
Anonymous
You first Merge the selected columns then, go into the codes of the New Column and add the following:
Text.Combine(List.Select(
{[Col 1], [Col 2], [Col 3], [Col 4], [Col 5], [Col 6], [Col 7], [Col 8], [Col 9]},each _<>""), "|")
You paste the below code in a new blank query in the advanced editor and check the step.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRAiInZxcIw9XNHcKAIHcPT6VYnWgQszglrTgFRBcXg0mIgnIgLoIwK5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Col 2" = _t, #"Col 3" = _t, #"Col 4" = _t, #"Col 5" = _t, #"Col 6" = _t, #"Col 7" = _t, #"Col 8" = _t, #"Col 9" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col 1", type text}, {"Col 2", type text}, {"Col 3", type text}, {"Col 4", type text}, {"Col 5", type text}, {"Col 6", type text}, {"Col 7", type text}, {"Col 8", type text}, {"Col 9", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.Select( {[Col 1], [Col 2], [Col 3], [Col 4], [Col 5], [Col 6], [Col 7], [Col 8], [Col 9]},each _<>""), "|")) in #"Added Custom"
