Combine column value from multiple table

[duycao]

Hi there,

I'm seeking your help on how we can creating a new column that combines value from multiple columns from different tables.

In particular, I have at least 2 tables, for example

Table 1:

Customer Name |    ID   |     DOB

A                        

B

C

Table 2:

Customer Name |    ID   |     Address

A

B

E

F

Expected result: I wanna have the list of unique Customer name from Table 1, Table 2 as well as other tables as below

Customer Name

A

B

C

E

F

Thank you for your help!

[Anonymous]

The ID fields are the same in both tables? And I understand you want to do it in Power Query?

Best
D

[jdbuchanan71 · Accepted solution]

Hello @duycao 

You can create a calcualted table that give you the unique list from both tables like so.

Combined Table = 
DISTINCT(
    UNION(
        DISTINCT(Table1[Customer Name]),
        DISTINCT(Table2[Customer Name])
    )
)

You can continue to add the additional source tables in this one calculation.

Combined Table = 
DISTINCT(
    UNION(
        DISTINCT(Table1[Customer Name]),
        DISTINCT(Table2[Customer Name]),
        DISTINCT(Table3[Customer Name]),
        DISTINCT(Table4[Customer Name]),
        DISTINCT(Table5[Customer Name])
    )
)

[duycao]

Hi there, this works perfectly. Thank you!

[v-yiruan-msft]

Hi @duycao ,

If your problem has been resolved, could you please mark the helpful post as Answered? It will help anyone in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

Rena