Rank duplicate ID based on date

[bnjmnnl]

Good day! Im facing a small issue but would help a lot when solved. My dataset shows the following:

Job	PU Date
1	01/01/23
1	01/02/23
1	01/03/23
2	01/01/23
3	01/01/23
3	01/02/23

However I need a new column (in query editor) to rank them like the following:

Job	PU Date		Step
1	01/01/23		1
1	01/02/23		2
1	01/03/23		3
2	01/01/23		1
3	01/01/23		1
3	01/02/23		2

Would help me out a lot if someone has the solution for me! Table is called 'Jobs Ranked'
Thank you!

[Ritaf1983]

Hi @bnjmnnl 

Add a calculated column with the dax code :

Ranking =

RANKX(

    FILTER(

        ALL('Table (2)'),

        'Table (2)'[Job] = EARLIER('Table (2)'[Job])

    ),

    'Table (2)'[PU Date],

    ,

    ASC

)

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

[bnjmnnl]

Hello! Thank you but this is not the option I'm looking for. This works for table view in the report but wom't show the data in query editor. I need to have it in Query editor to go further with new steps. Do you know how to achieve this?

[Ritaf1983 · Accepted solution]

Hi @bnjmnnl  for ranking with POWER QUERY please refer to the linked tutorial

https://www.youtube.com/watch?v=ej60Wxaum_E

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

[bnjmnnl]

Thank you!

[Ritaf1983]

Glad to help😊

[bnjmnnl]

And I'm going to add a followup question haha, maybe you can help me again. 

Thanks to your help, my data is like the following:

Job	Step	PU Date	PU At
1	1	01/01/23	a
1	2	01/02/23	b
1	3	01/03/23	c
2	1	01/01/23	a
3	1	01/01/23	a
3	2	01/02/23	b

However, now I want to add them in new columns. Like the following:

Job

Step 1

PU Date 1

PU At 1

Step 2

PU Date 2

PU At 2

Step 3

PU Date 3

PU At 3

Step 4

PU Date 4

PU At 4

1

1

01/01/23

a

2

01/02/23

b

3

01/03/23

c

4

null

null

2

1

01/01/23

a

2

null

null

3

null

null

4

null

null

3

1

01/01/23

a

2

01/02/23

b

3

null

null

4

null

null

As you see, no job has a 4th step. The jobs can have a maximum of 10 steps, so I will need to make the new columns for 10 steps, they ma all show blank when they are not used. 

Do you know a way to achieve this? Thank you!