Need help replacing value x with column headers

Anonymous

Not applicable

4 years ago

Although, it is late, I suggest the following method : unpivot > duplicate column > pivot but it requires replace empty by null. beforehand.

Below is the code that can be copied to a blank query

let
    Source = "Product,H1,H2,H3,H4
A,,X,X,
B,X,,,X
C,,,X,X
D,X,X,,
E,X,,X,X",
TBL= Csv.Document(Source),
    #"Promoted Headers" = Table.PromoteHeaders(TBL, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","",null,Replacer.ReplaceValue,{"H1", "H2", "H3", "H4"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Product"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Other Columns", "Attribute", "Attribute - Copy"),
    #"Removed Columns" = Table.RemoveColumns(#"Duplicated Column",{"Value"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Attribute - Copy")
in
    #"Pivoted Column"

Forum Discussion

Need help replacing value x with column headers