Conditionally finding column name of maximum value

[bossamus]

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!

[Anonymous]

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

[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"

[amitchandak · Accepted solution]

@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"