Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 null. However, this covers a merge for entirity of the sheet. I'd only need a selected amount of columns.
I've attempted this, and other variants, but keep getting errors:
= Table.AddColumn(#"Filtered Rows1", "Merged", (row) => Text.Combine(List.RemoveNulls(List.Skip(Record.FieldValues(Record.FromTable(Table.SelectColumns(#"Filtered Rows1",{"Name.1.1", "Name.1.2", "Name.1.3", "Name.1.4", "Name.1.5", "Name.1.6", "Name.1.7"}))), 1)), "/"))
The easy solution that I can see would be to duplicate > remove columns > forumulate > remerge. But, I think there would be a way of accomplishing this while avoiding those additional steps.
P.S I'm fairly new to BI!
Solved! Go to Solution.
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]}), "/"))
Hi @CPrince
Please see the attached file with the solution included.
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
Thanks for this! I've been looking for a dynamic solution for a while and this really does it.
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
9 | |
7 | |
6 | |
6 |