cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
matdune56 Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Combining same field from 3 tables into single list

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.
6 REPLIES 6
Super User
Super User

Re: Combining same field from 3 tables into single list

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] )
)

 

Community Support Team
Community Support Team

Re: Combining same field from 3 tables into single list

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.
matdune56 Regular Visitor
Regular Visitor

Re: Combining same field from 3 tables into single list

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

 

Used a DAX function in the end

matdune56 Regular Visitor
Regular Visitor

Re: Combining same field from 3 tables into single list

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.

Super User
Super User

Re: Combining same field from 3 tables into single list

@matdune56

What if it comes from more than one table?

matdune56 Regular Visitor
Regular Visitor

Re: Combining same field from 3 tables into single list

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).