Forum Discussion

CPrince's avatar
CPrince
Frequent Visitor
6 years ago
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 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!

Need Help
  • CPrince's avatar
    CPrince
    6 years ago

    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]}), "/"))

     

6 Replies

    • CPrince's avatar
      CPrince
      Frequent Visitor
      6 years ago

      Thanks unfortunately I don't have access to Power BI so I can't access this!

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        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