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 wo...
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]),
#"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"
Thank you so much!!!