#Excel #PowerQuery Challenge – Add value to CSV data in cells

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.

@ExcelIsFun channel
@MrExcel.com channel


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.