Power Query Merge Columns

More about working with Excel’s Get & Transform (aka Power Query). this time, we explore merging columns and the “gotchas” that you need to know about.

0:49 Description of the 3 warnings
1:09 Showing the problem
2:00 Problem 1: The source columns are gone
2:17 Problem 2: The columns are out of order
2:50 Merge properly
3:09 Problem 3: Excess delimiters
3:29 A hack for replacing the excess delimiters
5:12 Using TEXTJOIN to do the work in native Excel

The problems:
1. The source columns disappear after you merge. If you need the source columns to do other calculations or transformations, you’ll need to duplicate those columns, then merge the duplicates.

2. The final order after merging depends on the order that you highlighted the columns. In this video, I highlighted column 3 first, and then 1, 2 and 4. Yup! The data merged out of order.

3. If you have blank cells, Power Query will add a delimiter any way. And those delimiters are tricky to get rid of! I show you a hack on how to clean those out.

To compare, I show you how to do the same thing in native Excel by using TEXTJOIN

Thanks again to:
Ernie Johnson and
Puneet Gogia ExcelChamps.com

Contact me:
oz1.depot@gmail.com

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2