In this tutorial, you’ll learn about Power Query and M language and how to master them. This will also go over their uses inside the Power BI desktop. You’ll learn and understand their importance in developing and obtaining data for your Power BI reports.
The Power Query Editor
Power Query is designed for data transformation and data preparation. It comes with a graphical interface that is connected to a wide range of data sources. It also has an editor inside the Power BI Desktop called Power Query Editor that transforms raw data.
Raw data is transformed through the process called ETL which stands for Extract, Transform, and Load. The process extracts data by connecting to the data sources, then transforms them through reshaping and cleaning. The last process loads the data to the data model.
The goal of the Query Editor is to help the user apply common transformations by interacting with the ribbons, menus, and other components. These transformation capabilities are common across all sources. So, the user experience inside the Query Editor remains the same no matter what the source is.
Once you connect to a data source, Power Query will show you a preview of the data. The preview is based on a subset that allows you to work with large datasets and design your query. The full data will only be provided once you click Close and Apply. The destination where the data is stored depends on what Power Query is used.
You can apply the same general techniques in Excel, Data Flows, Analysis Services, and other products where Microsoft has embedded the Power Query Engine. The Power Query Engine is available in many products and services.
The M Language
M is a functional, case-sensitive, mash up language that is designed for data transformation. When you perform a transformation through the UI, the code for that step is automatically generated. So, you don’t need to write any M code.
However, there are some things that you can only do by writing M such as creating custom functions, performing more complex transformations, optimizations, and even building custom connectors.
Now, with the introduction of IntelliSense, case sensitivity begins to have less impact. IntelliSense lets you call members of the intrinsic #shared, such as the following:
- All native M functions
- Custom functions
- Variables or step names
- Parameters in the Power Query instance
One of the most productive ways when using the user interface is to build and approach the query that you want. Then, go to the formula bar or Advanced Editor to modify the code.
To learn and master M, you need to understand the flow of the language. It will help you read and write codes easier, and improve your data development skills significantly.
Make sure that the formula bar is visible in the user interface at all times. This allows you to see the M code that’s generated and exposes you to its flow and syntax.
There are online resources available like the Formula Language Specification which describes the structure of the language, and the M function reference that lists all the functions and their documentation. Power Query also contains a standard library accessible through the #shared keyword.
***** Related Links *****
Power Query/M Masterclass #1 – New Course on Enterprise DNA
M Code In Power Query Custom Columns | Power BI
Power Query Function In Power BI: Group By Function
Conclusion
Learning how Power Query and M language operate is one of the best ways to master data development. If you know your way around all the available features inside the Power Query Editor, building your data report will be easier.
Melissa
