Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 !!!!
Solved! Go to 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 🙂
If you want to do it with DAX instead of M, you can use this approach.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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 🙂
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |