Column to rows/ Group by

[Anonymous]

Hello, 

I have a Table with the following data:

Company Id	Sales Person	Product
999	Alex	A
999	Alex	B
999	Alex	C
1000	Brain	A
1000	Brain	B
1005	Max	A

Expected Output:

Company Id	Sales Person	A	B	C
999	Alex	TRUE	TRUE	TRUE
1000	Brain	TRUE	TRUE	FALSE
1005	Max	TRUE	FALSE	FALSE

How to group them to see which salesperson has sold all three products? To identify I marked them as "TRUE" OR "FALSE"  is their way to replace them with Icons ( ✔️ or ❌).

Thanks in advance !!!!

[mahoneypat]

If you want to do it with DAX instead of M, you can use this approach.  

ProductSold = var salescount = COUNTROWS(Sales)+0

return if(salescount=0, "❌", "✔") // this uses Windows 10 emojis you can find with Windows Key and "."

This will get you the visual on the left.

If you want the one on the right, you need to do a little more.  Make a simple DAX calculated table with 

SalesProducts = VALUES(Sales[Product ])

and use that as your column in the matrix visual and then use this measure

ProductSold2 = var salescount = CALCULATE(COUNTROWS(Sales)+0, TREATAS(VALUES(SalesProducts[Product ]), Sales[Product ]))

return if(salescount=0, "❌", "✔") // note - the red X isn't default so you need to copy paste that text from https://getemoji.com/#symbols

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[nandukrishnavs · Accepted solution]

@Anonymous 

Try the below PowerQuery

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrS0VNJRcsxJrQBRSrE6aEJOmELOYCFDAwMDkHxRYmYeXCuaoBNM0BTI8U2EWhELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Company Id" = _t, #"Sales Person" = _t, #"Product " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Id", Int64.Type}, {"Sales Person", type text}, {"Product ", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Company Id", "Company Id - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Product "]), "Product ", "Company Id - Copy")
in
    #"Pivoted Column"

You can apply conditional formatting in the formatting pane.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

How can we group the data using DAX formula? Also, I'm pretty new to PowerQuery can you elaborate little on applied steps.

Thank You.