Create a new column in a table and get values from 4 separate tables

[Anonymous]

Hello Everyone,

I have 5 tables:

Table 1

Id 1	City
1	New York
2	London
3	Sydney

Table 2

Id 1	City
4	New Jersey
5	Dallas
6	Melbourne

Table 3

Id 1	City
7	Dubai
8	Mumbai
9	Delhi

Table 4

Id 1	City
10	Munich
11	Perth
12	Kolkatta

Table 5

Id 1	City
1
2
3
4
5
6

How do I get the city value in table 5 from all the 4 tables?

[PhilipTreacy]

@Anonymous 

Hi,

Please @ mention me in replies or I won't be notified that you've responded. Type @ then select my name.

Is it necessary to add a new column to an existing table?  Rater than just display the City corresponding to the ID in a visual?

Regards

Phil

[Anonymous · Accepted solution]

@PhilipTreacy Just found the solution:

Location = Lookupvalue(Table1[City],  Table1[ID], Table5[ID],
Lookupvalue(Table2[City],  Table2[ID], Table5[ID],
Lookupvalue(Table3[City],  Table3[ID], Table5[ID],

Lookupvalue(Table4[City],  Table4[ID], Table5[ID]))))

Thanks for your help

[PhilipTreacy]

Hi @Anonymous 

Download example PBIX file

Do you actually have Table5 which consist of just a single column of ID's? Or do you want to create a table from the other 4 tables?

If it's the latter you can use UNION to create this table.  From the Ribbon lick on Table Tools->New table and enter this DAX 

Table5 = UNION(Table1, Table2, Table3, Table4)

Regards

Phil

[Anonymous]

Hi Phil,

I appreciate your response to my question.
I actually have separate table (Table 5) in which I want to add all cities from the other 4 tables.