Use Microsoft Excel Power Query To Transform Your Data

With Microsoft Excel Power Query you can share and manage queries as well as search data within your organization. Users can find and use these shared queries (if it is shared with them) to use the underlying data in the queries for their data analysis and reporting.

Power Query Transforms Your Data

You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
Press buttons in the Power Query window to transform your data.
Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.

Power Query can be used like automated templates because once you have your query setup, the process can be repeated with the click of a button every time your data changes.

If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding.

Common Data Tasks Made Easy

Excel Power Query Data transformations could include tasks like:

Remove columns, rows, blanks
Convert data types – text, numbers, dates
Split or merge columns
Sort & filter columns
Add calculated columns
Aggregate or summarize data
Find & replace text
Un-pivot data to use for pivot tables

Excel Power Query, part of the Microsoft Power BI suite, is a tool that automates the process of getting data into Excel and will save you hours of dull, repetitive, and error-prone work. Power Query makes it easy to extract data from many different data sources, filter that data, aggregate it, clean it and perform calculations on it, finally loading that data into either your worksheet or directly into the Excel Data Model used by Power Pivot.

Big Data is all the rage, and Microsoft is reacting with powerful solutions which are focused on making sense out of data, to render it actionable and useful. The improvements in the recent updates to Power Query follow a trend of creating functionality to support unstructured data and Big Data technologies. As a familiar front-end, Excel offers some powerful capabilities for visualizing and charting information. With the current performance improvements, loading massive data sets into Excel is now much more manageable.

Read more below, or book on one of our new Excel Power Query training courses in Glasgow, Edniburgh or onsite at your offices.

Overview of the Power Query Add-in Menus

Once installed, the Power Query tab will be visible in the Excel Ribbon.

You use the buttons in the Power Query tab to get your source data. Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, websites, and more.

Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data. The buttons in the Power Query Editor Window allow you to transform your data.

Think about some of those tasks you do repeatedly as you browse the buttons in the image above. Each time you press a button your actions (steps) are recorded, and you can quickly re-apply the steps when you receive new data.

After completing your steps, you can output the data to a Table in Excel workbooks by clicking the Close & Load button.

You can also modify existing queries and refresh your output tables with the changes or updated data.

Here are a few examples of what Power Query can do with your data.

Un-pivot Data for Pivot Tables

A favourite feature of Power Query is it’s ability to Un-pivot data. This is a technique used to get your data ready for the source of a pivot table. This is also referred to as normalizing your data to get it in a tabular format. Power Query can do this with the click of a few buttons, and prepare your data for use in a pivot table.

Append (Combine) Tables with Power Query

The Append feature of Power Query allows you to combine multiple tables (stack them vertically) to create one large table. It can do this with multiple tables in one file, or it can pull in data from a bunch of different files/sources. Let’s say you have a folder that contains CSV or Excel files with report data for each month. Throw all those files in the Power Query machine, and it will spit out one nice table that you can then use to create pivot tables and charts.

Append Combine Multiple Excel Files to One Table with Power Query

If the data in those reports also needs to be transformed (remove rows, split columns, un-pivot, etc.), then Power Query can handle that in the same process.

Once it is setup, all you have to do is hit the refresh button every month when a new file is added to the folder and the rows will be added to your output table.

Merge Tables – A VLOOKUP Alternative

Power Query has the ability to merge or join tables. This can be used as an alternative to VLOOKUP or INDEX/MATCH formulae. If you have this data table of sales records, and you are using a VLOOKUP to bring in information about the product based on the name of the product sold, your product group information is located in another table on a different sheet or even workbook. Using VLOOKUP formulae is great, but it can often mean adding thousands of formulas to your workbook, which increases the file size and calculation time.

Power Query makes it very fast and easy to merge two tables together with just a few clicks. It basically uses SQL joins, so you can even do more advanced merges like inner, outer, left, right, full, and anti joins.

Create Custom Functions

Power Query was designed so that you do NOT have to know how to code to use it. It is very easy to use because you can just click buttons and apply filters like you normally would in Excel, however Power Query can be also programmed to create custom functions. This gives you seemingly unlimited potential to transform your data in just about any way possible. It is based on the M language, and most of the functions are very similar to writing a formula in Excel. This also makes it more user friendly and easier to learn the code.

Power Query Advanced Editor M Code Language

This new language and set of functions means there is a lot to learn, but I consider that the fun and challenging part. Plus, employers of the future will definitely be looking for employees with Power Query skills.

Power Query Records Your Steps & Automates Processes

Power Query not only makes all these tasks easier, but it also records your steps so you don’t have to do them over-and-over again. It will save you a lot of time if you are preparing the same data every day, week, or month.

Power Query Records Applied Steps and Automates Processes

It also does a pretty good job of handling errors. If the structure of your source data changes, Power Query will tell you what step an error was found at and allow you to fix it. This makes maintenance easy and you don’t have to completely redo your process when something changes.

You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports. This is a critical step in the process of summarizing and analysing data.

The Power Query Machine & Power BI

Power Query is just one piece of the suite of Power BI (Business Intelligence) products from Microsoft.

Excel Power BI Solution Overview

You can think of Excel as the factory building that houses all these tools. The exciting part is that there is a very bright future for Excel! All these advances in technology will help us make sense of our data in new ways, save us time, and impress our colleagues.

With Power Query, you can

Find and connectdata across a wide variety of sources.
Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modeling by tools such as Power Pivot and Power View.
Create custom views over data.
Use the JSON parser to create data visualizations over Big Data and Azure HDInsight.
Perform data cleansing operations.
Import data from multiple log files.
Perform Online Search for data from a large collection of public data sources including Wikipedia tables, a subset of Microsoft Azure Marketplace, and a subset of Data.gov.
Create a query from your Facebook likes that render an Excel chart.
Pull data into Power Pivot from new data sources, such as XML, Facebook, and File Folders as refreshable connections.

Power Query Data Sources

Web page
Excel or CSV file
XML file
Text file
Folder
SQL Server database
Microsoft Azure SQL Database
Access database
Oracle database
IBM DB2 database
MySQL database
PostgreSQL Database
Sybase Database
Teradata Database
SharePoint List
OData feed
Microsoft Azure Marketplace
Hadoop File (HDFS)
Microsoft Azure HDInsight
Microsoft Azure Table Storage
Active Directory
Microsoft Exchange
Facebook