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    1
455EQUA    1
455EQUA     2
4663E    1
4883E    1

I humbly ask for this help

 

thanks.

1 Reply

  • 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.