What is PowerQuery
PowerQuery is a lightweight ETL tool (ETL – Extract, Transform, Load) capable of managing difficult data manipulation and transformation tasks. With its implementation into Microsoft Dataflows, it can also be considered as an enterprise-level ETL tool.
Power Query was initially created for Excel 2010, but it is currently also natively integrated into several other Microsoft products, including Microsoft Power BI, Microsoft SQL Server Data Tools for Visual Studio, and Common Data Service.
Power Query has 3 main functions:
- connect to the data source and extract data from it
- transform data inside of the Power Query user interface
- load the data inside of the data model for further analysis.
Power Query is the place you will spend time preparing data to enter your data model in proper form, especially if your data is coming from unstructured data sources. If your data is coming from a structured data source, Power Query will be used only to connect to and load data into a data model. There is no so-called write-back option that would enable users to write data back to the data source from within the user interface. After you connect to the data source, transform and load the data inside the data model with Power Query, you can create relationships inside the model, add measures and calculated columns with DAX, and finally visualize data.
In the picture below, you can see the position and functions of Power Query inside the simplified version of Microsoft Power BI environment:
The Power Query interface is very user-friendly and enables you to do all these steps (connect, transform, combine, and load data) without much coding knowledge. Behind the scenes, each step you make in Power Query is translated into M language code, which Power Query is writing for you.
If you are tired of dealing with repetitive tasks and unstructured data, then Power Query is a great tool for you. By mastering this course, you will save yourself a lot of time and energy in future work.
So, let’s get started!