Power What???

Do you spend a lot of time building dashboards and reports in Excel?. Do they take a lot of time and effort to repeat the same task over and over again? If your answer is yes then it sounds like you could really benefit from using Power Query.

Let’s walk you through this Add-In for Excel explaining what it is and what it can do for you to free you to concentrate on analyzing and gaining insights from your data.

Power Query is part of Microsoft’s Business Intelligence (BI) tool kit or their Power BI along with Power Pivot, Power View and also Power Map. This powerful Add In can be used to extract, clean, transform and aggregate data from many data sources. It can load the result of the query directly into the Excel worksheet or load it into the Excel Data Model.

Installing Power Query

Power Query needs to be downloaded and installed after Excel has been installed on your machine. If you do not see the Power Query Tab then you may have to manually enable the Add-In. First, you need to download it. Installation is the same for Exel 2013 and Excel 210 and you need to log out of Excel before completing this exercise.

• Click this link to go to the Microsoft Download Page
• Hit the Download button

• Hit the checkbox for the version you require- either 32 or 64bit
• Hit Next

There are a few restrictions on installing it on your PC. You will need Windows Vista or later if you want to use it on a PC and Windows 2008 or later if installing on a server. Be sure to choose the correct version, 32bit or 64bit depending on which version of Excel you are running and finally, you will need.NET 3.5 Service Pack 1 and Internet Explorer 9 or later.

You will automatically be notified by the Power Query Tab lighting up if an update or a new version is available for you to download.

Power Query will be available for you if you have one of the following products

• Excel 2013 standalone version
• Office 2013 Professional Plus Licence for your desktop
• An office 365 subscription that gives you access to the Office 2013 Professional Plus desktop tools.

Power Query allows you to connect to a variety of data sources, extract the data really quick and easy. You then can define a series of repeatable steps to clean and filter the data all before it even gets into Excel.

These repeatable steps are the ‘query’ part of Power Query and are carried out in a specific order. These steps are defined in its own expression language known as ‘M’. This language is nothing like Excel formulas, functions or even VBA, but unless you are a programmer (and interested in the coding) you probably will find you do not have to go near the coding or write any code yourself to carry out any activities in Power Query.

Power Query will probably be the first component part of the BI suite you use as normally the first part of building reports and dashboards is collecting the data, cleansing and aggregating in readiness for analysis. It’s a super sweet tool in Excel. Go ahead and get it installed!!.

How To Quickly Move A Pivot Table Or Pivot Chart In Excel

Default Your Pivot Tables To SUM not COUNT

Add A Rank Number Field To Your Pivot Table