Forum Discussion
Moving Column Names to Column Value
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 🙂
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"
4 Replies
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"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 Expandedor
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
