ID column where a new row receives a new ID

[TB123]

Hi,

I am trying to create an ID column (like an index column), where if a new row is added to the table, it receives a new index number (ID).

The problem I'm having with creating an index column is that if I were to add a new country, in this case France, in the source file, then it just joins the index and takes an existing number (see example below):

Original:                                                                                  

                                                                                                  

New (Wrong):

France has taken Italy's number of 4, whereas I want it to say 7.

As I'm using this colunm to join to other tables, it has to be fixed and the numbers can't move around when a new row is added.

Any help would be much appreciated.

[v-angzheng-msft]

Hi, @TB123 

Always adding new countries at the bottom of the table will solve your problem.

You can also create a country dimension table and then create a custom function to find and replace the index. This helps you to replace the index of the newly added country. Refer to the thread below to create the function.

Replacing values in one column with values from another table

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Two approaches you can consider:

1. Make your index/countryID columns dynamic across all your tables, if possible, so even if they change they are correct.

2. Add a date added column somehow to your country table and then use Table.Buffer(Table.Sort()) to sort it by that new column prior to adding the index column.

Pat

[TB123]

Thanks for the reply Pat.

How would I got about adding a date added column?