Forum Discussion
Solved
Merge selected columns ignoring null?
Following the solution provided here: https://community.powerbi.com/t5/Desktop/Table-Combine-How-to-handle-null-values/td-p/370001# I've been able to sucessfully merge the columns ignoring nu...
Hi, thanks for your help!
I figured it out, the solution was to simplify the formula from the link. Simply becoming:
= Table.AddColumn(#"Filtered Rows1", "Merged", each Text.Combine(List.RemoveNulls({[Name.1.1], [Name.1.2], [Name.1.3], [Name.1.4], [Name.1.5], [Name.1.6], [Name.1.7]}), "/"))
Thanks unfortunately I don't have access to Power BI so I can't access this!
hello CPrince ,
you should do it like this.. dynamically, nice, smart and crisp
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYicgBiIXIHYFYjcgdleK1YlWMkJS4IxVhTFEK24FJhAFOOVNoVZAFaDKxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name.1.1 = _t, Name.1.2 = _t, Name.1.3 = _t, Name.1.4 = _t, Name.1.5 = _t, Name.1.6 = _t, Name.1.7 = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"ID"}, {{"Count", each Text.Combine(_[Value],"/"), type text }})
in
#"Grouped Rows"If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hi, thanks for your help!
I figured it out, the solution was to simplify the formula from the link. Simply becoming:
= Table.AddColumn(#"Filtered Rows1", "Merged", each Text.Combine(List.RemoveNulls({[Name.1.1], [Name.1.2], [Name.1.3], [Name.1.4], [Name.1.5], [Name.1.6], [Name.1.7]}), "/"))Hello
thats okay. The only thing is that this version is not dynamic, meaning, that a column is added, it's not longer considered.
All the best
Jimmy
Thanks for this! I've been looking for a dynamic solution for a while and this really does it.
