Attach two tables

[Jul1An]

Hello everybody,

I have a question regarding merging two tables. These have the same columns in parts, but also different ones. Both tables come from different databases but represent a similar theme. It looks something like this:

Table 1

R_ID | Date | Coloumn A | Column B

123 | 01012022 | Abc | Def

Table 2

R_ID | Date | Column 1 | Column 2

456 | 01012023 | Wer | Gfd
Can someone help me to make sure that these tables are listed completely, analogous to the example below:

Desired result table

R_ID | Date | Coloumn A | Column B | Column 1 | Column 2

123 | 01012022 | Abc | Def | null | null

456 | 01012023 | null | null | Wer | Gfd

[BiNavPete · Accepted solution]

Hi @Jul1An 

In Power Query you just need to append one table to the other. PQ will automatically create the nulls in Cols A and B and Cols 1 and 2 as you want to see them.

A bit trickier in DAX but this syntax works. In PBI desktop go to New table in the ribbon and use the DAX below.

JoinedTable =

UNION(

    SELECTCOLUMNS(Table1,

                        "R ID", [R ID],

                        "Date", [Date],

                        "Col A", [Col A],

                        "Col B", [Col B],

                        "Col 1", Blank(),

                        "Col 2", BLANK()

    ),

    SELECTCOLUMNS(Table2,

                        "R ID", [R ID],

                        "Date", [Date],

                        "Col A", Blank(),

                        "Col B", Blank(),

                        "Col 1", [Col 1],

                        "Col 2", [Col 2]

    )

)

The UNION Keyword appends tables in DAX but the column names must be the same. The SELECTCOLUMNS enables you to add blanks for the missing columns.

Hope that solves your problem.

Pete