Forum Discussion
bossamus
4 years agoFrequent Visitor
Conditionally finding column name of maximum value
Hi there! Ive got data that looks like this, Customer HasProductA HasProductB HasProductC HasProductD LikelihoodOfBuyingA LikelihoodOfBuyingB LikelihoodOfBuyingC LikelihoodOfBuying...
- 4 years ago
bossamus , I think you need to unpivot twice, You need to unpivot these columns then pivot again so that you get product and likelyshood to buy as column and then you can use that for analysis
https://kohera.be/blog/power-bi/how-to-unpivot-twice/
Please find the Power Query code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZGxDoMgFEX/hdmhKiCMWuPQoUunxjhQZTA2mqDx+8vFiDWR5QQSjvfdZ12TUq19RyLy1rPlczofxc2CJhYxwxUoSRPV5KHnK2E7Chgcjym1kEDutNeiV31WDmQSjwWQWaT4SuG0yqhxCMYdAzolPuKK6XNK+FPdeJnYwaQPK7RZwtUwWRIDfL9uWfm3b3sVXKXEaKnbIkTJ/Soro7sLwSFFF4oQ6vvdtx+g2iEYxvwmGd+72nLNDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, HasProductA = _t, HasProductB = _t, HasProductC = _t, HasProductD = _t, LikelihoodOfBuyingA = _t, LikelihoodOfBuyingB = _t, LikelihoodOfBuyingC = _t, LikelihoodOfBuyingD = _t, MostLikelyPurchase = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"HasProductA", type text}, {"HasProductB", type text}, {"HasProductC", type text}, {"HasProductD", type text}, {"LikelihoodOfBuyingA", Int64.Type}, {"LikelihoodOfBuyingB", Int64.Type}, {"LikelihoodOfBuyingC", Int64.Type}, {"LikelihoodOfBuyingD", Int64.Type}, {"MostLikelyPurchase", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "MostLikelyPurchase"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Attribute Group", each if Text.Contains([Attribute], "Has") then "Has" else if Text.Contains([Attribute], "Likelihood") then "LikelihoodofBuying" else ""), #"Replaced Value" = Table.ReplaceValue(#"Added Custom","Has","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","LikelihoodOfBuying","Product",Replacer.ReplaceText,{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[#"Attribute Group"]), "Attribute Group", "Value", List.Max) in #"Pivoted Column"
amitchandak
Super User
4 years agobossamus , I think you need to unpivot twice, You need to unpivot these columns then pivot again so that you get product and likelyshood to buy as column and then you can use that for analysis
https://kohera.be/blog/power-bi/how-to-unpivot-twice/
Please find the Power Query code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZGxDoMgFEX/hdmhKiCMWuPQoUunxjhQZTA2mqDx+8vFiDWR5QQSjvfdZ12TUq19RyLy1rPlczofxc2CJhYxwxUoSRPV5KHnK2E7Chgcjym1kEDutNeiV31WDmQSjwWQWaT4SuG0yqhxCMYdAzolPuKK6XNK+FPdeJnYwaQPK7RZwtUwWRIDfL9uWfm3b3sVXKXEaKnbIkTJ/Soro7sLwSFFF4oQ6vvdtx+g2iEYxvwmGd+72nLNDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, HasProductA = _t, HasProductB = _t, HasProductC = _t, HasProductD = _t, LikelihoodOfBuyingA = _t, LikelihoodOfBuyingB = _t, LikelihoodOfBuyingC = _t, LikelihoodOfBuyingD = _t, MostLikelyPurchase = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"HasProductA", type text}, {"HasProductB", type text}, {"HasProductC", type text}, {"HasProductD", type text}, {"LikelihoodOfBuyingA", Int64.Type}, {"LikelihoodOfBuyingB", Int64.Type}, {"LikelihoodOfBuyingC", Int64.Type}, {"LikelihoodOfBuyingD", Int64.Type}, {"MostLikelyPurchase", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "MostLikelyPurchase"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Attribute Group", each if Text.Contains([Attribute], "Has") then "Has"
else if Text.Contains([Attribute], "Likelihood") then "LikelihoodofBuying" else ""),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","Has","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","LikelihoodOfBuying","Product",Replacer.ReplaceText,{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[#"Attribute Group"]), "Attribute Group", "Value", List.Max)
in
#"Pivoted Column"