Combining 2 tables without matching rows

[Anonymous]

Hello, is it possible to combine 2 tables some column match but some aren't match without any matching rows. Just wanted to make this 2 dataset saved as 1 table?

Here's the column for my 1st data set

Here's the column for my 2nd data set

For the columns that are matching the data would just append and the other columns that arent present on the 1st table will beadded. Thanks in advance

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Table1:

Table2:

Please try following measure to create a new table to combine two tables:

Table = UNION(
    SELECTCOLUMNS('Table1',"Column1",'Table1'[Column1],"Column2",'Table1'[Column2]),
    SELECTCOLUMNS('Table2',"Column1",'Table2'[Column1],"Column2",'Table2'[Column2])
)

Is this the result you want?

Best regards,

Yadong Fang

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

[Anonymous]

Hello, tried this and works like magic! Thanks a lot!!

[mangaus1111]

Hi @Anonymous ,

you can try maybe with

DISTINCT(UNION(Table1,Table2))

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

[Anonymous]

Do i need to create a new table that consist all of the columns then use union all? Actually I have total of 3 table, 1 for mapping and 2 for may dataset. Im thinking to create 1 more table > use union all > created union table to make a relationship with my mapping table? Is it possible?

[djurecicK2]

Hi @Anonymous ,

 You can create a calaculted column in the table 1 and use LOOKUPVALUE

https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax

to get values from the first column of table 2

[Anonymous]

Lookupvalue cant get duplicates, im having duplicate on column "employeeName" since the data was entered per month. Is there any other solution? Im getting this error because of duplicate "A table of multiple values was supplied where a single value was expected."