Power Query: Split by Variable Columns (BONUS: Road Trip Summary)

It's easy to use Power Query to split by, say, 5 columns. But, what if your data changes and needs to be split by 3 columns or 10 columns? That's trickier--especially since Power Query will hard-code that 5 in the query and will ignore future changes.

This video shows how to get Power Query to cooperate. It takes a few steps, but we get it done!

Also. You'll get to see where I've been during my time away. I finally went on a road trip that I'd been putting off for 7 years.

#Roadtrip
#SplitVariableColumns
#SplitColumnsinExcel

For a list of my Excel courses at Lynda/LinkedIn:

There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.


My book: Guerrilla Data Analysis 2nd Edition

[paigemartin]

You’ve broken down the dynamic approach so cleanly and quickly. I’ve struggled with other videos that start from square 1 before getting there the long way; I love that you tackle the issue head on and simplify.

[Officeinstructor]

Oz, You came back from the trip with VERY good stuff... Thanks for sharing and welcome back.

[johnhackwood]

Oh man that was such PQ wizardry, thank you!

[CeliaAlvesSolveExcel]

A sprinkle of M here and there will only do good. ;-)
Welcome back, Oz!

[aman_mashetty]

Firstly welcome back I hope u enjoyed your break.. and thanx for another awesome video

[wayneedmondson]

Awesome Oz! Thanks for sharing this technique.. super cool :)) Thumbs up!! PS - Great road trip shots.. glad to see you getting a break!

[angelaisland]

Everyone should take road trips! This country is beautiful

[ExactProBi]

good that you had a great trip! Excellent tutorial, I am sure it's the effect of the road trip!

[garethwoodall]

Glad your back Oz! Good to see that familiar face again pop up on the vids... booom!

[AlexRuu]

OMG, thank you Oz...was looking for a creative solution with this limitation.

[gregorytaylor]

Looks like a great road trip. Thanks for the great solution Uncle Oz!

[spilledgraphics]

Glad to have you back Oz! 🔥🔥
This is some impressive stuff ! Very valuable when the times comes to put into in practice 👌
The message at 5:23 is of GREAT, GREAT importance! #GoTeam 🏆🏆!

[eddybarrionuevo]

Great

"Driver" & Text.From([Column1])

[GrainneDuggan_Excel]

Nice one. Count max # columns - effective solution

[patrickschardt]

Everyone needs to recharge their batteries once in a while. Sounds like it was a good trip.

I am glad you’re back. Your videos are always entertaining and educational

Time to learn

[slayya]

That is SO neat! I have an immediate application for this, thanks a ton!

Also, you've made some really nice shots there on your road trip!

[utubeAgape]

Cool stuff - nice presentation! I need to use PQ like this!

[wmfexcel]

Welcome Back! Good to have a break! Hope you enjoyed it. :)

[Phoenixspin]

I like the idea of just getting in a car with no plan.

[wildecorrea]

07:21 *Data* => *Refresh* => 💥💥💥💥💥💥🔥🔥🔥🔥🔥 *(What!!!???
And the Oscar goes to => 🏆🥇 *DYNAMIC* List of numbers 1 through HCOUNT procedure. That was great, legit, solid!!! 😎🤜💥🤛👏👏👏👏

04:38 *= Curly brackets, one, dot, dot and HCOUNT* {1..HCOUNT} ▶▶▶ what an awesome Power Query M code!!! 👏👏👏👏🔥💪 .

03:05 Doing a *DYNAMIC* procedure of automatically updating a query is always a best practice!!!

00:06 What a nice road trip, my friend!!!! Taking a break; separating a time for resting, appreciating landscape, and getting to know new people and culture was a great decision, brother.
🏝🌞🏞🏜🌄

Nice sound choices, DJ!!! 🎵🎶🎼🎹🎧🎤 The sound came and went at the right time, without compromising the explanation. Congratulations on this video!

God is good, all the time. (Psalm 107.1 / Romans 1.20) 🙌🙏🔥🔥🔥🔥🔥🔥🔥✝🛐🕎🔯 God loves you, my brother! (John 3.16) God bless you!!!