Learning M the Mashup Language in Power Query [Power Query in Excel and Power BI Series - Ep. 4]

In this video from Pragmatic Works, Allison Gonzalez introduces viewers to M, the mashup language used in Power Query. This language powers all of the actions behind the user-friendly UI buttons that many Power Query users rely on. The episode explores how to understand M’s structure, its components, and how to view and edit M code through Power Query's Advanced Editor.

What is M Language?

The M language is the foundation of Power Query’s data transformation capabilities. It is responsible for performing all the data manipulation tasks that are typically handled through Power Query’s graphical interface. While the Power Query editor provides an intuitive interface for users, M code operates behind the scenes, executing every operation like changing data types, splitting columns, or removing unwanted data.

Understanding the Structure of M Code

Allison walks us through the essential parts of M code using an example. To gain a deeper understanding, she demonstrates how the code is structured in Power Query’s Advanced Editor. The key components of an M query include:

• Let Expression: This section holds all of the commands and instructions for the data transformations. It serves as the foundation of your M code.
• Named Expressions (Variables): These represent the steps that users see in the applied steps panel in Power Query. Each line in the let expression corresponds to a specific operation performed on the data.
• M Function: These are the actual commands that perform actions on the data, such as "Table.PromoteHeaders" or "Table.TransformColumnTypes."
• Previous Step References: M queries rely on a chain reaction approach. Each new step references the previous step, making the transformations sequential and dependent on one another.
• In Expression: The "In" expression is the final output of the M query, referencing the last applied step that completes the transformation process.

Using the Advanced Editor

In Power Query, users can see the M code in two places: the formula bar and the Advanced Editor. The formula bar shows the M code for individual steps, which is useful for small transformations. However, for larger projects, the Advanced Editor allows users to view and modify the entire code for all steps. In both Power Query in Excel and Power BI, the Advanced Editor works in a similar way, displaying all of the components in a straightforward text format.

Best Practices for Editing M Code

One of the key takeaways from the video is the importance of organizing M code for readability. Allison demonstrates how to use spaces and indentation to make M queries easier to understand. While spacing doesn’t impact the functionality of the code, it greatly improves clarity and helps prevent errors. The steps in the Advanced Editor can be spaced out by adjusting the position of the equal signs and parentheses to create a more structured appearance.

Viewing M Code in Power Query

Allison shows how to use the Advanced Editor in Power Query to modify M code in real-time. She demonstrates this using a sample data set and walks through the entire process of reviewing and editing the code. By toggling between the formula bar and Advanced Editor, users can better understand how each transformation step is linked and how the M code evolves throughout the process.

Practical Applications of M Code

Ultimately, the goal of understanding M is to harness its full potential for data transformation tasks. Whether in Power Query for Excel or Power BI, users can fine-tune their data transformation pipelines, improving both the efficiency and scalability of their queries. Allison emphasizes that mastering M allows users to create cleaner, more flexible queries that can easily be adjusted to meet future requirements.

Conclusion

Understanding M, the mashup language, is a critical skill for Power Query users. It allows for greater control over data transformations and opens up advanced capabilities beyond what is possible through the user interface alone. By using the Advanced Editor and applying best practices for organizing M code, users can significantly improve their data workflows and maximize the power of Power Query.

For more tutorials and training on Power Query and other Microsoft tools, be sure to check out Pragmatic Works’ full library of resources!

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on  Power Query and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.