How to Union Two Tables in Power BI

Combining data from different reports can feel like putting together a puzzle, especially when those pieces come from different periods or categories. If you've ever dealt with monthly sales exports, regional reports, or separate marketing campaign results, you know the annoyance of trying to stack them into one neat, comprehensive table. Fortunately, Power BI makes this process straightforward once you know where to look. This article will show you exactly how to combine, or "union," two (or more) tables in Power BI using both the user-friendly Power Query Editor and the powerful DAX language.

First, Let's Clarify: Append vs. Merge

Before we jump in, it's important to understand a key distinction in Power BI vocabulary: the difference between appending and merging queries. People often use these terms interchangeably, but they mean very different things.

• Append (aka Union): This is what we are focusing on. Appending is like stacking tables on top of each other. If you have a table of January sales and a table of February sales, appending them creates a single table with all sales from both months, one after the other. You add more rows.
• Merge (aka Join): Merging is like connecting tables side-by-side. If you have a table with sales transactions and another table with customer details, you would merge them using a common column (like CustomerID) to add columns like CustomerName or CustomerCity to your sales records. You add more columns.

Think of it like this: Appending stacks Lego bricks to make a taller tower. Merging connects them side-by-side to make a wider wall. For this guide, we’re focused on building that taller tower.

Method 1: The Simple Route with Power Query's 'Append Queries'

For most data preparation tasks, the Power Query Editor is your best friend. It provides a visual, step-by-step interface for cleaning and shaping your data before it even hits your Power BI report canvas. Appending tables here is the recommended method for 90% of use cases because it's intuitive and efficient.

Let’s imagine we have two separate tables: one for our sales in the first half of the year (H1_Sales) and one for the second half (H2_Sales). Both tables have the same columns: OrderID, Product, SaleDate, and SaleAmount.

Step-by-Step Instructions

1. Load Your Data into Power BI

First, get your data into Power BI Desktop. Go to the Home tab, click Get Data, and select your source (e.g., Excel workbook, CSV files). Load both your H1_Sales and H2_Sales tables. You should see them appear in the Fields pane on the right.

2. Open the Power Query Editor

With your data loaded, you need to open the engine room of Power BI: the Power Query Editor. Go to the Home ribbon and click on Transform Data. This will launch a new window where all the data shaping magic happens. You’ll see your queries (your tables) listed on the left.

3. Locate and Use the 'Append Queries' Option

Now it’s time to combine the tables. Click on the query you want to start with (e.g., H1_Sales) in the left-hand pane. Next, in the Home ribbon of the Power Query Editor, find the Append Queries button.

You have two choices here:

• Append Queries: This will append the second table into the first one, modifying your original H1_Sales query. This is a destructive action because your original H1_Sales table will now contain data from H2 as well.
• Append Queries as New: This creates a completely new query that contains the combined data from both tables, leaving your original H1_Sales and H2_Sales queries untouched. This is almost always the best practice. It keeps your source data clean and makes your Power Query steps easier to debug later.

Select Append Queries as New.

4. Configure the Append Window

An "Append" dialog box will pop up. Since we're combining two tables, the default view is sufficient. The "Primary table" will be the one you already selected (H1_Sales). For the "Table to append to the primary table," simply select your second table (H2_Sales) from the dropdown menu.

Pro Tip: If you have three or more tables to combine (e.g., quarterly sales reports), you can select the "Three or more tables" option. This will give you a box where you can add multiple tables to the append list.

Click OK.

5. Rename and Verify Your New Table

Power Query will instantly create a new query, likely named "Append1". This new query contains all the rows from H1_Sales stacked on top of all the rows from H2_Sales. A great final housekeeping step is to give this newly combined table a meaningful name like "Full_Year_Sales." Right-click the query and choose Rename.

6. Close & Apply

Once you are happy with the combined table, click the Close & Apply button in the top-left corner of the Power Query Editor. This saves your changes and loads the new, complete table into your Power BI data model, ready for you to use in your reports and dashboards.

What If My Column Names Don't Match?

This is where Power Query shines. It’s smarter than you might think. Let's say your H1_Sales table has a column called SaleAmount but your H2_Sales table has it named Revenue. When you append them, Power Query will handle it gracefully:

• It will create a new table with both the SaleAmount column and the Revenue column.
• For the rows coming from H1_Sales, the Revenue column will be filled with null values.
• For the rows coming from H2_Sales, the SaleAmount column will be filled with null values.

While this is functional, it’s not ideal for clean analysis. The best solution is to rename the columns in Power Query to be identical before you perform the append step. This ensures all your values end up in a single, unified column.

Method 2: The Advanced Route Using the DAX UNION Function

While appending in Power Query is perfect for data preparation, there are times you might need to combine tables "on the fly" within your data model. This is where DAX (Data Analysis Expressions) comes in. You can use the UNION function to create a new calculated table right from the report view.

When would you use DAX UNION instead of Power Query?

• Dynamic Scenarios: When the tables you want to union are themselves the result of other DAX calculations or filters.
• Data Model Simplicity: If you prefer to perform a simple union without returning to the Power Query Editor.
• Keeping It Virtual: When you require a combined table for a specific measure but don't want to physically add another table to your final model.

Key Rule for DAX UNION

Unlike Power Query's flexible approach, the DAX UNION function has one very strict rule: all tables in the union must have the exact same number of columns. If they don't, DAX will throw an error. The column names don't have to be identical - the final table will inherit the column names from the first table in your formula - but the column count is non-negotiable.

Step-by-Step Instructions

1. Go to the Data View

In Power BI Desktop, navigate to the Data view by clicking the table icon in the far-left vertical pane. This is where you can see the raw data inside your tables.

2. Create a New Calculated Table

From the ribbon at the top, select the Table tools tab, and then click New table.

This will open up the formula bar, waiting for you to enter your DAX expression.

3. Write The UNION Formula

Now, write your formula using the UNION function. The syntax is very simple. Just list the tables you want to combine as arguments.

Using our H1/H2 sales example, the formula would be:

Full Year Sales (DAX) = UNION('H1_Sales', 'H2_Sales')

Press Enter. Power BI will execute the DAX code and generate a new, calculated table named Full Year Sales (DAX), which contains all the rows from H1_Sales followed by all the rows from H2_Sales. You’ll see this new table appear in your Fields list.

Which Method Should You Choose? A Quick Comparison

Both methods achieve the same goal, but they operate at different stages of the data pipeline and have different strengths.

• Use Power Query (Append Queries) for…
• Use DAX (UNION) for…

Final Thoughts

Mastering how to stack or "union" tables is a fundamental skill that transforms fragmented datasets into a powerful, cohesive asset in Power BI. As we’ve seen, Power BI gives you two excellent and straightforward paths to get there. For your primary data cleaning and preparation, stick with 'Append Queries as New' in the Power Query Editor. For more dynamic, in-memory table calculations that live inside your model, the DAX UNION function is an incredibly useful tool.

Before you can even get to cleaning and appending, though, you have to find and connect all your data in the first place - a frequent source of headaches and wasted time. We created Graphed to erase that manual step entirely. Rather than downloading CSVs from ten different apps and piecing them together in Power BI, we let you create unified dashboards directly by connecting to apps like Google Analytics, Salesforce, Shopify, and Facebook Ads. Just describe your combined analysis in plain English ("Show me Facebook Ad spend vs Shopify revenue by campaign"), and we automatically build the dashboard - no prepping, no Power Query needed.