HELP INDEX COLUMN WITH RANGES

[abelrmg]

I have a two columns "JOB" and "ROL", Job is a incremental number and "ROL" this could be "A","B" or "C". And I would like to add a column like index considering how many equals roles there are for each job, as the folowing example:

JOB	ROL	INDEX RESULT
1	A	1
1	A	2
1	B	1
1	C	1
2	A	1
2	B	1
2	C	1
3	A	1
3	B	1
3	B	2
3	B	3
3	C	1

[Mariusz]

Hi @abelrmg 

You can try something like below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitVBZznBWc5glhFc1gguawSXNYbLGsNlcbGAOmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [JOB = _t, ROL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB", Int64.Type}, {"ROL", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JOB", "ROL"}, {{"Index", each Table.AddIndexColumn( _, "Index", 1 )[Index], type list }}),
    #"Expanded Index" = Table.ExpandListColumn(#"Grouped Rows", "Index"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index", Int64.Type}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[parry2k]

@abelrmg add an index column in query editor and then add following column and it will get you what you are looking for

Index Result = 
RANKX ( 
FILTER(  
What, 
What[Job]= EARLIER ( What[Job] )  && 
What[ROL] = EARLIER ( What[ROL] )  
), 
What[Index], , ASC, Dense 
)

[abelrmg]

@parry2k , thank you for your suggestion it works, but is there sometihing similar in Power Query?, beacuse then i want to create a pivot table and the values are dynamics and this does not work in DAX.

Regards

[parry2k]

Not sure what you mean by dynamic, it should work. May be I am missing something.

[abelrmg]

@parry2k It was my mistake, I forgot to mention that I was looking for the solution via Power Query, because then I will use that column to make a pivot table to perform some like this:

Job	ROL A1	ROL A2	ROL B1	ROL B2	ROL B3	ROL C1
1	Name 1	Name 2	Name 3			Name 4
2	NAME 5		NAME 6			NAME 7
3	NAME 8		NAME 9	NAME 10	NAME 11	NAME 12

Regards