The Power Query M Switch simplifies the management of conditional expressions and lightens complex data transformation formulas. Find out all you need to know!
For Microsoft Excel and Power BI, the Power Query tool offers a wealth of possibilities. Its simple, intuitive query editor lets you collect data from multiple sources, transform it and load it into a table.
In addition to functions accessible with a single click from the graphical interface, the M programming language lets you write custom formulas for more complex or specific transformations.
However, M formulas can become too cumbersome and complicated when dealing with multiple cases or complex conditional expressions. To avoid this problem, the Power Query M Switch can be used.
Understanding conditional expressions in M
Conditional expressions in the M language are used to define conditions for transforming data. In particular, they are used to perform operations such as deleting rows, modifying values or merging columns.
Conditions are generally based on comparisons, value evaluations and logical tests. The basic structure of a conditional expression in M is as follows:
“if [condition] then [value_if_true] else [value_if_false]”.
For multiple tests, several conditional expressions can be nested. To avoid cumbersome syntax, it’s best to use M Switch.
What is the Power Query M Switch?
In programming languages, a Switch function evaluates an expression or variable and executes a block of code according to its value.
It is often used to simplify complex conditional expressions, and to avoid nested control structures such as if/else loops.
It allows you to specify a list of possible cases or values for a given expression, then provide an action or block of code to execute for each case.
Many programming languages have a Switch function, including JavaScript, Python, C# and Visual Basic.
This is also the case for the M formula language incorporated into Power Query for Microsoft Excel and Power BI.
What' is the Power Query M Switch used for?
The Power Query M Switch lets you select specific actions to be performed based on the values of a table column or other condition.
It is a conditional transformation tool used to perform operations on specific values. The user can specify values to search for and operations to perform for each value found.
This is particularly useful for data processing where multiple conditional cases need to be managed, as it allows them to be organized into a single, easy-to-read and easy-to-understand formula.
Similarly, M Switch replaces excessive nesting of functions, such as if/then clauses, which can make the formula difficult to read and maintain.
Last but not least, this tool is very handy for performing advanced data transformations on complex data.
In short, the Power Query M Switch reduces the complexity of formulas, makes them easier to maintain and modify, and reduces the risk of errors such as missing values or using the wrong logical operation.
How does the Power Query M Switch work?
The Power Query M Switch works by evaluating a series of conditional expressions until a condition is true. As soon as the condition is true, Switch returns the corresponding value or executes the action associated with the condition.
Its syntax is as follows
“M Switch(expression, {value1, result1}, {value2, result2}, …, {valueN, resultN}, [default])”.
The “expression” argument is the expression or variable to be evaluated, while the “value1”, “value2”, “valueN” arguments are the possible values of the expression to be evaluated.
The arguments “result1”, “result2”, “resultN” are the results to be returned if the evaluated expression matches the corresponding value.
Finally, “default” is an optional argument specifying the value to be returned if the evaluated expression does not match any of the values specified in the list.
Here’s an example of how to use Power Query’s M Switch to create a column calculated according to the value of another column:
“let
Source = Table.FromRecords({
[Fruit=”Apple”, Price=2.0],
[Fruit=”Banana”, Price=1.5],
[Fruit=”Orange”, Price=2.5]
}),
#”Add a calculated column” = Table.AddColumn(Source, “Category”, each
M Switch([Fruit],
{“Apple”, “Orange”}, “Common Fruit”,
{“Banana”}, “Exotic Fruit”,
[Fruit]
)
)
in
#”Add a calculated column” “
In this example, a calculated column called “Category” has been added using M Switch to assign a category to each fruit based on its value.
If the fruit is an apple or an orange, the value of the “Category” column will be “Common Fruit”. If it’s an apple or an orange, the value will be “Exotic Fruit”. Finally, if the fruit is neither an apple, nor an orange, nor a banana, the value of the column will be equal to that of the “Fruit” column.
Conclusion: Power Query M Switch, a solid alternative to if/then embedding
So you see: Power Query’s M Switch is invaluable for simplifying complex formulas with conditional expressions and nested if/then clauses.
This is one of the many possibilities offered by Power Query that many newcomers are unaware of. To fully master this tool and exploit its full potential, you can choose DataScientest.
In just 5 days, our Power BI training course teaches you how to collect, transform and analyze data, and produce interactive dashboards.
Over the course of the various modules, you’ll discover Power Query and the M language, DataViz functionalities and more advanced concepts such as DataFlows and incremental updating.
At the end of the program, DataScientest will help you pass the Microsoft PL-300 exam, leading to Power BI Data Analyst Associate certification.
All our programs can be completed entirely online, and are eligible for funding options. Discover DataScientest now!
