New Column - How to achieve concatenation of muliple columns based on logic

[Anonymous]

Looking to create the new column below. I need the column to be the name of all columns where the value = "Yes". I am not sure how to achieve this other than writing an IF statement for every possible combination. I also do not want to end up with somethign like "Attribute 1, , , Attribute 4". 

Product	Attribute 1	Attribute 2	Attribute 3	Attribute 4	New Column
CODE1	Yes	No	No	No	Attribute 1
CODE2	No	Yes	Yes	Yes	Attribute 2, Attribute 3, Attribute 4
CODE3	No	No	No	No
CODE4	No	No	No	No
CODE5	Yes	Yes	Yes	Yes	Attribute 1, Attribute 2, Attribute 3, Attribute 4
CODE6	No	No	Yes	No	Attribute 3

[AntrikshSharma]

@Anonymous Use Power Query to unpivot the columns and create 2 columns, one for Product and the second for Attribute. Then you can use something like this:

=
VAR OuterProduct = Table[Product]
VAR SameProducts =
    FILTER ( Table, Table[Product] = OuterProduct )
VAR Result =
    CONCATENATEX ( SameProducts, Table[Attributes], ", " )
RETURN
    Result

[camargos88]

Hi @Anonymous ,

You can use PQ with this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvZ3cTVU0lGKTC0Gkn75SESsDkTaCCYGUYMgYQqM0XWiaDfBK2uKxVxU081QtMLdGRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Attribute 1" = _t, #"Attribute 2" = _t, #"Attribute 3" = _t, #"Attribute 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Attribute 1", type text}, {"Attribute 2", type text}, {"Attribute 3", type text}, {"Attribute 4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each let _product = [Product] in

Text.Combine(Table.SelectRows(#"Unpivoted Other Columns",
each [Product] = _product and [Value] = "Yes")[Attribute],
"-")),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Attribute]), "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Product", "Attribute 1", "Attribute 2", "Attribute 3", "Attribute 4", "Custom"})
in
    #"Reordered Columns"

[MFelix]

Hi @Anonymous ,

Try the following code:

Column =
VAR ATT_1 =
    IF ( Products[Attribute 1] = "YES"; "Attribute 1" )
VAR ATT_2 =
    IF ( Products[Attribute 2] = "YES"; "Attribute 2" )
VAR ATT_3 =
    IF ( Products[Attribute 3] = "YES"; "Attribute 3" )
VAR ATT_4 =
    IF ( Products[Attribute 4] = "YES"; "Attribute 4" )
RETURN
    ATT_1
        & IF ( ATT_1 = BLANK () || ATT_2 = BLANK (); BLANK (); ", " ) & ATT_2
        & IF ( ATT_2 = BLANK () || ATT_3 = BLANK (); BLANK (); ", " ) & ATT_3
        & IF ( ATT_3 = BLANK () || ATT_4 = BLANK (); BLANK (); ", " ) & ATT_4

[Anonymous]

Thank you Miguel.

I tried your solution and it is very close to what I am looking for. I ran into a small issue not having a ", " between every value. I actually have 9 Attributes that I need to do this for and I found that when every other attribute is blank, I end up with something like "att_1att_3att_7" instead of "att_1, att_3, att_7"

Any idea how to fix this for a table of 9 attributes?

Thanks

[MFelix · Accepted solution]

Hi @Anonymous .

Use the following syntax to create your column:

Column = 
VAR ATT_1 =
    IF ( Products[Attribute 1] = "YES"; "Attribute 1, " )
VAR ATT_2 =
    IF ( Products[Attribute 2] = "YES"; "Attribute 2, " )
VAR ATT_3 =
    IF ( Products[Attribute 3] = "YES"; "Attribute 3, " )
VAR ATT_4 =
    IF ( Products[Attribute 4] = "YES"; "Attribute 4, " )
VAR ATT_5 =
    IF ( Products[Attribute 5] = "YES"; "Attribute 5, " )
VAR ATT_6 =
    IF ( Products[Attribute 6] = "YES"; "Attribute 6, " )
VAR ATT_7 =
    IF ( Products[Attribute 7] = "YES"; "Attribute 7, " )
VAR ATT_8 =
    IF ( Products[Attribute 8] = "YES"; "Attribute 8, " )
VAR ATT_9 =
    IF ( Products[Attribute 9] = "YES"; "Attribute 9" )
VAR FINAL_TEXT = ATT_1 & ATT_2 & ATT_3 & ATT_4 & ATT_5 & ATT_6 & ATT_7 & ATT_8 & ATT_9
RETURN
    IF(RIGHT(FINAL_TEXT;2) = ", " ; LEFT(FINAL_TEXT; LEN(FINAL_TEXT) - 2); FINAL_TEXT)