Forum Discussion

Janaki_co's avatar
Janaki_co
Frequent Visitor
7 years ago

Need help replacing value x with column headers

Scenario -  I have product names in Col1 followed by Product group Headers(H1...H4) . The value X indicates that Product A belongs to Product Group H1. I would like to repace X's with respective product group Headers. How can I achieve this in power BI quickly?

 

ProductH1H2H3H4
A XX 
BX  X
C  XX
DXX  
EX XX
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You will need to transpose your table, then Replace Value under the Transform Tab, then once completed transpose your table back.

  • Anonymous's avatar
    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"