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.
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.
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:
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.
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.
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.
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.
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.