Forum Discussion
Conditionally finding column name of maximum value
Hi there!
Ive got data that looks like this,
| Customer | HasProductA | HasProductB | HasProductC | HasProductD | LikelihoodOfBuyingA | LikelihoodOfBuyingB | LikelihoodOfBuyingC | LikelihoodOfBuyingD | MostLikelyPurchase |
| David | Yes | No | Yes | No | 80 | 42 | 15 | 85 | D |
| Jess | No | Yes | No | Yes | 82 | 65 | 44 | 94 | A |
| Steve | Yes | No | No | No | 79 | 98 | 97 | 32 | B |
| Frank | No | Yes | No | Yes | 42 | 15 | 32 | 14 | A |
| Bob | No | No | Yes | No | 65 | 78 | 78 | 59 | B |
| Bert | No | Yes | No | Yes | 87 | 21 | 26 | 87 | A |
| Alicia | Yes | No | Yes | No | 92 | 35 | 86 | 96 | D |
| Fred | No | Yes | No | No | 34 | 47 | 45 | 32 | C |
| Jack | Yes | No | Yes | No | 59 | 98 | 56 | 65 | B |
I am trying to calculate 'Most Likely Purchase' column which returns the column name of the highest value of which that customer does not already have that product. I.e., Jack doesnt have Product B or D. Jacks "likelihood to purchase B" is 98 and "likelihood to purchase D" is 65, hence return "product B".
Thank you!
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"
3 Replies
- amitchandak
Super User
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
bossamus , Power Query code to get in shape to use
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" Hi bossamus ,
Here are the steps you can follow：
1. You can follow the amitchandak method to get the data after unpivot.
2. After opening the visual object of the matrix, place [Customer] on Rows, and place [LikelihoodofBuying] and [MostLikelyPurchase] on Values.
3. In to Filters, select the filter condition as "No".
4. The result is:
5. The matrix is sorted in alphabetical order, so we can use Enter data to create an Index_Table:
6. Click [Customer] – Column tools – Sort by column – [Index].
7. After placing the [Customer] of Index_Table into the Row of the matrix, the result is:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
