Need a new table with unique values and merging multiple tables

[vibhoryadav23]

Hi,

I need to combine values from multiple tables where I pull the unique values from a column from all the tables and then combine them. For example:

Table A
Region	Country	Concat	ColumnX
ME	UAE	MEUAE	1
NA	USA	NAUSA	1
LA	Chile	LAChile	2
LA	Argentina	LAArgentina	2

Table B
Region	Country	Concat	Example2
APAC	India	APACIndia	n
ME	UAE	MEUAE	y
NA	Canada	NACanada	y
LA	Argentina	LAArgentina	y

Output table
Concat (Unique)	Region	Country
MEUAE	ME	UAE
NAUSA	NA	USA
LAChile	LA	Chile
LAArgentina	LA	Argentina
APACIndia	APAC	India
NACanada	NA	Canada

Can you please help me with this? Thanks in advance.

[Anonymous · Accepted solution]

Hi @vibhoryadav23 ，

Is this table you want to achieve?

The following is process of my testing:

1.Open power query and append the two tables

You can get this table

2.Remove the column you don’t want to:

3.After modifying the order between the columns, right-click on the first column and select “Remove Duplicates”, finally you can get the following table as you want to achieve.

Best regards,

Community Support Team Selina zhu

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

[vibhoryadav23]

Hi Vahid,

Thanks for the solution but this doesn't work properly as I have some more columns in data. Is it possible to have just 'Concat (unique)' in the output?
Thanks!

[VahidDM]

Hi @vibhoryadav23 

Yes, Try this:

New Table = 
Var _Union = UNION('Table A','Table B')
return
SUMMARIZE(_Union,[Concat])

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[vibhoryadav23]

Hi @VahidDM ,

This doesnt work as well. Maybe I was not clear enough but there some extra columns in Table A and B which doesnt match between the tables. I have updated the example above.

Thanks!

[VahidDM]

Hi @vibhoryadav23 

Try this code to create a new table with DAX:

New Table = 
Var _Union = UNION('Table A','Table B')
return
SUMMARIZE(_Union,[Concat],[Country],[Region])

output:

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube