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