Forum Discussion

abelrmg's avatar
abelrmg
Icon for Helper II rankHelper II
6 years ago

HELP INDEX COLUMN WITH RANGES

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:

 

 
JOBROLINDEX RESULT
1A1
1A2
1B1
1C1
2A1
2B1
2C1
3A1
3B1
3B2
3B3
3C1
 
Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      abelrmg
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago
        Not sure what you mean by dynamic, it should work. May be I am missing something.
  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.