Forum Discussion

bossamus's avatar
bossamus
Frequent Visitor
4 years ago
Solved

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