Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Combining 2 tables without matching rows

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

 

Need Help
Show and Tell
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

6 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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."

  • mangaus1111's avatar
    mangaus1111
    Solution Sage
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?