Can't find correct syntax with EARLIER

[fandecine68]

Hi,

I want to create the index column below based on duplicates Key column:

Key,Index
A1,1
A1,2
B1,1
C1,1
D1,1
D1,2
E1,1
F1,1

I started with this but get only 1s.....

=RANKX(

    FILTER(

        Test,

        Test[Key] = EARLIER(Test[Key])

    ),

    Test[Key]

)

Thanks for your help.

[FreemanZ · Accepted solution]

hi @fandecine68 ,

this is mostly done with power query. try to paste the code below into a blank query,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitWBU04QyhlCuaBQrhDKDUjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each _, type table [Key=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
    #"Removed Columns"

for detailed steps, check like:

https://community.fabric.microsoft.com/t5/Power-Query/Group-by-a-column-and-set-index/m-p/2918028

https://community.fabric.microsoft.com/t5/Desktop/Create-Index-Column-on-Power-Query-based-on-2-or-3...

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

[v-zhengdxu-msft · Accepted solution]

Hi @fandecine68 

The rankx() function can only order by the distinct values, the duplicate values can get the same number.

You can try the method posted by @FreemanZ , or add a index column in power query:

And then add a calculate column:

Column =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Key] = EARLIER ( 'Table'[Key] ) ),
    'Table'[Index],
    ,
    ASC
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ · Accepted solution]

hi @fandecine68 ,

this is mostly done with power query. try to paste the code below into a blank query,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRUitWBU04QyhlCuaBQrhDKDUjFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Key"}, {{"Count", each _, type table [Key=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Index", 1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Index"}, {"Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"})
in
    #"Removed Columns"

for detailed steps, check like:

https://community.fabric.microsoft.com/t5/Power-Query/Group-by-a-column-and-set-index/m-p/2918028

https://community.fabric.microsoft.com/t5/Desktop/Create-Index-Column-on-Power-Query-based-on-2-or-3...

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query