How to Append Columns in Power Query

SharePoint Online training courses

Are you aware that Power BI allows us to join two tables together? We can append two or three tables in Power BI because it is possible to attach multiple tables. This Power BI tutorial will assist you in adding columns to Power BI if you have no previous knowledge of appending tables.

  1. What is Power Query append columns?
  2. Power query append vs merge
  3. How to use append in Power Query
  4. How to append multiple columns from inside one table in Power Query
  5. Power Query append columns in the same table
  6. Working with Power Query append columns with different names
  7. How to work with Power Query append columns from other tables
  8. Power Query append data to an existing table

Power Query Append Columns

  • The Power Query Append operation creates a new query containing all the table’s rows.
  • In Power Query, the Append operation creates a new query that contains all rows from a first query followed by all rows from a second query.
  • A Power Query Append operation requires at least two queries. These queries can also be based on different data sources in Power BI.

Also Read: How to Filter Date using Power BI DAX

Power Query append vs merge

The below-mentioned table represents the difference between the Power Query Append columns and the Power Query Merge columns.

Power Query AppendPower Query Merge
Append means results of two or more queries in a table, which will be combined into one query.Merge is another type of combining queries that are based on matching rows, rather than columns. The output of Merge will be a single query.
Rows will be appended after each other, Columns will be the same number of columns for each query.The number of rows will be dependent on matching criteria between queries
and, the number of Columns will be dependent on what columns are selected in the result set
Power Query Append vs Merge

How to use append in Power Query

Let us see how we can append columns using the Power Query editor in Power BI.

In this example, I’ll append the Queries so that the data will be duplicated in the same table.

  • Open Power bi desktop and Load the data using the get data option.
  • Click on the transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
  • From the ribbon, under the Home option, choose the Append Queries option as highlighted below:
  • Select the Table that you want to concatenate, and click on the OK button.
Power Query append Columns
Power Query append Columns
  • In the below screenshot, you can see that the first Query has been appended with the data presented in the selected table.
Power Query append Columns example
Power Query append Columns example

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

Check out: How to merge columns in Power Query

How to append multiple columns from inside one table in Power Query

  • No, directly it is impossible to append multiple columns inside one table in the Power Query editor.
  • When we select the Append Query option in the Power Query editor, it completely duplicates the table.
  • Once the new queries have been appended, we can remove the columns that we want to delete from the appended Queries table.
  • This is how to append multiple columns inside one table.

Power Query append Columns in the same table

Let us see how we can append columns in the same table using the Power Query editor in Power BI.

  • Open Power Bi Desktop and load the data using the get data option. I have used the below-mentioned sample data in this example.
Power Query append Columns in the same table
Power Query append Columns in the same table
  • Click on the Transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
  • Select Home > Append Queries, select the arrow next to the command, and then select Append Queries as New.
Power Query append Columns in the same example
Power Query append Columns in the same example
  • Once the table has been combined, right-click on the Attribute column and choose to group by option. The values will be grouped as shown below:
= Table.Combine({Table1, Table1})
Power Query append Columns in same table
Power Query append Columns in the same table
  • Once the columns are grouped, expand the values, so that the values will be expanded. as shown below:
Power Query append Columns in same example
Power Query append Columns in the same example
  • From the ribbon, under the Home -> Remove Rows -> Remove Duplicates, the duplicates will be removed as shown below:
Example of Power Query append Columns in the same table
Example of Power Query append Columns in the same table
  • In the below screenshot, you can see that the columns have been appended to the same table using the Power Query editor in Power BI.
Power Query append Columns into the same table
Power Query append Columns into the same table

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

Also check: How to Merge Column in Power BI

Power Query append Columns with different names

  • No, it is possible to append columns with different names if both the tables have similar columns so that we can append the columns.
  • If the columns are not the same, then it displays the null values. We cannot append columns with different names.

Power Query append Columns from other tables

Let us see how we can append columns to two different tables in Power BI using the Power Query editor.

In this example, I have used the two different tables as highlighted below:

Table: 1

Power Query append Columns from other tables
Power Query append Columns from other tables

Table: 2

Power Query append Columns from different tables
Power Query append Columns from different tables
  • Make sure both the tables have the same columns so that we can append the columns. If the columns are not the same, then it displays the null value.
  • Open Power bi desktop and Load the data using the get data option. Click on the transform data from the ribbon in power bi desktop, it will redirect to the power query editor, where we can Merge columns from the table.
  • Select Home > Append Queries, select the arrow next to the command, and then select Append Queries as New.
  • In the Append Pop-Up window, select the two tables and choose the different table options and click on the OK button.
Power Query append Columns from different tables example
Power Query append Columns from different tables example

In the below screenshot, you can see that the column has been appended.

= Table.Combine({Sheet1, #"Sheet 2"})
Example of Power Query append Columns from different tables
Example of Power Query append Columns from different tables

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

Power Query append data to an existing table

Let us see how we can append data to an existing table using Power Query in Power BI.

  • By default, when we append data it will be appended to an existing table.
  • If we want to append data to the new table. Expand the Append columns option and choose Append Query as the New option.
power query append data to an existing table
power query append data to an existing table

This is how to append data to an existing table using Power Query in Power BI.

Also, you may like the below Power BI Tutorials:

This Power BI tutorial explains how we can append the columns using the Power Query editor in Power BI. Also, covered the below-mentioned headings:

  • What is Power Query append columns?
  • Power query append vs merge
  • How to use append in Power Query
  • How to append multiple columns from inside one table in Power Query
  • Power Query append columns in the same table
  • Working with Power Query append columns with different names
  • How to work with Power Query append columns from other tables
  • Power Query append data to an existing table
>