Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

     

    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"

     

     

Jakinta's avatar
Jakinta
Solution Sage
5 years ago
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's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks for the help