PowerQuery
All that data extraction, cleaning, formatting ant stitching it together before we even get to do anything fun with it? All that time wasted and opportunity to make mistakes?
Yeah, let’s get rid of that.
What is PowerQuery?
An Introduction to PowerQuery...
To explain in a bit more detail, here’s the first lesson from our PowerQuery course…
How does PowerQuery work?
Stage 1 - Connect to your dataset
Easy! Just click a couple of buttons! Connect to all sorts of datasets including Excel files, PDFs, Outlook files, Salesforce, Oracle, SAP, Facebook, web pages, Access, SSAS, Power BI datasets… – the list goes on!
Stage 2 - Transform your dataset
Easy! Just click the buttons on the PowerQuery editor’s ribbon and PowerQuery will record your transformation steps ready for the next time you’ve got that dataset ready for transformation! Here’s a few examples of what you can do…
– Split text using customisable delimiters more easily, more quickly than Excel’s text to columns
-Extract date data without the use of date functions like DATE, TEXT, MONTH and YEAR
-Bring columns from different datasets together at the click of a button (no more VLOOKUPS!)
– Automatically add datasets one underneath another (no more copying and pasting!)
= Convert tables into columns through unpivoting
Stage 3 - Load your dataset for interrogation
Easy! Just a click of a button!
PowerQuery in Power BI? – The data will go to the Data Model
PowerQuery in Excel? – Choose to send your data to a table, pivot table, pivot chart or the Data Model
Stage 4 - Add new datasets
EASIEST OF ALL!!! Save your data and click ‘Refresh’!
PowerQuery remembers all the steps you took to transform the dataset last time and then applies those same steps to the new datsaset!
Stage 5 ... Have a cuppa
Fancy a demonstration?
What are the Top 4 Reasons to learn PowerQuery?
1) It saves you and your team a MASSIVE amount of time by automating your data transformation (underline MASSIVE several times).
There are very few tasks in finance teams that we don’t have to do repetitively; posting journals, creating budgets, comparing actuals to budget, doing analysis, reconciliations etc etc.
In most organisations this generally means grabbing data from lots of different sources and cleaning and bringing it together until you get the data that you need.
That can take anytime between 10 minutes to several hours. Everyday or every week or every month for almost every task.
PowerQuery automates all that transforming so that all you need to do is click refresh.
2) It saves you time on your initial transformation
Some transformation tasks take an age to perform in standard Excel and there’s no getting around it. For example, transforming a budget table into a single column of values for analysis to actuals or using VLOOKUPS to bring multiple columns from one dataset to another.
The PowerQuery editor makes lengthy transformations faster.
It does this by having single click buttons on the ribbon for the most widely used commands making intial transformations much quicker.
3) It can allow you to work on datasets that are larger than a standard Excel workbook
Excel workbooks have 1,048,576 rows but frequently that’s not enough particularly when copying and pasting daily, weekly or monthly data in the same workbook.
PowerQuery is built to deal with MILLIONS of lines of data.
If you’ve got more data than you can paste into an Excel sheet you can save data in multiple workbooks or sheets, join them together and then load the resulting joined dataset to a pivot table or Powerpivot.
4) You learn PowerQuery, you’re learning Power BI
Power BI is awesome.
It’s a frankly brilliant reporting tool that SHOULD replace reporting by Excel, Powerpoint and Word.
The first of Power BI’s four stages is PowerQuery and it’s almost exactly the same as the version in Excel (with a couple more advanced features).
You learn how to shape data in Excel, you know how to shape data in Power BI.