How to Append Columns in Power BI using Power Query Editor?

While working on Power BI, I was required to create a report using sales data from January to December, with each table having the same columns. To create the report, we needed to combine this data, and I learned that we can use the Append Columns method in Power Query Editor. I thought I would share with you what Append Columns in Power Query Editor in Power BI is and how to use it to append columns.

Additionally, we will discuss the following topics:

• Append Columns from the Same Table using Power BI Power Query Editor
• Append Tables with different columns using Power BI Power Query Editor
• Append Columns using Power BI DAX
• Difference between Append vs Merge in Power BI using Power Query Editor

What is an Append Columns in Power BI Power Query Editor?

Power Query’s “Append Columns” in Power BI is a feature that allows you to combine data from two or more tables by adding rows from one table to another. In simple terms, it stacks the rows of one table on top of the rows of another table, creating a single, larger table.

For example, if you have two tables of sales data for different months, appending them will create a single table with combined sales data for both months.

This differs from merging tables in Power BI, which typically combine columns based on a common field.

Append Columns in Power Query in Power BI

Take a Scenario where you work for a retail company that tracks monthly sales data in separate tables for each month. You have two tables: one for January sales and another for February sales. You want to combine these tables into one table to analyze the total sales for the year’s first two months.

According to this scenario, we have two SharePoint lists named January Sales and February Sales that contain the following columns with various data types:

Now follow the below steps:

1. Open Power BI Desktop and load the above SharePoint List in Power BI Desktop. Then, you can see the data presented in the Data pane.

2. Under the Home tab, click on Transform Data to open the Power Query Editor.

3. In the Power Query Editor, you will see your tables (e.g., JanuarySales and FebruarySales) listed in the Queries pane on the left.

4. Go to the Home tab, click on the Append Queries dropdown, and select Append Queries as New. This creates a new table from the appended data while keeping the original tables unchanged.

5. In the Append Queries dialog box, choose “Two tables” if you’re appending just the January and February sales tables. Then, select “January Sales” as the first table and “February Sales” as the second table. Finally, click OK.

6. A new query (e.g., Append1) will be created, containing the combined data from both tables. Review the data to ensure it has been appended correctly.

This is how to append columns using the Power Query editor in Power BI.

Append Columns from the Same Table in Power BI using Power Query

Here, we see how to append columns from the same table in the Power query editor.

Here, we have a table in Power BI, and I want to append a column.

Now follow the below steps:

Once the data is loaded, click on “Transform Data” to open the Power Query Editor. Next, navigate to the Home tab, click on the Append Queries dropdown, and choose “Append Queries as New”. This action creates a new table from the appended data, preserving the original tables.

In the Append Queries dialog box, choose “Two tables” since you’re appending data from the same table. Then, select the same table twice and click OK.

A new query will be created containing the combined data from both regions.

This way, you can append columns from the same table in Power Query Editor.

Append Tables With Different Columns in Power BI Power Query

When you append tables with different column names in Power Query, some table cells may display null values.

This occurs because Power Query attempts to match columns based on their names during the append operation.

If a column name in one table doesn’t exist in the other, there won’t be any data to fill those cells in the appended table, resulting in null values.

If you still need to append tables with different column names, you can rename the columns before performing the append operation.

Append Columns using Power BI DAX

This example shows how we can append columns without using the Power Query Editor.

Appending tables in Power BI without the Power Query Editor can be done using DAX (Data Analysis Expressions) in the Data Model. However, unlike Power Query, DAX doesn’t have a direct “append” function. Instead, you can achieve a similar result using the Power BI DAX UNION function.

We’re using the same table as before for the January and February sales. Now follow the below steps:

After loading the January and February sales data, click on “New table” under the Modeling tab.

Then, in the formula bar, put the below DAX expression.

AppendedTable = UNION('January Sales','February Sales')

Where:

• AppendedTable = This is the name given to the new table we’re creating by appending data from two existing tables.
• UNION() = This function combines the rows from two or more tables into a single table without duplicates.
• ‘January Sales’ = This specifies the first table we’re appending, which presumably contains sales data for the month of January.
• ‘February Sales’ = This specifies the second table we’re appending, which presumably contains sales data for the month of February.

When you go to the table view, you’ll see the AppendedTable, which contains data from both January and February sales.

This way, you can append columns without using the Power Query Editor.

Append vs Merge in Power BI and Power Query

The table below shows the contrast between the Power Query Append columns and the Power Query Merge columns.

Power Query Append	Power Query Merge
Combines rows from multiple tables vertically, stacking them on top of each other.	Combines columns from different tables horizontally based on a common key column(s).
Performs a union operation, simply stacking rows without considering common columns.	Performs a join operation, matching rows between tables based on specified key columns.
No specific key columns are required; it aligns columns based on name similarity.	Key columns must be specified from each table to perform the join operation.

I hope you follow all the steps above to append the column and know how to append it in Power BI.

Also, you may like some more Power BI Tutorials:

• Power BI Slicer Multiple Columns
• Power BI Measure Subtract Two Columns
• How to Compare Two Columns in Different Tables in Power BI
• Power BI Sum Multiple Columns