Unique key creation

[jeanrozin]

My database has a column in which the information is repeated in some lines.

This information is always repeated in sequence, never in lines that are far from each other.

Whenever information appears for the first time, I need you to bring 1 to the new column.

When the information is repeated in the bottom line, I need you to add the value from the previous line +1.

This way, whenever information is repeated I will have a different number for each repetition, following the order 1, 2, 3, 4, 5...

Does anyone know how to solve this?

[durack99 · Accepted solution]

Sounds like what you're after is an index column corporal. 

Basically this just adds a number going up for each row. 

What I think you're after specifically though is a group by and then adding an index column. Consider the following example:

We want to add an index: 1,2,3,4 etc. for each name. We can see that some names are repeated such as 'Ngolo', but we want a new index for each name. Lets say we want the earliest name to have the number 1 next to it. So the first step will be to group by the column that has duplicate values, in this case 'name', and select 'All Rows'.

Select the Ngolo Table:

Then you will want to sort the rows by time:

and then add an index column:

Once we have all of these steps done, we will need to apply them to all of these rows. So you will need to go to the 'advanced editor', and copy and paste all the rows with the grouped transformation query into a blank query, formatted as a 'function'.

let
    Source = (TableVariableName as table) => 

let
    #"Changed Type1" = Table.TransformColumnTypes(TableVariableName,{{"Name", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Time", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)
in
    #"Added Index"
in
    Source

With this function created you can then go back to your original table and delete until you get back to the step with the group tables, and then select the 'custom function' option:

This will then give you a new column which you can expand and will have your index grouped now for each column:

You can then delete any rows you don't want to use, and your final table should look as follows:

Notice the earliest record per name has the 0 index, and then each one after has the index+1

Hope that helps chief

[durack99 · Accepted solution]

Sounds like what you're after is an index column corporal. 

Basically this just adds a number going up for each row. 

What I think you're after specifically though is a group by and then adding an index column. Consider the following example:

We want to add an index: 1,2,3,4 etc. for each name. We can see that some names are repeated such as 'Ngolo', but we want a new index for each name. Lets say we want the earliest name to have the number 1 next to it. So the first step will be to group by the column that has duplicate values, in this case 'name', and select 'All Rows'.

Select the Ngolo Table:

Then you will want to sort the rows by time:

and then add an index column:

Once we have all of these steps done, we will need to apply them to all of these rows. So you will need to go to the 'advanced editor', and copy and paste all the rows with the grouped transformation query into a blank query, formatted as a 'function'.

let
    Source = (TableVariableName as table) => 

let
    #"Changed Type1" = Table.TransformColumnTypes(TableVariableName,{{"Name", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Time", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)
in
    #"Added Index"
in
    Source

With this function created you can then go back to your original table and delete until you get back to the step with the group tables, and then select the 'custom function' option:

This will then give you a new column which you can expand and will have your index grouped now for each column:

You can then delete any rows you don't want to use, and your final table should look as follows:

Notice the earliest record per name has the 0 index, and then each one after has the index+1

Hope that helps chief

[jeanrozin · Accepted solution]

After an hour I think I did something wrong

[durack99]

lo siento no puedo hablar espanol, in ingles por favor ?

[jeanrozin]

Now I got it, thank you very much.

I did a simple test with some data, now I'm going to test with the database with more than 30 million rows.

Lets Go!!!

[Adsy]