Power Query Challenge: Split by Multiple Delimiters | Merge All Columns

/in /by

Iranian Dr. Excel posted a challenge on LinkedIn:
How can you split columns when there are multiple delimiters? Ex:
Joe+Lisa+Francine/Kathy
Rita&Sal&Gene
Samantha/Denise

This was interesting for several reasons:

1. It’s very easy to do if you want a 1-and-done solution with just 3 delimiters. However,
2. If you want something truly dynamic, it involves solving 2 major problems with Power Query: splitting variable columns and merging all columns in a query.
3. I show an ugly but effective solution that I call The Elephant Through the Front Door Move.

First. When splitting columns with Power Query, a value gets hard-coded; e.g., if your initial split goes into 5 columns, the 5 is stuck, and if you later have 7 columns or 3 columns, that 5 is still there.
I found a solution to this here:

Split by Variable Columns in Power Query

Second. Sometimes we want to merge all of the columns in a query, but there’s no feature for that. But here is Power Query M-Code:

Merging of all columns in Power Query – regardless of their names

This was hard! But the solution I show is truly dynamic. If there are more delimiters in future data, they get picked up; if the number of columns grows or expands, Power Query will cooperate.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2