This article is the first in a series of blogs intended for newcomers to Power Query. It will give you an overview of what Power Query is, what it can do and how it fits into the overall Power BI framework.
Subsequent articles in this series will dive into more detail, revealing how to achieve required outcomes by taking advantage of the huge array of tools available within Power Query.
For clarity, it should be noted that Power Query is the name used by Microsoft when the tool was first introduced in Excel 2010 and later 2013. The same tools were then renamed Get & Transform in subsequent releases of Excel (eg . 2016) and are called Get Data in Power BI Desktop.
However, regardless of what it is called, it is the same thing. We will tend to use the term Power Query, as this is what the tool is universally known as, but regardless of whether you are using Excel or Power BI Desktop, the information provided here should apply equally to both tools. If there is a difference that we know of, we will highlight it in the article.
OK, so let’s get started. What is Power Query?
Power Query is an ETL (Extract, Transform & Load) tool that allows anybody to Extract data from a wide array of data sources, Transform, reshape, manipulate and cleanse the extracted data to meet requirements and then Load the resulting data for further processing in a Power Pivot data model in Excel or Power BI Desktop. It can even load the transformed data into Excel worksheets and whilst it could be argued that this is not the best use of data processed through Power Query, it is a legitimate use of the tool and one that should not be overlooked by seasoned Excel users.
Extract
Regardless of where the data that you need lives, it is highly likely that Power Query can extract it for you. Power Query has built-in functionality to seamlessly connect to and extract data from a huge (and growing) list of data sources, from
- plain text files
- CSV’s
- Excel
database files, such as
- Access
- SQL Server
- Oracle
- DB2
- Azure
- MySQL
- Teradata
- SAP Hana
- SAP BW
as well as being able to bring in data from
- SharePoint
- All files held in a folder
This is by no means a complete list of data types that Power Query is able to connect to – Microsoft are adding data connectors to Power Query on a very regular basis (monthly in most cases) and they already claim to be able to extract data from more sources than any other ETL tool on the market.
Transform
This is the fun bit! This is where the magic happens and data can be transformed from something resembling a tin of alphabeti spaghetti to a format needed to enable advanced data modeling and analysis.
For most transformation needs, Power Query offers a very intuitive UI (user interface) that gives the user all they need at the push of a button. Only in extreme circumstances would you need to dive into the Advanced Editor and write M code from scratch (M is the official name of the language used by Power Query).
The Power Query interface offers a vast array of transformation options, as shown in the images below. These screenshots are taken from Excel 2016, but the same options exist in Power BI Desktop:
As you can see, the UI offers a very comprehensive set of functions without needing to resort to writing code. Without a doubt, the best way of learning how to use these tools is by diving in and trying them out. They are extremely easy to use and are also very intuitive. There is also a mountain of help available, not only from Microsoft, but from an army of Power BI developers keen to share their knowledge and expertise. Just go to your favourite search engine and type in your requirements and you are guaranteed to find what you need.
Load
Once your data has been cleansed and reshaped (transformed), it is time to load it into Power Pivot. Using Power Query, this is a very simple task. Within the Power Query editor, simply select Close & Apply from the Home menu in Power Bi Desktop or Close & Load from the Home menu in Excel and the table being edited will be loaded into your data model. There are some options that need to be considered (particularly in Excel), but these will be covered in a later article.
Summary
As you can see, getting started with Power Query is quick and simple. It is fast, intuitive and very powerful. An important point to remember is that the process of extracting, transforming and loading a dataset only needs to be done once. Power Query remembers every step and once built, it is simply a matter of refreshing the data source each time there are updates to be loaded. Power Query will then apply all your transformations to the updated dataset automatically.
Power Query is a seriously good tool. You will be amazed at what you can do with it and the best part is that it just keeps getting better, so dive in and start playing.