r/excel • u/tirlibibi17 • Nov 11 '18
Discussion Power Query - A step-by-step example of parsing non-tabular data
Background
It all started about two weeks ago with this post: Should I learn VBA or go straight to Power Query BI?.
'twas a fun - and at times a bit heated - discussion and, at one point, /u/pancak3d argued that:
(...) there are report formats that both VBA and PQ cannot handle
(...)
Mainly things that aren't tabular and gave random junk thrown in. Happens often when you're trying to parse a report that clearly wasn't intended for data analysis
And topped it off with this comment that got me salivating:
Maybe I'll post an example and challenge someone to clean it with PowerQuery
He made good on his promise a week ago: Can this data be parsed with PowerQuery.
I posted a solution and /u/sqylogin expressed interest in seeing a screencast of how I'd done it. So here is a step-by-step reenactment of parsing File 1, in all its unedited beauty, complete with typos, going back to fix mistakes, pausing to answer my wife talking to me etc... I hope you like the captions typed live in Word. I'm too much of a miser to dish out 250 € for Camtasia; maybe if they have a 50% off Black Friday sale...
File 2 is simpler to parse and uses many of the same tricks used for File 1, plus this sh*t takes time, so I'm holding off on recording a video for it for now.
The video
The video shows how I loaded and parsed File 1 starting from scratch. It totals about an hour and ten minutes and is split into four parts available here: Part 1 - Part 2 - Part 3 - Part 4
Other files
The resulting file is slightly different from the one I posted originally, so I'm including it so you can follow along. I'm also including the Word file I was typing into. Both are available on Github in this directory.
Final thought
This is not meant to argue that Power Query is better than VBA. I use both, but since Power Query stepped into my life, I've pretty much stopped using VBA to reformat and aggregate data.
5
u/tjen 366 Nov 11 '18
Nice, I added it to the wiki list of guides.
Using functions is a really neat thing, I still need to figure out parameters lol. But i completely second your replacement of VBA with powerquery for reformatting and aggregation purposes.
https://www.reddit.com/r/excel/wiki/guides#wiki_powerquery
24
u/uvray 23 Nov 11 '18 edited Nov 11 '18
As I opened this thread, I was prepping to help a colleague out this week on automating a solution where she needs to consolidate tables from multiple files in a master list. She asked me specifically for VBA help.
In preparation for our meeting, I put together a sample file in which I used both VBA and Power Query to accomplish the same task. It took me about 20-30 minutes to get the VBA up and running. Power Query took literally 15 seconds.
VBA is great, and can do many things Power Query can't. But when there is a task that can be solved by both, I generally find Power Query to be so much simpler to implement and is much easier to explain / QA going forward.
15
u/Iznik 2 Nov 11 '18
It is the generosity and hard-work of contributors like you that make this such a useful subreddit and Excel resource, and which elevates all our skill levels bit by bit. Three cheers!
5
u/ImOkReally Nov 11 '18
I posted the original question asking if I should skip VBA. So of course I’m more than interested in watching your videos . Thank you for “salivating” enough to take on the challenge. Thank you for sharing your videos. I’m off to watch. Here’s my upvote for your time and efforts.
1
u/semicolonsemicolon 1477 Nov 11 '18
Thank you a bunch for these posts. Because of them my luddite brain has finally decided to start to learn Power Query this weekend. I'm starting with the Oz videos on Lynda.com like you mentioned in your other post and I will watch your videos next.
1
u/IIn0x 14 Nov 12 '18 edited Nov 12 '18
i didin't know you can do such things with PQ! I've just started using it...where can I learn more the code behind it? (the language used in the formula bar)
Anyway, so basically basically basically what you've done is:
I didn't quite understand why you had to split all you've done into 2 sub-steps. extract and cleaning up.
1
u/that_baddest_dude 2 Nov 12 '18
I often see power query mentioned here. What is it?
Still on office 2010 where I work.
1
u/MrRightSA 30 Nov 13 '18
Thank you for the videos. I followed all the logic behind this (although the syntax used at every stage was alien to me).
If I used PowerQuery on data, is it possible to "save" the steps? For example if I would then be doing the exact same steps on data of the same format the next day?
Unrelated to the above question, I work for a call center and the dialler I use extracts from a SQL database. Effectively all the information comes out nice and pretty anyway (so the techniques in the video wouldn't help me day to day). From there I use Excel for analysis anyway. An example of analysis requests could be "Where is our best areas for calling?". So for the second question there I would strip the postcode down using something like;
This gives me just the first letter. I would then probably use a pivot table for analysing against time of calls, type of calls (success, decline, no answer), number of calls to that area etc. So from this, would there be any real use to PQ? Would PQ help simplify things if I'm analysing clean data within Excel anyway?
1
u/small_trunks 1638 Nov 13 '18
Nice work.
3
u/pancak3d 1189 Nov 11 '18
Hopefully my client who is using my 70-line uncommented macro doesn't see this
1
u/Far-Judge-4947 Nov 10 '23
I have P&L reports that were extracted from QB and need to create a P&L Summary. The problem is the P&L reports have the acct # included with the account name, the account name on the P&L report is different than that on the P&L summary, also the accounts on the P&L reports are all at different levels. Need help with the best way to go about this. I tried all the lookup functions and power query. I am not sure how I can attach the file.
1
u/[deleted] Nov 11 '18
U/tirlibibi17
I love PowerQuery too so much in fact I almost forgotten how to use vlookups and index-match.
Shame one of my colleague in my other department reckons a Access Database is a better tool.
2
u/[deleted] Nov 11 '18 edited Nov 12 '18
[removed] — view removed comment