Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

 

mcrackin_0-1600900192733.png

 

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

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Anonymous 

Whats 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
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.