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) took 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 excercise.
- 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 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 give 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 easily. 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 it’s 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!!.
What is this course?
These days almost any job requires data analysis & presentation of results. While anyone can put a list of values in Excel & sum them up, not everyone can do advanced analysis, create charts, make them interactive, summarize data intelligently, present output in an intuitive dashboard or slice & dice data using Pivot tables & Power Pivot. Having these vital skills can make you invaluable to your organization & expose you to new opportunities. Not to mention the amount of time, money & effort you can save by efficiently using Excel.
That is why we have created 2 powerful courses – Excel School & Power Pivot classes. Think of these as steps in a ladder.
Excel School program teaches you how to analyze data, create pivot tables, make charts, design interactive dashboards using Excel.
Power Pivot class shows you how to use Power Pivot (an Excel add-in) to connect to your enterprise databases, analyze data, create advanced reports & prepare dashboards right from familiar interface of Excel.