In this blog, you will learn about M Language in PowerBI.
What is M Language in PowerBI?
The M language, also known as Power Query Formula Language, is a data mashup language used in Power Query. It is designed to create and manipulate data transformations and queries within the Power Query editor in Microsoft Power BI, Excel, and other Microsoft services.
History and Development
The M language was developed by Microsoft to facilitate data extraction, transformation, and loading (ETL) processes in Power BI and Excel. It was created to provide a powerful, yet flexible and easy-to-use tool for handling and preparing data.
Where M language is used
- Power Query: The M language is the backbone of the Power Query editor, enabling users to shape, transform, and combine data from various sources.
- Power BI: In Power BI, the M language is used to create and edit queries that turn raw data into meaningful insights.
- Excel: The M language is integrated into Excel via the Power Query add-in, allowing users to connect, transform, and combine data from a variety of sources.
Basic concepts of M language
Understanding queries in Power Query
A query in Power Query is a series of steps that define how data is transformed and shaped. Each step performs a specific operation, such as filtering rows, deleting columns, or merging tables. The query is written in the M language, which describes these steps and their sequence.
Data sources and data types in M language
- Data sources: The M language supports a wide range of data sources, including databases, web services, files, and more.
- Data types: The M language recognizes several data types:
- Text: Represents string data.
- Number: Includes various numeric formats, such as integer, decimal, etc.
- Date/Time: Includes date, time, date, and duration.
- Binary: Represents binary data, such as images or files.
Role of M language in data transformation
The M language plays a vital role in data transformation by providing functions and expressions that enable users to filter, sort, group, pivot, and aggregate data. This allows the automation of complex data manipulation tasks, ensuring that data is in the correct format for analysis.
M Language Syntax and Elements
Functions
Functions in the M language perform specific operations on data. They can be predefined or user-defined.
Examples of predefined function:
#“Text.Upper” = Text.Upper(“Hello”)
Examples of user-defined function:
let
AddNumbers = (a as number, b as number) as number =>
a + b,
Result = AddNumbers(5, 7)
in
Result
Operators
Operators are symbols that specify the type of calculation or operation to be performed. Common operators include:
Arithmetic: +, -, *, /
Comparison: =, <, >, <=, >=, <>
Logical: and, or, not
Identifiers and keywords
Identifiers: Names used to identify variables, functions, and other elements. They must start with a letter and may contain letters, numbers, and underscores.
Keywords: Reserved words with special meaning in the M language, such as let, in, if, then, else.
Variables
Variables in the M language are used to store values or the results of expressions. They are defined using the let keyword and can be referenced throughout the query. Example:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FilteredRows = Table.SelectRows(Source, each [Column1] > 100)
in
FilteredRowsMain features of the M language
Data types
- Text: “Hello, world!”
- Number: 123, 45.67
- Date/time: #date(2024, 7, 9), #datetime(2024, 7, 9, 13, 45, 0)
- Binary: Binary data, such as file contents
Records and tables
Record: Collection of fields containing name-value pairs. Similar to rows in a table.
Record = [name = "John", age = 30, city = "New York"]
Table: Collection of records. Represented in tabular form with rows and columns.
Table.FromRecords({
[name = "John", age = 30, city = "New York"],
[name = "Jane", age = 25, city = "Chicago"]
})
Lists and list functions
Lists: Ordered collection of values.
{1, 2, 3, 4, 5}
List functions: Functions for manipulating lists, such as List.Sum({1, 2, 3}) which returns 6.
Common M Language Functions in PowerBI
Text Functions:
Text.From(): Return Text from value
#“Text.From” = Text.From(123)Text.Upper(): Converts all characters in a text string to uppercase.
#“Text.Upper” = Text.Upper("hello")Text.Lower(): Converts all characters in a text string to lowercase.
#“Text.Lower” = Text.Lower("HELLO")Text.Length(): Returns the number of characters in a text string.
#“Text.Length” = Text.Length("Power BI")Text.Contains(): Checks if a text string contains a specific substring.
#“ Text.Contains” = Text.Contains("Power BI", "BI")Numeric Functions
Number.From(): Converts a value to a number.
#“Number.From” = Number.From("123")Number.Round(): Rounds a number to a specified number of decimal places.
#“Number.Round” = Number.Round(123.456, 2)Number.Abs(): Returns the absolute value of a number.
#“Number.Abs” = Number.Abs(-123)Number.Power(): Returns the result of a number raised to a power.
#“Number.Power” = Number.Power(2, 3)Number.Mod(): Returns the remainder of a division operation.
#“Number.Mod” = Number.Mod(10, 3)Date/Time Functions
DateTime.From(): Converts a value to a DateTime value.
#“DateTime.From” = DateTime.From("2024-07-11")Date.AddDays(): Adds a specified number of days to a Date value.
#“Date.AddDays” = Date.AddDays(#“DateTime.From”, 5)Date.DayOfWeek(): Returns the day of the week for a DateTime value.
#“Date.DayOfWeek” = Date.DayOfWeek(#“DateTime.From”)DateTime.ToText(): Converts a DateTime value to text.
#“DateTime.ToText” = DateTime.ToText(#“DateTime.From”)DateTime.LocalNow(): Returns the current date and time.
#“DateTime.LocalNow” = DateTime.LocalNow()List Functions
List.Count(): Returns the number of items in a list.
#“List.Count” = List.Count({1, 2, 3})List.First(): Returns the first item in a list.
#“List.First” = List.First({1, 2, 3})List.Last(): Returns the last item in a list.
#“List.Last” = List.Last({1, 2, 3})List.Contains(): Checks if a list contains a specific value.
#“List.Contains” = List.Contains({1, 2, 3}, 2)List.Sort(): Sorts the items in a list.
#“List.Sort” = List.Sort({3, 1, 2})Table Functions
Table.AddColumn(): Adds a new column to a table.
#“Table.AddColumn” = Table.AddColumn(#table({"Column1"}, {{1}, {2}, {3}}), "NewColumn", each [Column1] * 2)Table.SelectRows(): Selects rows from a table based on a condition.
#“Table.SelectRows” = Table.SelectRows(#table({"Column1"}, {{1}, {2}, {3}}), each [Column1] > 1)Table.Sort(): Sorts a table by specified columns.
#“Table.Sort” = Table.Sort(#table({"Column1"}, {{3}, {1}, {2}}), {{"Column1", Order.Ascending}})Table.RenameColumns(): Renames columns in a table.
#“Table.RenameColumns” = Table.RenameColumns(#table({"OldName"}, {{1}}), {{"OldName", "NewName"}})Table.RemoveColumns(): Removes specified columns from a table.
#“Table.RemoveColumns” = Table.RemoveColumns(#table({"Column1", "Column2"}, {{1, 2}}), {"Column2"})Error Handling Functions
try…otherwise: Handles errors by trying an expression and providing an alternative value if an error occurs.
try Number.From("abc") otherwise -1Error.Record(): Creates an error record.
Error.Record("Error", "This is an error message", "Detail")try…catch: Attempts an operation and catches any errors that occur.
try Text.From(123) catch (e) => "An error occurred"Working With M Language
Step 1. Open Power BI Desktop.
Step 2. Click on Get Data from the Home tab.
Step 3. Select your data source and load it.
Step 4. Click on Transform Data in the Home tab.
Step 5. In Power Query Editor, click on the Home tab.
Writing M Code
Step 6. Click on Advanced Editor in the Home tab.
let
Source = Excel.Workbook(File.Contents("C:\file.xlsx"), null, true),
Sheet1 = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1, {{"Column1", type text}})
in
#"Changed Type"Using the Advanced Editor in Power Query
Opening the Advanced Editor
Step 1. In Power Query Editor, click on the Advanced Editor button in the Home tab.
Step 2. Make necessary modifications to the M code.
Step 3. Click “Done” to apply the changes.
Applying M Language Transformations in Power BI
Step 1. Open Power BI Desktop.
Step 2. Click on Get Data to load your data source.
Step 3. Click on Transform Data to open the Power Query Editor.
Applying Transformations
Use the Power Query Editor’s interface to apply transformations (filtering, sorting, adding columns, etc.).
Example of Sorting a List:
let
SourceList = {3, 1, 2},
SortedList = List.Sort(SourceList, Order.Ascending)
in
SortedList
Advanced Data Conversion Capabilities
- Merging: Combining multiple tables based on common columns.
- Concatenating: Stacking tables on top of each other.
- Pivoting/Unpivoting: Converting data between wide and long formats.
- Grouping: Aggregating data based on specified criteria.
Conclusion
M language is a powerful tool for data transformation in Power BI and Excel. By understanding its syntax, functions, and capabilities, you can leverage M language to efficiently perform complex data manipulation tasks. Whether you are merging tables, filtering rows, or creating custom functions, M language provides the flexibility and power needed for advanced data preparation and analysis. Working with the M language provides powerful data manipulation and transformation capabilities. By accessing the Power Query Editor, creating new queries, and writing M code, you can efficiently prepare and shape your data for analysis. The M language provides the flexibility to handle complex data tasks, ensuring your data is ready for insightful visualization and reporting in Power BI.
If you like the article and would like to support me, make sure to:
- 👏 Like for this article and subscribe to our newsletter
- 📰 View more content on my DataSpoof website
- 🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
