Forum Discussion

Jul1An's avatar
Jul1An
Frequent Visitor
3 years ago
Solved

Attach two tables

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

Need Help
  • BiNavPete's avatar
    BiNavPete
    3 years ago

    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
     

1 Reply

  • BiNavPete's avatar
    BiNavPete
    Resolver III
    3 years ago

    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