Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Column to rows/ Group by

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     E...
Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    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     🙂

     

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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