Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jeanrozin
New Member

Unique key creation

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?
2 ACCEPTED SOLUTIONS
durack99
Advocate I
Advocate I

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

durack99_0-1694479554872.png

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:

durack99_2-1694480073121.png

 

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

durack99_3-1694480211436.png

Select the Ngolo Table:

durack99_4-1694480261493.pngdurack99_5-1694480289086.png

Then you will want to sort the rows by time:

durack99_6-1694480369880.png

and then add an index column:

durack99_7-1694480407265.png

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

durack99_8-1694480516651.png

durack99_9-1694480908874.png

 

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:

durack99_10-1694481067498.pngdurack99_11-1694481116738.png

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

durack99_12-1694481174855.png

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

durack99_13-1694481230896.png

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

 

Hope that helps chief

View solution in original post

After an hour I think I did something wrong

 

jeanrozin_0-1694483936044.png

 

View solution in original post

5 REPLIES 5
durack99
Advocate I
Advocate I

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

durack99_0-1694479554872.png

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:

durack99_2-1694480073121.png

 

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

durack99_3-1694480211436.png

Select the Ngolo Table:

durack99_4-1694480261493.pngdurack99_5-1694480289086.png

Then you will want to sort the rows by time:

durack99_6-1694480369880.png

and then add an index column:

durack99_7-1694480407265.png

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

durack99_8-1694480516651.png

durack99_9-1694480908874.png

 

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:

durack99_10-1694481067498.pngdurack99_11-1694481116738.png

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

durack99_12-1694481174855.png

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

durack99_13-1694481230896.png

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

 

Hope that helps chief

After an hour I think I did something wrong

 

jeanrozin_0-1694483936044.png

 

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

 

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
Frequent Visitor

Adsy_0-1694481393441.gif

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.