topic Re: Need a new table with unique values and merging multiple tables in DAX Commands and Tips

Need a new table with unique values and merging multiple tables

vibhoryadav23 — Wed, 20 Jul 2022 13:29:15 GMT

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.

Re: Need a new table with unique values and merging multiple tables

VahidDM — Tue, 19 Jul 2022 00:42:30 GMT

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

Re: Need a new table with unique values and merging multiple tables

vibhoryadav23 — Wed, 20 Jul 2022 10:23:34 GMT

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!

Re: Need a new table with unique values and merging multiple tables

VahidDM — Wed, 20 Jul 2022 12:14:58 GMT

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

Re: Need a new table with unique values and merging multiple tables

vibhoryadav23 — Wed, 20 Jul 2022 13:31:35 GMT

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!

Re: Need a new table with unique values and merging multiple tables

Anonymous — Thu, 21 Jul 2022 10:07:01 GMT

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