How to Append Two Tables in Power BI Using DAX

Combining two separate tables into one comprehensive dataset in Power BI is a fundamental step toward building smarter, more insightful reports. Whether you're trying to merge monthly sales reports or combine performance data from different marketing campaigns, stacking your tables makes analysis simpler. This tutorial will walk you through exactly how to append tables directly within Power BI using a simple yet powerful DAX function.

When (and Why) Should You Append Tables?

Appending is the process of stacking tables on top of each other. Think of it like taking two spreadsheets with identical columns and pasting the rows from the second sheet right below the rows of the first. You end up with a single, longer table that contains all the data from the original tables. This is different from merging or joining, which involves adding new columns to a table from another table based on a common value (like looking up a customer's name using an ID).

Here are a few common scenarios where appending tables is the perfect solution:

• Consolidating Time-Based Data: You have sales data broken out by month or quarter into separate tables (e.g., January_Sales, February_Sales). You want to analyze the whole year in one go.
• Combining Regional Data: Your sales or customer information is split by location, like a US_Customers table and an EU_Customers table. Appending them creates a global customer list.
• Unifying Different Data Logs: You might have website clicks logged in one table and social media leads in another. If the core data structure is similar (Date, Source, Conversion), you can append them to analyze your overall conversion funnel.

The end goal is always the same: to create a single, master table that simplifies creating visualizations, writing measures, and building relationships in your data model. Cleaning up your data sources into one unified table is one of the most effective things you can do to make your reports easier to manage.

Unifying Your Data with DAX: Meet the UNION Function

While appending can be done in Power Query Editor, sometimes you need to perform this operation on tables that already exist within your data model or are created by another DAX expression. This is where the UNION function comes in.

The UNION function is a table function in DAX, meaning its output is a brand-new table. Its job is to take two or more tables as input and return a single table containing all the rows from each input table.

The basic syntax is uncomplicated:

New_Table_Name = UNION(<Table1>, <Table2> [, <Table3>]…)

Before you jump in, there are two golden rules for UNION to work smoothly:

1. Matching Number of Columns: All tables you're trying to append must have the exact same number of columns.
2. Compatible Data Types: For the best results, the columns in each position should have matching data types (e.g., the first column of Table1 should be a date, and the first column of Table2 should also be a date). Power BI will try to convert them if they differ, but it's best to ensure they are consistent to avoid errors.

Additionally, while UNION will technically work even if the column names are different (it combines columns based on their position, not their name), your final appended table will inherit its column headers from the first table in the formula. For clarity and predictable results, always make sure your column names match across all tables before appending.

Step-by-Step: Appending Two Tables with a DAX Formula

Let's run through a practical example. Imagine you manage sales for a global company and have your sales data in two different tables: Sales_NA (for North America) and Sales_EU (for Europe). You want to create a single Global_Sales table for your main dashboard.

Step 1: Check Your Tables

First, take a look at your source tables. Let's assume they look something like this:

Table 1: Sales_NA

Table 2: Sales_EU

Perfect. Both tables have four columns, and the column names and data types are identical. They're ready to be appended.

Step 2: Create a New Table for the Appended Data

You can't add rows to an existing table with this method, instead, you'll create a new, third table that contains the combined data. In Power BI Desktop, go to the Data View, then on the ribbon select Table tools and click on New table.

This will open the formula bar, where you'll write your DAX expression.

Step 3: Write Your DAX Formula

Now it's time to write the UNION formula. Since we want to combine Sales_NA and Sales_EU, and create a table called Global_Sales, the formula will be:

Global_Sales = UNION(Sales_NA, Sales_EU)

Type this into the formula bar and press Enter. Power BI will execute the command and generate your new table instantly.

Step 4: Review Your New Combined Table

After you press Enter, the new Global_Sales table will appear in your Fields pane and be visible in the Data View. It should contain all the rows from both the Sales_NA and Sales_EU tables.

Result: Global_Sales

A quick verification is to check the row counts. If Sales_NA had 2 rows and Sales_EU had 2 rows, your new Global_Sales should have 4 rows. Now, you can use this combined table in your data model, build relationships, and create visuals from a single, unified data source.

Troubleshooting Common Appending Issues

Things don't always go smoothly. Here are a few common hiccups and how to fix them:

Problem: My tables don't have the same number of columns!

If you try to append tables with a different column count, DAX will give an error stating that "The number of columns in the two tables must be the same." The easiest fix is to normalize your tables before using DAX:

• Open Power Query Editor (click "Transform data"),
• Find your source tables,
• Remove extra columns or add blank/null custom columns to match structures.

Problem: I'm getting strange results or data type errors.

This usually happens when corresponding columns have different data types— for example, Amount is Whole Number in one table and Text in another. To fix this:

• Open Power Query Editor,
• Select the mismatched columns,
• Change their data types to be consistent across all tables.

Problem: I have duplicate rows after appending.

By default, UNION includes all rows, even duplicates. To get a unique list of rows, wrap your UNION inside DISTINCT. For example:

Unique_Customers = DISTINCT(UNION(US_Customers, EU_Customers))

This appends the tables and then removes duplicate rows.

What About Appending More Than Two Tables?

The UNION function isn't limited to just two tables. You can list multiple tables within a single formula. For instance, to combine four quarterly tables:

Annual_Sales = UNION(Sales_Q1, Sales_Q2, Sales_Q3, Sales_Q4)

It’s a cleaner and more manageable way to consolidate multiple datasets.

Final Thoughts

Appending tables in Power BI with the DAX UNION function is an essential skill for managing and simplifying your data. Stacking related datasets into a single master table makes report building faster, easier, and more reliable for everyone.

Pulling data from various platforms—Google Analytics, Facebook Ads, Shopify, Salesforce—can be time-consuming. To streamline this process, we built Graphed — which connects to all your marketing and sales platforms automatically. Keep your data unified and up-to-date effortlessly, and focus more on deriving insights than manual data prep.