how to join TEXT column when the join must be only on INT column

[Rosner]

hello, 

i have pk field type text in Dim and in the Fact. 
e.g buyerid - AB05053.

now, i've been requested to connect only feilds from INT type. how do i implement this requirement ?

how the fact/dim will change ,what should i build or add to the tables ?
**it seems like i need to create interpretation table 

could you guide me through 

thanks 

[Anonymous]

Hi @Rosner ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[bcdobbs]

You need to create surrogate keys on the dimension. One way to do this is to use the index function in power query. You then merge back into your fact table and store only the integer surrogate key.

Guy in a cube has a good video https://m.youtube.com/watch?v=Zlu99RUtMRY

[Rosner]

hey, 
in the video they refear to xlsx as a source and always distinct the values and refear to the fact table

how do i do it when it's come from my dwh and i don't want to interfear
e.g: if there is new buyerid or change how it will be reflect back ?
do i need always to do those steps ?
it should be automatics

[Anonymous · Accepted solution]

Hi @Rosner,

Perhaps you can try to create a bridge table to mapping two table records. (the structure of bridge table: index, DIM id, Fact id )

These two field links to the two table id fields. Since you are mapping tables based on id instead of fact values, these relationship mapping don't changed even if you add or modify the table detail field values in these tables.
You can simply use the key(id) to find out the corresponding fields values.

Regards,

Xiaoxin Sheng