PowerBI is all the rage, but most people don’t seem to realise Microsoft’s Power tools are available within Excel already. But even more importantly, the most important one is there and ready to work. PowerQuery (now called Get & Transform) is ground breaking and something you must (and easily) can learn.
Why to learn PowerQuery NOT PowerBI
Below the reasons to focus on PowerQuery and (for now) forget about PowerBI
It is PowerBI WITHOUT needing new software or brand-new training. You already have it in your Excel, NO new software required. Depending on your version, you probably look at it every day (do you every use Sort or the Filter?)
It lets you continue to use all your Excel knowledge. Pivots, VLOOKUP’s. Everything you already know can be used in the normal way. It just makes all those tedious tasks so much easier and repeatable!
It solves any problem you thought you needed VBA for! If you think VBA may make your life easier, first learn PowerQuery
So for example, you can combine the sheets within a spreadsheet into one database (and just refresh next month when a new sheet is added!)
Below, you can have all the data in the sheets appear in a consolidated sheet (with no manual linking and looking up). Next month add a sheet and it will consolidate!
Make any report ‘normal’ to use in pivot tables, VLOOKUPS etc
So turn this report
Into a database that you can pivot, lookup, summarise (and next month you just need to refresh with the new data!)
Combine sheets in another spreadsheet into one database (and just refresh next month when a new sheet is added!)
Append files where column order DOES NOT MATCH. No more insert, move, copy, paste just to get them to line up. And next month you just refresh!
Access all the Excel files in a folder. Next month just refresh when you add a new spreadsheet
In case you didn’t notice in all the above, it allows you to repeat procedures once set up (build once- use multiple times- the way you know it should be)
Allows Excel to process more than a million rows
Only requires an Intermediate Excel skill level (push buttons- limited typing/ formula or code) and can be learnt in 1 day. Your Intermediate users will be able to outperform the advanced users!
Replaces need for VBA/ MS Access ‘workarounds’
Some other transformations your Intermediate users will now be able to do better than the advanced user
Convert from this
To this (and next month just refresh with the new data!)
Ever needed to create a unique lookup list based on the vendor accounts?
Start with this
A few clicks and if will look like this, and best of all next month you will just need to refresh! All your VLOOKUPS/ SUMIFS/ PIVOTS can link to this in the normal way!
Need to see only the duplicate bank account number when looking for fraud
Get a monthly report like this
Set up the report and then just refresh when you get new data for new month, new department, new company!
Your life may change just by learning about PowerQuery!
Have a look at the online PowerQuery course for a quick way to learn all this.