Using composite keys: better to concatenate the two columns in Power Query or as DAX?

[OneWithQuestion]

I have two tables that have no unique key to them.

I have to merge two columns together to get a unique composite key that I can use to join them on.

Is it more efficient for PBI if I do the creation of the new column in PowerQuery or after the data is loaded and do it in DAX as a calculated column there?

[v-kelly-msft · Accepted solution]

Hi  @OneWithQuestion ,

Maybe Union() function can help achieve this:

See my 2 sample tables below:

Then using below dax expression to create a new table with a unique key:

new table = UNION(DISTINCT('Table'[Category]),DISTINCT('Table 1'[Category]))

And you will get a new table as below:

See attached for my sample .pbix file.Hope this is what you need.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@OneWithQuestion - Probably Power Query but doubt it will matter that much unless you are talking tens of millions of rows.