Order within each product

Question

I want to prioritize the sub_product within each product_id.

Product_id	Sub_product	Category
10101010	02020202	Superior
10101010	02020204	High-valued
0120202	0930303	High-valued
0120202	039303	VALUE
0303303	039302929	VALUE
0303303	0393342929	AddOn

Here is the expected results:

Product_id	Sub_product	Category	Priority
10101010	02020202	Superior	1
10101010	02020204	High-valued	2
0120202	0930303	High-valued	1
0120202	039303	VALUE	2
0303303	039302929	VALUE	1
0303303	0393342929	AddOn	2

Order of priority:

Superior = 1
High-valued = 2
VALUE = 3
AddOn = 4

Using derived column, I can achieve the below output.

Product_id	Sub_product	Category	Priority
10101010	02020202	Superior	1
10101010	02020204	High-valued	2
0120202	0930303	High-valued	2
0120202	039303	VALUE	3
0303303	039302929	VALUE	3
0303303	0393342929	AddOn	4

Is it possible to start the number from 1 for each product_id?

Answer 1

After derived column, you can use window transformation and group it over product_id column and apply rank() function for each group. Below are the steps in dataflow.

• Give the Product_id column name in the over section of window settings.

• Give the Priority column that is computed from derived column transformation step in sort section of window settings.

• In Windows columns, give the column name Priority_new and expression as rank().

• Below is the output data.