In this tutorial, you’ll learn about Power Query and the M language inside the Power BI desktop.
Although there can be slight differences, you can apply the same general techniques across other Microsoft products like Excel, Analysis Services, and other Microsoft tools with an embedded Power Query Engine.
What Is Power Query?
Power Query is designed for data transformation and data preparation. It comes with a graphical interface for connecting to a wide range of data sources.
It also has an editor, more commonly known as, the Power Query Editor in the Power BI desktop which is used to transform raw data.
The process of transforming raw data is also referred to as ETL which stands for Extract, Transform and Load.
Power Query extracts data by connecting to a wide variety of data sources. It then reshapes, cleans, and transforms your data. And finally, it loads the data to the data model.
Data is never perfect. Thus, the goal of the Query Editor is to help the user apply most of the common transformations by interacting with a simple set of ribbons, menus, and other components.
This transformation capability is common across all sources. So regardless of the underlying source, the user experience inside the Query Editor remains the same.
Once you connect to a data source, you’ll have a preview of the data.
The preview is based on a subset that allows you to design your query. You’ll only see the full data when you click Close and apply. This means that you can work with very large data sets.
The Power Query engine is available in many products and services. The destination where the data will be stored depends on the query used.
What Is M Language?
M is a functional, case-sensitive mashup language designed for data transformation.
When you perform a transformation, the code for that step is automatically generated for you. So you don’t need to write an M code yourself.
However, there are cases where you need to learn how to write M codes. These include creating custom functions, performing more complex transformations, optimization, and building custom connectors.
And with the introduction of IntelliSense in September 2018, case sensitivity now has a lesser impact on M codes.
IntelliSense lets you call members of the intrinsic #shared, such as all native M functions, custom functions, variables or steps names, and parameters.
M is a functional language. When you understand its flow, it becomes easier to read and write M codes.
As a general best practice, make sure to have the formula bar visible in the user interface at all times. In this way, you can see the M code generated and follow through with its flow and syntax.
A variety of online resources are available where you can learn more about M.
One resource is the ForMula Language Specification which describes the structure of the language itself 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.
But overall, the most important thing when learning something new is practice, practice, and practice.
***** Related Links *****
Power Query M Language Tutorial And Mastery
Query Editor In Power BI | An Overview
Power Query Language Flow Best Practices
Conclusion
The Power Query Editor is a powerful tool when performing data transformations. Once you understand its basic concepts, it’ll be easier for you to convert your raw data into more meaningful information.
And though M language is often automatically generated for you, it’s always better to learn. This will be a great advantage when dealing with more complex scenarios in the future.
Melissa
