Getting Started with M Language in Power Query

Power Query, a powerful data transformation tool embedded in Microsoft Excel and other Microsoft products, utilizes a specialized language known as “M.” M is designed for manipulating and transforming data, offering users a robust set of functions and capabilities. If you’re looking to dive into the world of M language in Power Query, this guide will provide you with a solid foundation to get started.

What is M Language in Power Query in Excel?

M Language, also known as Power Query Formula Language, is a specialized language used in Power Query, a data transformation and shaping tool integrated into various Microsoft products, including Excel, Power BI, and others. M language is designed specifically for defining data transformation steps and manipulating data within the Power Query Editor.

Why Use M Language in Power Query?

Some reasons why M language in Power Query is used include:

• Data Transformation: M is a functional language designed for expressing data transformation tasks. It allows users to define a series of steps to clean, shape, and combine data from different sources.
• Formula-Based: M is a formula language, which means users can write formulas to perform specific operations on the data. This includes tasks such as filtering, sorting, grouping, and aggregating.
• Power Query Editor: M is used extensively in the Power Query Editor, a graphical user interface for building data transformation queries. Users can also view and modify the underlying M code to perform more advanced transformations.
• Advanced Data Cleansing: M allows users to perform advanced data cleansing tasks, such as handling missing values, removing duplicates, and converting data types.
• Custom Functions: Users can create custom functions using M, which can be reused across different queries. This helps in maintaining a modular and organized approach to data transformation.
• Query Folding: M supports query folding, which means that certain transformations can be pushed back to the data source if the source system supports it. This can improve performance by minimizing data transfer between the data source and Power Query.
• Extensibility: M provides extensibility by allowing users to write custom functions and expressions to meet specific data transformation requirements beyond the built-in capabilities.

Understanding the Basics of M Language

Power Query Formula Language, also known as M language, is a functional language designed specifically for data manipulation tasks. Its syntax is designed to be intuitive and expressive, making it accessible to both beginners and experienced users.

1. Power Query Editor

To begin using M language, open the Power Query Editor in Excel or other Microsoft applications that support Power Query. Load your data into the editor, and you’ll be ready to transform it using M language.

2. Formulas and Expressions

M language relies heavily on formulas and expressions. Use these to create step-by-step instructions for transforming your data.

Each step in the Power Query Editor generates an M expression in the formula bar, allowing for easy review and modification.

3. The Let Statement

The let statement is a fundamental construct in M language. It allows you to define variables and reuse them throughout your code.

Example:

let
  Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
  FilteredRows = Table.SelectRows(Source, each [Column1] > 10)
in
  FilteredRows

Generate an M Query Using the Power Query Advanced Editor

Our initial objective is to generate a query from a table or range. In this illustration, I’ve utilized a range comprising three columns named “Company Name,” “Product Name,” and “Price.”

Creating a Table Using Power Query

To create a query from this range, start by selecting the desired cells, and then navigate to the “Data” ribbon. Next, click on “From Table/Range” located in the “Get & Transform Data” section.

Following this step, a prompt for the “Create Table” option will appear. Ensure to check the “My Table Has a Header” option and proceed by clicking “OK.”

This action will direct you to the “Power Query Editor”. Within the “Power Query Editor”, you gain the capability to insert, delete, and modify your data through the query.

Within the “Power Query Editor,” there exists an additional tool known as the “Advanced Editor.” To access this feature, navigate to the “View” tab on the ribbon and select “Advanced Editor.”

Upon clicking “Advanced Editor,” the editor interface will unfold, presenting you with a view where M code is automatically generated for the selected table.

The code is given below:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text},
{"Product Name", type text}, {"Price", Int64.Type}})
in
#"Changed Type"

Code Explanation

This Power Query M code is crafted to transform and shape data from an Excel table named “Table2.”

• ‘let’ and ‘in’
  These keywords mark the beginning and end of a series of Power Query steps. Everything between let and in is the script, and the result of the script is what follows.
• Source = Excel.CurrentWorkbook(){[Name=”Table2″]}[Content],
  This line sets the variable “Source” to represent the content of the Excel table named “Table2” in the current workbook.
• #”Changed Type” = Table.TransformColumnTypes(Source,{{“Company Name”, type text}, {“Product Name”, type text}, {“Price”, Int64.Type}})
  #”Changed Type” utilizes the Table.TransformColumnTypes function to modify the data types of specific columns in the table referred to as “Source.” The three columns transforming are “Company Name,” “Product Name,” and “Price.” The transformations are outlined in a list, specifying that “Company Name” and “Product Name” should be treated as text, while “Price” should be converted to a 64-bit integer (Int64.Type).
• #”Changed Type”
  Specifies that the final output of the query is the result of the “Changed Type” step.

Removing a Column from a Table

Now, if we delete the “Price” column, what will happen? After removing the “Price” column, navigate back to the “Advanced Editor.” You’ll observe that the M code for this query has undergone modifications. The Changed code is provided below:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text},
{"Product Name", type text}, {"Price", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Price"})
in
#"Removed Columns"

In this modified code:

•  #”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“Price”})
  The operation utilizes the Table.RemoveColumns function to remove the “Price” column, and the resulting table, now without this column, is specified as the final output.
• The “in #”Removed Columns” part signifies that the final result of this Power Query is the table obtained after removing the specified column.

Afterward, you can save this query by navigating to the “Home” ribbon tab and selecting “Close and Load.”

Power Query M Formula Examples

Define a List Manually

A list in Power Query is a straightforward, single-column data structure. It differs from a singular columnar table; a singular columnar table is not classified as a list, and defining a list with a single column restricts the addition of other columns. However, you can freely add rows to a list.

To establish a list in Power Query within Excel, you can insert a function using M code within the Power Query function script. Remember that Power Query always encloses lists within curly brackets. Go to the “fx” to insert a function and click it, then write the below code to the formula bar: ={1,2,3,4,5}.

After that, you can create a list manually.

Extracting a List from Dataset

To accomplish this, return to the source data. To extract a list from a dataset with three columns defined as the source in the Power Query Editor, navigate to the ‘fx’ icon to add a step named “Custom 2”. Then, copy and paste the following code to the formula bar: =Source[Product Name]

The name inside the third bracket in ‘ =Source[ ] ‘ signifies the column name from which you intend to extract the list. Finally, the extracted list is here:

If you wish to create a list of column names, simply copy and paste the following code into the formula bar: “= Table.ColumnNames(Source)”.

This will ultimately extract all the column names from the table.

Extracting a Record from Dataset

A record in Power Query is essentially a data row, with each row in the table considered a record. It’s crucial to note that records encompass not only the data values but also the data header. If I aim to extract the record from row number “3,” it might seem like it should hold “Google’s” product. However, it’s essential to remember that in Power Query, row counting commences from 0. Therefore, row number 3 actually corresponds to “Huawei’s” product. To achieve this, simply copy and paste the following code into the formula bar: =Source{3}.

Finally, we have extracted the record, revealing it as “Huawei Mate 30” rather than the expected “Google Pixel 8.”

Counting the Number of Rows

If you want to count the number of rows, you can easily count it with the help of =Table.RowCount(Source) function. Here’s how:

Paste the code into the formula bar, and you’ll observe that it accurately counts the rows, revealing the presence of 6 rows in total.

Common M Language Functions

1. Table.TransformColumns

This function is handy for transforming multiple columns in a table simultaneously.

Example:

Table.TransformColumns(
YourTable,
{{"Column1", each _ * 2, type number}, {"Column2", Text.Upper, type text}}
)

2. List functions (List.Transform, List.Filter, etc.)

M language provides powerful functions for working with lists, allowing you to manipulate data at a granular level.

Example:

List.Transform({1, 2, 3}, each _ * 10)

3. Error Handling with “try…otherwise”

Use the “try…otherwise” construct to handle errors gracefully and ensure the reliability of your code.

Example:

try
YourCode
otherwise
"Error: Something went wrong"

Advanced Concepts Related to M Language

1. Custom Functions

As you become more comfortable with M language, consider creating custom functions to modularize your code and enhance reusability.

Example:

let
MultiplyByTwo = (value) => value * 2,
YourTable = YourTable |> Table.TransformColumns({"Column1"}, {{"Column1", each MultiplyByTwo(_), Int64.Type}})
in
YourTable

2. Understanding Query Folding

Query folding, a critical concept in Power Query, enables pushing certain operations back to the data source, thereby improving performance.

Be aware of query folding implications when working with M language.

Error Handling in M Language

In Power Query M language, error handling is essential for creating robust and reliable queries. Here are some common techniques for handling errors in M code:

1. Using the “try…otherwise” Construct

You can use the “try…otherwise” construct to catch and handle errors. This allows you to attempt a certain operation and specify what to do in case of an error.

try
// Your code that might raise an error
otherwise
// Code to handle the error

For example:

try
let
result = 10 / 0
in
result
otherwise
"Error: Division by zero"

2. Using Error functions

M language provides functions for working with errors, such as Error.Record, Error.Table, and Error.Type. These functions allow you to create custom error messages and handle errors more precisely.

try
// Your code that might raise an error
otherwise error "Custom error message"

3. Using Error.Record and Error.Table

These functions allow you to create custom error records or tables, which can be useful for providing detailed error information.

try
// Your code that might raise an error
otherwise Error.Record("CustomError", "Error details")

4. Using Error.Raise

You can use Error.Raise to raise a custom error. This function can be useful when you want to explicitly generate an error in a specific condition.

if someCondition then
Error.Raise("CustomError", "Error details")
else
// Your code

5. Using the “if…then…else” Statement

You can use conditional statements to check for specific conditions and handle errors accordingly.

if someCondition then
// Your code
else
// Code to handle the error

6. Using “try…each”

When working with lists, you can use “try…each” to apply an operation to each element of a list and handle errors individually.

List.Transform(
myList,
each try _ / 0 otherwise Error.Record("DivisionError", "Cannot divide by zero")
)

7. Using Custom Functions for Error Handling

You can create custom functions that encapsulate specific error-handling logic. This can help improve the readability of your code and make it easier to maintain.

let
// Your custom function with error handling
MyFunction = (parameter) =>
try
// Your code
otherwise
// Code to handle the error
in
MyFunction("someParameter")

Conclusion

Starting with M language in Power Query unlocks a world of possibilities for transforming and manipulating data. Understanding the basics, exploring common functions, and delving into advanced concepts will equip you to handle diverse data challenges efficiently. As you continue your journey, refer to Microsoft’s official documentation and community forums for ongoing support and learning opportunities. Embrace the power of M language, and transform your data with confidence!

Frequently Asked Questions

Where can I find the M code for a query?

In the Power Query Editor, you can view the M code for a query by clicking on the “Advanced Editor” in the “Home” tab. It opens a window displaying the M code for the selected query.

Can I write M code manually?

Yes, you can write M code manually in the Advanced Editor. It allows you to customize and extend the transformations applied to your data.

What are some basic M language functions for data transformation?

Common M language functions include Table.TransformColumns, Table.AddColumn, Table.Group, Table.Filter, List.Transform, etc. These functions help in transforming and reshaping data.

Is it possible to import M code from one query to another?

Yes, you can reuse M code from one query in another. In the Power Query Editor, select the query containing the desired code, right-click, and choose “Copy” or “Copy M.” Then, paste it into the Advanced Editor of the target query.

Can I use the M code in Power BI as well?

Yes, M language powers data transformation not only in Excel’s Power Query but also in Power BI. The syntax and functions are largely the same between the two environments.