Combining same field from 3 tables into single list

[Anonymous]

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

[v-jiascu-msft · Accepted solution]

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,

[Anonymous]

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

Used a DAX function in the end

[Anonymous]

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.

[AlB]

@Anonymous

What if it comes from more than one table?

[Anonymous]

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]

Hi @Anonymous

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