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
matdune56
Helper I
Helper I

Combining same field from 3 tables into single list

Hi

 

I hae queries which brings in company information for 3 different sources.


Each of the resulting tables has a Company Name Column

 

What I want to do is produce a single list of distinct companies from combining the 3 tables

 

e.g

 

table1- field company Name

Company1

Company2

 

table2- field company Name

Company3

Company4

 

table3- field company Name

Company5

Company6

 

Resulting table - field company Name

Company1

Company2

Company3

Company4

Company5

Company6

 

thanks

 

Mat

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi Mat,

 

Maybe the Distinct function is also needed.

 

NewTable =
DISTINCT (
    UNION (
        VALUES ( Table1[Company Name] ),
        VALUES ( Table2[Company Name] ),
        VALUES ( Table3[Company Name] )
    )
)

 

Best Regards,

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

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi Mat,

 

Maybe the Distinct function is also needed.

 

NewTable =
DISTINCT (
    UNION (
        VALUES ( Table1[Company Name] ),
        VALUES ( Table2[Company Name] ),
        VALUES ( Table3[Company Name] )
    )
)

 

Best Regards,

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

thank you both for such a quick reply. Works perfectly.

 

Used a DAX function in the end

sorry, one last question.

 

How would I add a field that denotes which table(s), the company name came from. Is it possible.

 

really apprecaite your help on this.

@matdune56

What if it comes from more than one table?

Hi

 

the table that's been created has a single field, with all (distinct) company names from each of the 3 tables.

 

By looking at this new table, I'd want to user to know whether it had come from Table 1, Table 2, Table 3 or a combination of the 3 (the company name could be in more than one table).

AlB
Super User
Super User

Hi @matdune56

 

I think this would best done in the query editor but if you want to do it in DAX you can create a calculated table with this code:

 

 

NewTable =
UNION (
    VALUES ( Table1[Company Name] ),
    VALUES ( Table2[Company Name] ),
    VALUES ( Table3[Company Name] )
)

 

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.