Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have data similar to the following tables containing category hierarchy and sales quantity by items.
I want to create a hierarchy based on the product category and filter the sales quantity table by it.
i.e
Category
A
B
C
Solved! Go to Solution.
Hi @Ahmaddaniyal ,
Here are the steps you can follow:
1. Enter Power query, select [Category hierarchy 1], [ Category hierarchy 2], [ Category hierarchy 3] - Unpivot Columns of Table.
2. Right-click the newly generated [Attribute] - Remove.
Result:
3. Check [Value] - Sort Ascending - uncheck its blank.
Result:
4. Home – Merge Queries -- Merge Queries as New.
5. Open the new table formed, click Expand next to Table2, and select [SalesPerson] and [SelesQuantity].
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Ahmaddaniyal ,
Here are the steps you can follow:
1. Enter Power query, select [Category hierarchy 1], [ Category hierarchy 2], [ Category hierarchy 3] - Unpivot Columns of Table.
2. Right-click the newly generated [Attribute] - Remove.
Result:
3. Check [Value] - Sort Ascending - uncheck its blank.
Result:
4. Home – Merge Queries -- Merge Queries as New.
5. Open the new table formed, click Expand next to Table2, and select [SalesPerson] and [SelesQuantity].
6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
but it does not resolve my issue to filter by categories.
A
B
C
as mentioned in the dataset
@Ahmaddaniyal , Unpivot the category in the first table, that will all categories in row for the item, That will make analysis easy. But will create a many to many join
https://radacad.com/pivot-and-unpivot-with-power-bi
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |