Custom index colum with multiple conditions

[EllO]

Hi,

I'm new here, but I have read many posts and have gotten a lot of help from them 🙂 So I wonder if anyone can help med with my problem. Hope it's alright that I post a couple of pictures insted of files. 

This is my data: (The dataset has many more colums and about 50000 rows, but the problem I have is with the data below.)

I want an index colum that looks like this:

I can do this in the table view in power BI, but I need to do it in power query. In the table view in power BI, this is what I get: (And this is what I want in power query.)

I've tried to use "group by" in power query, but this is what I get:

I will really appreciate if anybody has the time to help me 🙂 Thanks 🙂

[Ahmedx · Accepted solution]

and to know how you can do this in power query, watch my video

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jce5DQAgCADAXahp+GEWwv5raG9iuO66gYgZEFTNYHB9M/flRe7dI5ZXvY/I/D2z6vcqovdzAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SO = _t, #"Analysis nr" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SO", Int64.Type}, {"Analysis nr", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SO", "Analysis nr"}, {{"Count", each _, type table [SO=nullable number, Analysis nr=nullable number]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"SO"}, {{"Count", (x)=> Table.AddIndexColumn(x,"Indx",1,1)
}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows1",{"SO"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Count", "Indx"}, {"Count.1", "Indx"}),
    #"Expanded Count.1" = Table.ExpandTableColumn(#"Expanded Count", "Count.1", {"SO", "Analysis nr"}, {"SO", "Analysis nr"})
in
    #"Expanded Count.1"

Custom index colum.mp4

[EllO]

Thank you so much, especially for the video! Really impressed that you took the time to make a video! This worked out really well 🙂 Thanks again 🙂

[Ahmedx]

pls try this

Column = 
VAR t1 ='Table'[SO]
RETURN
RANKX(FILTER(ALL('Table'),'Table'[SO]=t1),'Table'[Analysis nr],,ASC,Dense)