Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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,

6 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

      Used a DAX function in the end

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.