Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Index to distinct value

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 ...
lbendlin's avatar
lbendlin
Super User
4 years ago
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.