Generate Unique id for columns and replicate is of these columns in other tables

[Mahendran_C_S]

I have a requirement to generate a unique id for a column in a table and that column is also present in another table where the generated unique id should replicate in another table too.

For example, I have a product table : 

I want to generate a unique id for the column product id

Generated ID	Product ID	Product Name	Category
1	FUR-BO-10001798	Bush Somerset Collection Bookcase	Furniture
2	TEC-PH-10002275	Mitel 5320 IP Phone VoIP phone	Phones

I have another table called Orders where it had product ID where the product ID is repeated :

I want the ID generated for product ID in product table has to be the same in generated ID for product ID in the orders column too.

Date	Product ID	Generated ID for Product ID	Product Name	Sales	Customer ID
09-11-2013	FUR-BO-10001798	1	Bush Somerset Collection Bookcase	3000	CG-12520
09-11-2013	FUR-BO-10001798	1	Bush Somerset Collection Bookcase	6000	SO-20335
09-11-2013	TEC-PH-10002275	2	Mitel 5320 IP Phone VoIP phone	2400	SO-20335
09-12-2013	TEC-PH-10002275	2	Mitel 5320 IP Phone VoIP phone	4800	BH-11710

Even though product ID is unique in the product column, the purpose of generating generated columns in numbers is to increase the performance while searching or linking the tables. The actual product table contains a 13 digits unique string where it reduces the performance.

Could anyone please help me in finding the solution for the above requirement? Thanks in advance.

[BA_Pete · Accepted solution]

Hi @Mahendran_C_S ,

A bit of a left-field idea, but you could convert your product ID's to Unicode or some other format.

To convert to Unicode in Power Query, you would do something like this in a new custom column:

List.Transform(Text.ToList([Product ID]), each Character.ToNumber(_))

Unfortunately, this produces numerical outputs too large for the Int.64 data type, but you could change data type to decimal and it may work for you.

I get the following output:

As I said, a strange idea, but hopefully gives you a new perspective on possible solutions.

Pete

[mahoneypat]

You could reference your orders query, disable load, remove all but the product and product ID columns, remove duplicates, and then merge that query into your products table, and expand the Product ID column.

Pat

[mahoneypat]

If I understand your scenario, it seems like you could add an index column to your product table, and then create a column with those same ids in your other table via a merge, look up value with list functions, or with a DAX column with LOOKUPVALUE.

Pat

[Mahendran_C_S]

Thanks for the response @mahoneypat . I tried the above one it works but it compares the Product ID of the product table with the product ID in the orders table and the index occurs in the orders table. But I don't want to compare the columns because it makes high complexity in performance.

Could you suggest few other ways to generate autonumber for the specific column? There is a function called autonumber() in Qliksense which does the same requirement. Is there any alternative for that function in power bi using M language or DAX? 
Thanks in advance.