Appending Selected Columns from Different Tables

[newgirl]

Hi, guys!

My situation is I have 3 different transaction tables with different info provided but each table has some common columns that I would like to combine to create a new table.

For example I have these basic sample sets:

Table 1

Date1	Name1	Client Code1	Type1
1/1/2020	NewGirl	5000123	Retailer

Table 2

Name2	Date2	Color	Client Code2
NewBoy	1/1/2020	Blue	5000124

Table 3

Amount	Date3	Name3	Client Code3
5000	1/1/2020	NewKid	5000125

and this is my expected output:

New Table

Client Code	Name
5000123	NewGirl
5000124	NewBoy
5000125	NewKid

I was hoping to create a new table using DAX.

[Anonymous · Accepted solution]

Hi  @newgirl ,

Here are the steps you can follow：

1. Create calculated column.

New Table =
UNION(
    SELECTCOLUMNS('Table 1',"Client Code",[Client Code1],"name",[Name1]),
    SELECTCOLUMNS('Table 2',"Client Code",[Client Code2],"name",[Name2]),
    SELECTCOLUMNS('Table 3',"Client Code",[Client Code3],"name",[Name3]))

2. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[newgirl]

Thank you so much!!

[amitchandak]

@newgirl , After some column renaming, you should be able to append the tables

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Or create a bridge table for client code and use that for analysis

https://www.youtube.com/watch?v=Bkf35Roman8

https://www.seerinteractive.com/blog/join-many-many-power-bi/