Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Product | H1 | H2 | H3 | H4 |
A | X | X | ||
B | X | X | ||
C | X | X | ||
D | X | X | ||
E | X | X | X |
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"
You will need to transpose your table, then Replace Value under the Transform Tab, then once completed transpose your table back.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |