alphanumeric columns

[powerbiexpert22]

is it okay to use below values (sample) for joining columns between two tables in power bi , these values are used to denote primary key of column, likewise we have such alphanumeric column values as primary key for other tables as well

BU_Key

--------------------------------------------
2146f4rr-18sf-q377-d11a-000s7g1165f1
1146j4mn-2fqw-w847-e14v-010k7b9g8

[Anonymous]

Your ideas is so great @rajendraongole1 

Hi, @powerbiexpert22 

Your use of such string columns as join keys does cause performance issues. For this reason, I created the following two tables to test its performance:

The query performance for this state is as follows:

My queries using numeric columns as join keys perform better:

There are performance gaps from small volumes of data, and if we create some metrics for these tables, the performance difference will be even greater. You can choose the right column as a join key depending on the situation you have.

Best Regards

Jianpeng Li

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

[powerbiexpert22]

Hi @Anonymous , is there a way to create  numeric primary key in power bi? can i use index function to create primary key

[Anonymous]

Hi, @powerbiexpert22 

You can use the DAX function to create an Index calculated column to be used as a primary key though. But when you use these two calculated columns to create a relationship between two tables, you are prone to circular dependency issues. Using my previous data as an example, I used the following two DAX expressions to create a calculated column in each of the two tables:

IndexColumn = RANKX(ALL(Customers), 'Customers'[CustomerID], , ASC, DENSE)

IndexColumn = RANKX(ALL('Orders'), 'Orders'[CustomerID], , ASC, DENSE)

So, I would recommend creating the index column in the data source or in PowerQuery to ensure that there are no circular dependencies.

In the future, if you have any new questions about creating primary key columns, you can post a new thread so that the rest of the community can help you in a timely manner.

Best Regards

Jianpeng Li

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

[mickey64]

Yes, you can use them.

The maximum number of characters you can use is 32,000.

Data types in Power BI Desktop - Power BI | Microsoft Learn

[powerbiexpert22]

Hi @mickey64 

will there be any performance related concerns for using these text columns for joining, as per my understanding we should use numeric columns for better performance

[rajendraongole1]

Hi @powerbiexpert22  Adding to the performance , yes it is true when comparisons and joins on alphanumeric columns can be slower because string comparisons are generally more complex than numeric comparisons.Usually result in faster comparisons and joins due to simpler comparison operations as power bi use the columnar storage.

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[mickey64]

I have no insight into performance.