Moving Column Names to Column Value

[Anonymous]

I have a data source that provides sold products as column headings with a Yes in the column and I would like to replace the Yes with the column name, the issue is there are a lot of columns and I wonder if there is a smarter way to do this then to create a replacevalue statement for each column as there are a LOT of them

Customer	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H	Combine
Customer 1			Yes		Yes
Customer 2	Yes	Yes	Yes				Yes

I would like to end up with a table that looks like this, I did also consider a combine with if statements but the actual scenario is much more complex and it seemed simpler to set the column values first and then have the next steps do the complex combinations 

Customer	Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H	Combine
Customer 1			Product C		Product E				Product C Product E
Customer 2	Product A	Product B	Product C				Product G		Product A Product B Product C Product G

Any suggestions greatfully received 🙂

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUVKA4sjUYgwWBMfqIGkxgitAJhWwGBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer " = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t, #"Product E" = _t, #"Product F" = _t, #"Product G" = _t, #"Product H" = _t]),

    #"Replaced Yes" = List.Accumulate(List.Skip(Table.ColumnNames(Source)), Source, (s,c) => Table.ReplaceValue(s, c, "", (x,y,z) => if x="Yes" then y else z, {c})),
    #"Combined Products" = Table.AddColumn(#"Replaced Yes", "Combine", each Text.Combine(List.Select(List.Skip(Record.ToList(_)), each _<>""), "#(lf)"))
in
    #"Combined Products"

[Anonymous]

Thank you so much!!!

[Jakinta]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUVKA4sjUYgwWBMfqIGkxgitAJhWwGBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer " = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t, #"Product E" = _t, #"Product F" = _t, #"Product G" = _t, #"Product H" = _t]),
    Custom = Table.AddColumn(Source, "Custom", each let cols=List.Skip(Table.ColumnNames(Source)), r=List.Skip(Record.ToList(_)), r1=List.Transform(List.Positions(cols), each Text.From(_) & r{_}), i= List.PositionOf(r,"Yes", Occurrence.All), replace =  List.Zip({ List.Transform(i, each r1{_}),List.Transform(i, each cols{_})}) in  List.Transform( List.ReplaceMatchingItems(r1, replace), each if Text.Length( Text.Trim(_))>1 then _ else "")),
    Custom1 = Table.AddColumn(Custom, "Custom.1", each Table.FromRows ({[Custom]}, List.Skip(Table.ColumnNames(Source)))),
    RemovedOther = Table.SelectColumns(Custom1,{"Customer ", "Custom.1"}),
    Expanded = Table.ExpandTableColumn(RemovedOther, "Custom.1", Table.ColumnNames(RemovedOther[Custom.1]{0}))
in
    Expanded

or

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUVKA4sjUYgwWBMfqIGkxgitAJhWwGBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer " = _t, #"Product A" = _t, #"Product B" = _t, #"Product C" = _t, #"Product D" = _t, #"Product E" = _t, #"Product F" = _t, #"Product G" = _t, #"Product H" = _t]),
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Customer "}, "Attribute", "Value"),
    Grouped = Table.Group(Unpivoted, {"Customer "}, {{"Combine", each Text.Combine( Table.SelectColumns(Table.SelectRows(_, each ([Value] = "Yes")),"Attribute")[Attribute], "#(lf)")}})
in
    Grouped

[Anonymous]

Thanks for the help