As always, a picture tells thousand words. The above image explains clearly what we want to achieve, with Power Query in Excel of course.

This problem was bought to my attention when I watched the Dueling Excel Podcast #192 by MrExcel.com and ExcelIsFun channels. You may check it out here:

@MrExcel.com channel: https://www.youtube.com/watch?v=pcnWsCwEPbE
@ExcelIsFun channel: https://youtu.be/Z2Cy9IY7IBA

MrExcel solved the problem with VBA while ExcelIsFun solved it with LET functions. Super cool techniques. However it requires the latest version of Excel with Dynamic Arrays. What if you don’t have Dynamic Arrays? Power Query should be the best alternative. I was a bit surprised that they didn’t solve the problem with Power Query. So I couldn’t wait to try on my own and you know what… Yes, it can be solved with Power Query!

You may download the sample files to follow along:

Here’s my video:

I solved it with UI driven approach (coz I don’t know how to write M code from scratch 😅). I didn’t input a single word in the formula bar…… hmm-mm…. but I did input a few words when creating custom columns. Having said that, if your version of Excel comes with “Intellisense” in Power Query, you will find it quite easy to follow along (as long as you are aware of the functions of Power Query). 😁

Hope you like my video. If you do, please give a thumb up and Subscribe. 🙏

After I created my video, I couldn’t wait to share it (shamelessly 😅) with MrExcel and ExcelIsFun. Then I realized that they had plan to release their Power Query solutions the week after. Make sure you watch their solutions too.