How to Append Columns in Power BI

Bringing columns from different tables together is a fundamental step in building a useful Power BI report. While you might be thinking of "appending" columns, in the world of Power BI, this process of adding columns from another table is actually called a "merge." This tutorial will guide you step-by-step through merging tables in Power BI’s Power Query Editor to enrich your data and prepare it for analysis.

"Append" vs. "Merge": Understanding the Difference in Power BI

Before we go any further, it's essential to clarify a common point of confusion. In Power BI, "append" and "merge" are two distinct operations:

• Appending Queries: This is like stacking tables on top of each other. If you have January sales data in one table and February sales data in another, you would append them to create one master table with all sales data. You end up with more rows.
• Merging Queries: This involves joining two tables side-by-side based on a shared column. If you have a sales table with a ProductID and a separate product details table with that same ProductID, you would merge them to add columns like ProductName or ProductCategory to your sales table. You end up with more columns.

So, when you want to "append columns," the feature you're looking for in Power BI is Merge Queries. Let's walk through how to use it.

Preparing Your Data for a Merge

A successful merge depends on having your data set up correctly. Rushing this step often leads to errors and incorrect results. Before you start, take a moment to check two critical things:

1. A Common Column (The "Key"): Both tables you want to merge must share at least one column that contains matching values. This is often an ID column, like ProductID, Email, CustomerID, or OrderID. This shared column acts as the bridge connecting your two tables.
2. Consistent Data Types: Power Query is sensitive about data types. If the ProductID column is a "Number" in one table and "Text" in another, the merge will fail. Ensure the key columns in both tables are the same data type. You can easily change this in the Power Query Editor by selecting the column and using the "Data Type" dropdown in the Home tab.

Taking a minute to verify these points will save you a lot of troubleshooting time later.

Step-by-Step Guide to Merging Queries in Power BI

Once your data is ready, you can start the merging process. All of this action happens in the Power Query Editor, which is Power BI’s data transformation engine.

First, open the Power Query Editor by clicking on the Transform data button on the Home tab of the main Power BI Desktop window.

1. Initiate the Merge Operation

Inside the Power Query Editor, you'll see your queries (tables) listed in the left-hand pane. Select the primary table you want to add columns to. In our example, this would be our main sales data table.

With that table selected, navigate to the Home tab in the ribbon and click the Merge Queries dropdown.

You have two choices:

• Merge Queries: This adds the new columns directly into your currently selected table.
• Merge Queries as New: This creates an entirely new, third table that contains the merged data, leaving your original two tables unchanged.

For most cases, "Merge Queries" is the right choice as it modifies your existing query. If you want to preserve your original tables as they are, choose "Merge Queries as New."

2. Configure the Merge Dialog Box

After clicking "Merge Queries," a new dialog box will appear. This is where you configure the merge.

1. Select Your Tables: Your primary table will already be selected at the top. In the dropdown menu below it, select the second table - the one that contains the columns you want to add.
2. Select the Matching Columns: This is the most crucial step. Click on the header of the common column (the key) in the first table, then click the header of the matching common column in the second table. Power Query will highlight them and show you an estimate of how many rows match at the bottom of the window.
3. Choose the "Join Kind": The "Join Kind" tells Power BI how to handle matching and non-matching rows between the two tables. There are several options, but you'll use these three the most:

For most scenarios where you're simply enriching a primary table with more detail (like adding product names to sales data), Left Outer is the perfect choice.

Once you've configured these settings, click OK.

3. Expand the New Column

After you click OK, you won't see your new columns immediately. Instead, Power Query will add a single new column to your table. This new column header will match the name of the second table you merged, and each cell will say "Table."

This "Table" column is a structured column containing all the data from your second table for each matching row.

To get your individual columns, click on the expand icon (two arrows pointing in opposite directions) in the column header.

A dropdown will appear, listing all available columns from the second table. Now you can:

• Deselect any columns you don't need to add. For example, you probably don't need to bring in the ProductID column again since you already have it.
• Most importantly, uncheck the box that says "Use original column name as prefix." If you leave this checked, your new columns will have long names like Products.ProductName and Products.ProductCategory. Unchecking it gives you cleaner column names like ProductName.

Click OK.

And that's it! Your new columns from the second table now appear in your primary table, perfectly aligned with the correct rows.

Finally, remember to click Close & Apply in the top-left corner of the Power Query Editor to load your newly transformed data into your Power BI model.

Bonus Tip: Dealing with "Fuzzy" Matches

What if your key columns don't match exactly due to typos, misspellings, or formatting issues (e.g., "Apple Inc." vs. "Apple")? Standard merging will fail here. Fortunately, Power Query has a powerful solution.

In the Merge dialog box, check the box for "Use fuzzy matching to perform the merge." This opens up several options:

• Similarity threshold: A number between 0 and 1. A threshold of 1.0 means an exact match is required, while a lower value like 0.8 allows for small variations.
• Match by combining text parts: This can help match "John Smith" with "Smith, John."
• Ignore case: Makes "apple" and "Apple" match.

Fuzzy matching can be a lifesaver when dealing with messy, human-entered data, but use it with care as it requires more processing power and can sometimes produce unexpected matches.

Final Thoughts

Merging queries is a foundational skill in Power BI that unlocks deeper analysis. By joining disparate tables based on a common key, you create a single, enriched dataset that provides a complete view of your business operations. This process allows you to turn raw, disconnected data tables into a cohesive model ready for powerful report building.

Of course, prepping and merging data across different marketing and sales platforms like Google Analytics, Shopify, Facebook Ads, and Salesforce can turn into a repetitive manual grind. We built Graphed to automate that entire workflow. It connects all your sources in one click and uses AI to let you build complete, real-time dashboards just by describing what you want to see - no need for manual merges, data cleaning, or wrestling with Power Query. You get straight to the insights without the setup.