Index to distinct value

[danielbon]

I need to do an indexing changing the value only if the id repeats itself, how can I do this in a column?

For example:

ID	INDEX
4568ENG	1
4568ENG	2
4568ENG	3
4783E	1
455EQUA	1
455EQUA	2
4663E	1
4883E	1

I humbly ask for this help

thanks.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRitWJVjIxNbNw9XPHzza3MHaFipq6BoY6YrLNzGAqLMBqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"ID"}, {{"Rows", each _, type table [ID=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows], "Index", 1, 1, Int64.Type)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"ID", "Index"}, {"ID", "Index"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Note you have trailing spaces in some of your data. Power Query is case sensitive.