All courses available in-class and remotely. Remote East (AEST, ACST). Remote West (AWST)

Brianna Higgins Jul 29

Introduction 

Power Query is a data preparation and transformation engine with a GUI. It can fetch data from various sources and has a Power Query Editor for its extraction, transformation, and load (ETL) processing of data. Since the engine is available in many services and products, the data storage destination usually depends on where Power Query was used. Today we will learn How to use Power Query to merge columns. 

what is power query

Source 

 

Merging Columns

Power Query allows us to merge two or more columns of Text Data Type in our query. We can combine columns to replace them with one column or create a new one adjacent to our merged columns. It's a nice idea to make a new column and keep the original columns so you can avoid any errors that may happen later. Users commonly ask, "How do I combine columns in Power Query?." So, today we shall learn "How to use Power Query to merge columns"

merging columns power query

Source

Merge Columns to Replace Existing Columns

We will use the MS Excel Power Query Merge Columns feature in this example. On merging columns, the selected columns turn into a single column called Merged. The original columns will no longer be available. In the table below, for instance, we will merge OrderID and CustomerID.

  1. Locate a previously loaded query from Power Query Editor, select a cell in the data, and select query>Edit. This link teaches how to create, load, or edit a query in Excel
  2. Columns must be Text data type. If not, then select the column, and then click Transform > Data Type > Text.
  3. Select the columns (contiguously or discontiguously) by pressing Shift+Click or CTRL+Click on each subsequent column that needs to merge. The order of selection will set the order of the merged values.
  4. Click Transform > Merge Columns.
  5. Specify a separator to insert between each merged column in the Merge Columns dialog box. We can also select from available predefined separator values, or specify custom separator values.

how to merge columns power query

Source

  1. Click OK. Click to learn how to Rename a column. The result is as shown below:

how to merge columns power query

Merge Columns to Create New Columns 

In this example, we will insert a custom column into the query table and use a custom column to concatenate text columns. The merged columns will still be available along with the new merged column in the query table.

Let us see how we can merge OrderID and CustomerID, separated by a space character:

  1. Locate a previously loaded query from Power Query Editor, select a cell in the data, and select query>Edit. Learn how to create, load, or edit a query in Excel on this link
  2. Columns must be Text data type. If not, select the column and click Transform > Data Type > Text.
  3. Click Add Column > Custom Column. The Custom Column dialog box will appear.
  4. Select the first column in the Available Columns list, and click Insert. We can also double-click the first column. The column will be added to the Custom Column Formula box immediately after the equal sign (=). Press Ctrl + Space in the Custom Column Formula box to display columns and other elements of the formula.
  5. Enter an ampersand (&) in the Custom Column Formula box after the first inserted column. The ampersand (&) is the AND operator which combines values in Power Query in the same way as in Excel. Additional separators can also be specified, for example, using a space character by entering" "after the ampersand (&).
  6. Enter another ampersand (&) after the space character in the Custom Column Formula box.
  7. Select the second column in the Available Columns list, and click Insert. We can also double-click the second column.

Merge Columns to Create New Columns power query

Source 

  1. Press OK. The result is as shown below:

Merge Columns to Create New Columns power query

Source

Conclusion

Power Query (also known as Get & Transform data in Excel) allows us to import or connect to external data and shape it. For example, change a data type, remove a column or merge data tables as per requirement. We can load our query into Excel to create charts and reports. We can refresh the data periodically to keep it updated. Power Query is available on Excel for Windows, Excel for Mac, and Excel for the Web. 

Power Query is also part of Power BI and is used to perform the ETL Transformation and load data. Power Query is also called Query Editor. We can use the Power Query Editor to connect data sources, modify as per requirements, shape and transform data. You can later load that model into Power BI Desktop (or web service). It provides the same Power query merge rows based on column and Power query merge Excel tables with different columns features. 

In this post, we learned How to Merge Columns in Power Query. Check out our fantastic PowerBI Courses and Microsoft Excel Courses to learn more.

Related Topics

Contact Us

Why Nexacu?

Valued by Individuals

4.72 / 5
Over 43926 Reviews

Trusted by Business

Procured by Government

Awards and Accreditations

Follow us