Creating a table based on distinct values within columns of existing tables

[Anonymous]

Hi all, I'm trying to create a key/reference table that would have distinct values from columns in other existing tables. Example in the image below:

I've tried a few suggestions in other posts but nothing works quite like this. Any help is appreciated!

[v-jingzhang]

Hi @Anonymous 

What’s the relationship between Order and Department columns when combining them to the same table? For each column, you can get the distinct values by a measure like: 

newTable = DISTINCT(UNION(VALUES(Table1[Order]),VALUES(Table2[Order])))

newTable2 = DISTINCT(UNION(VALUES(Table3[Dept]),VALUES(Table4[Dept])))

 This will create a table for Order and Department separately. But I cannot combine them without knowing the relationship between them.

Community Support Team _ Jing Zhang

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

[amitchandak]

@Anonymous , how order and department are related

we can union Table 1 and Table 2

And Table3 and Table4. After that only option I see is a cross join

CrossJoin( union(Table1,Table2),union(Table3,Table4))

Refer Dax Join

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Another option is, append and merge in power Query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query