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 IdSales PersonProduct 
999AlexA
999AlexB
999AlexC
1000BrainA
1000BrainB
1005MaxA

 

 

Expected Output:

Company IdSales PersonABC
999AlexTRUETRUETRUE
1000BrainTRUETRUEFALSE
1005MaxTRUEFALSEFALSE

 

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 !!!!

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     🙂

     

     

3 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    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     🙂

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

  • 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