Power Query is an amazing tool for importing and cleaning up data from various sources. The problem was that it required you to have professional version of Office. Now that problem is solved…
Contents
Download Link
Download Power Query from here.
Remember to choose 32 bit or 64 bit version. Even if your Windows version is 64 bit, Office is usually installed as a 32 bit edition.
If you handle large amount of data, it is a good idea to install 64 bit version of Office.
Why? Because 32 bit version of Excel can only use 4 GB of RAM – even if your PC has more memory. 64 bit version can use all available RAM – which makes data crunching much faster.
What is required
Excel 2010 still has to be the Professional Edition to run Power Query.
For Excel 2013, standard version can also use Power Query – it is now called the Public version of Power Query.
Professional editions of Excel 2013 (Pro Plus, Office 365 version) get full power of Power Query – called Premium edition of Power Query.
All the details about what is different are given here. Important differences and new features are discussed in this article.
What is NOT there in the Public edition
Most sophisticated data sources like databases other than SQL Server, HDFS, SharePoint, SAP, etc. are not available. This is ok because for personal use or smaller business usage, these sources are probably not available anyway. Of course, Power BI integration is not possible.
Otherwise all features are available. Which means from the point of view of data crunching, all powerful facilities are available in the Public edition as well.
New features
Over time Power Query had accumulated many configuration options – which were scattered across the user interface. Now these are consolidated into a single – logically designed – dialog. Very useful.
Fixed Decimal Type
While converting data types, we had Decimal type option earlier. But there was no control over how many decimal places will the conversion result into. Now that control is added.
Online Search is renamed as Data Catalog Search
If you use the curated tabular data search sources in Power Query, this is an important change you should know about. The name of the button has changed. Its position has also changed. It used to be the first button from the left side. Now it has moved to the Power BI Group.
Remove blank rows operation added
This is a simple functionality – but quite useful. Easy to remove entire rows which are blank. Remember that there are other operations already existing which help you remove Top or Bottom n rows or remove alternate rows or remove rows based upon a filter condition. Also remember that row order can be reversed very quickly using Power Query.
New navigator dialog
This was added in the April update. This is a very useful feature for navigating complex queries or table structures.
Searching makes locating specific items easier. Multi-selection is also possible easily.
I feel the most useful improvement is the PREVIEW functionality. Earlier, the preview used to open as a pop-up (fly-out) on mouse hover. This was difficult to control. Now Preview is part of the same navigation dialog – making it stable and easier to manipulate.
Year to Date operation available for filtering
While editing queries, you can only get dates from Year to Date filtering mechanism. This is a good way of filtering data quickly – at import stage itself – if you know that the reports to be generated are only going to use this subset of data.
Ask your IT team to give you Power Query
Power Query is extremely useful for data gathering and clean-up. Unfortunately, it is an add-on. In most organizations, regular users are not allowed to install add-ins. Therefore, it is important to ask your IT team to install Power Query for you. Tell them the benefits of Power Query. I have given a list of articles below – which are more than enough justification for installing it.
Related Articles
Miracle: Instant “Report Clean Up” using Power Query
Combine Multiple Sheets into One Sheet
DO NOT copy paste data from browser. Use Power Query.
Bad Data to Good Data: Filling in gaps using Power Query
Crosstab data consolidation using Power Query
Analyzing badly captured survey data
CSV import problems solved with Power Query
Reversing row order in Excel data
What did I learn today: Power Query Preview Hyperlink
***
