By Mannix CarneyWhat is Power Query?
Power Query can be seen as a free ranging data wrangling tool. As a cowboy uses a lasso to round up cattle, you can use Power Query take the unruly data you have and convert it into a format that can be used for report building and data visualization.
Power Query is the data extraction and transforming feature inside of Excel. It is accessible through the Data tab at the top of the software ribbon.
Power Query set a link between a data sources and the current excel work book. The data source can be a wide range of data types. The data is then shaped and transformed using the Power Query interface.
The way Power Query works is that the transformation steps that are applied to the data are listed on the right-hand column as Applied Steps. You can add or delete steps as you manipulate the data towards the format that you will need. Do not be fooled by the simple Power Query interface. Under the graphical interface there is a scripting language call M that can be used to create very complex data shaping solutions.
Once you are happy with the data format, you must load the data to another location. You have a few options. You could just create a connection, you can load the data to a tab in the excel work book or you can load the data to the Data Model (Power Pivot).
Power query can be used as a standalone tool or it can be used in combination with Power Pivot.
What can Power Query Do for you?
I look at Power Query as a fully featured data transformation tool with an easy on ramp. You can start to clean and reformat your data with in minutes of using the tool due to its easy to use interface.
For example – you have a detailed table of data that you receive periodically from a supplier. Every time you get the table you have to change the coding on some of the items so that it matches with the rest of your system. This is painful and error prone task that needs to be done every two weeks.
With Power Query you can create a simple re-mapping solution that automatically updates that code in about 15 minutes. Now every time you get your data table you update the source, press refresh and use your updated data.
If you want more powerful custom solutions, you can write scripts and functions using the M language to create any data transformation solution that you can think of.
If you compare Power Query to VBA, Power Query has a far shorter learning curve and it is far easier to train other people on how to use simple Power Query solutions. The user interface is a lot more understandable.
Learn more about Power Query
The base resources that I have found on Power Query is the book and online course by Ken Puls and Miguel Escobar