Why to learn PowerQuery NOT PowerBI

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?)

PowerQuery in Excel

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!

PowerQuery not PowerBI

Make any report ‘normal’ to use in pivot tables, VLOOKUPS etc

So turn this report

PowerQuery not PowerBI

Into a database that you can pivot, lookup, summarise (and next month you just need to refresh with the new data!)

PowerQuery not PowerBI

Combine sheets in another spreadsheet into one database (and just refresh next month when a new sheet is added!)

PowerQuery not PowerBI

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!

PowerQuery not PowerBI

Access all the Excel files in a folder. Next month just refresh when you add a new spreadsheet

PowerQuery not PowerBI

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

PowerQuery not PowerBI

To this (and next month just refresh with the new data!)

PowerQuery not PowerBI

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.

Where has the Text Import Wizard in Excel gone

Reasons NOT to attend a MS Excel VBA training course