Data Analysis with Microsoft Power BI

Module 4 – M Language

Institute of Advanced Technologies (IAT)

 Overview of M Language

• Microsoft Power Query provides a powerful "get data" experience that encompasses many
features.
• A core capability of Power Query is to filter and combine, that is, to "mash-up" data from
one or more of a rich collection of supported data sources.
• Any such data mashup is expressed using the Power Query formula language (informally
known as "M").
• Power Query embeds M documents in a wide range of Microsoft products, including Excel,
Power BI, Analysis Services, and Dataverse, to enable repeatable mashup of data.

2
 Expressions and values: Primitive value

• The central construct in M is the expression.

• An expression can be evaluated (computed), yielding a single value.

• A primitive value is single-part value, such as a number, logical, text, or null.

• A null value can be used to indicate the absence of any data.
• For example, 123 // A number
true // A logical
"abc" // A text
null // null value

• Note that the // indicates the start of a comment which continues to the end of the line.)

3
Expressions and values: List

• A list value is an ordered sequence of values.

• M supports infinite lists, but if written as a literal, lists have a fixed length.
• The curly brace characters { and } denote the beginning and end of a list.

{123, true, "A"} // list containing a number, a logical, and

// a text
{1, 2, 3} // list of three numbers

4
Expressions and values: record

• A record is a set of fields.

• A field is a name/value pair where the name is a text value that's unique within the field's
record.
• The literal syntax for record values allows the names to be written without quotes, a form also
referred to as identifiers. The following shows a record containing three fields named "A", "B",
and "C", which have values 1, 2, and 3.

[ A = 1,
B = 2,
C = 3 ]

5
Expressions and values: table

• A table is a set of values organized into columns (which are identified by name), and rows.
• There's no literal syntax for creating a table, but there are several standard functions that can
be used to create tables from lists or records.

#table( {"A", "B"}, { {1, 2}, {3, 4} } )

6
Expressions and values: function

• A function is a value that, when invoked with arguments, produces a new value.
• A function is written by listing the function's parameters in parentheses, followed by the
goes-to symbol =>, followed by the expression defining the function.
• That expression typically refers to the parameters (by name).

(x, y) => (x + y) / 2)

7
 Evaluation

• The evaluation model of the M language is modeled after the evaluation model commonly
found in spreadsheets, where the order of calculation can be determined based on
dependencies between the formulas in the cells.
• If you've written formulas in a spreadsheet such as Excel, you may recognize the formulas
on the left result in the values on the right when calculated:

[
A1 = A2 * 2,
A2 = A3 + 1,
A3 = 1
]
Power Query M

8
Evaluation

• Records can be contained within, or nest, within other records.

• You can use the lookup operator ([]) to access the fields of a record by name.
• For example, the following record has a field named Sales containing a record,
and a field named Total that accesses the FirstHalf and SecondHalf fields of
the Sales record:

[
Sales = [ FirstHalf = 1000, SecondHalf = 1100 ],
Total = Sales[FirstHalf] + Sales[SecondHalf] //2100
]

9
 Evaluation
• Records can also be contained within lists.
• You can use the positional index operator ({}) to access an item in a list by its
numeric index.
• The values within a list are referred to using a zero-based index from the beginning
of the list. [
Sales =
{
[
Year = 2007,
FirstHalf = 1000,
SecondHalf = 1100,
Total = FirstHalf + SecondHalf // 2100
],
[
Year = 2008,
FirstHalf = 1200,
SecondHalf = 1300,
Total = FirstHalf + SecondHalf // 2500
]
},
TotalSales = Sales{0}[Total] + Sales{1}[Total] // 4600
]
10
 Function

• In M, a function is a mapping from a set of input values to a single output value.

• A function is written by first naming the required set of input values (the parameters to the
function) and then providing an expression that computes the result of the function using
those input values (the body of the function) following the goes-to (=>) symbol. For example:

[
Add = (x, y) => x + y,
OnePlusOne = Add(1, 1), // 2
OnePlusTwo = Add(1, 2) // 3
]

11
Library

• M includes a common set of definitions available for use from an expression called the
standard library, or just library for short.
• These definitions consist of a set of named values.
• The names of values provided by a library are available for use within an expression
without having been defined explicitly by the expression.
• For example:

Number.E // Euler's number e (2.7182...)

[Link]("Hello", "ll") // 2

12
Operators
• M includes a set of operators that can be used in expressions.
• Operators are applied to operands to form symbolic expressions.

1 + 2 // numeric addition: 3
#time(12,23,0) + #duration(0,0,2,0) // time arithmetic: #time(12,25,0)

• Combination operator (&)

"A" & "BC" // text concatenation: "ABC"

{1} & {2, 3} // list concatenation: {1, 2, 3}
[ a = 1 ] & [ b = 2 ] // record merge: [ a = 1, b = 2 ]
• Note that some operators don't support all combinations of values. For example:
1 + "2" // error: adding number and text isn't supported

13
 Meta
• Metadata is information about a value that's associated with a value.
• Metadata is represented as a record value, called a metadata record.
• The fields of a metadata record can be used to store the metadata for a value.
• Every value has a metadata record. If the value of the metadata record hasn't been
specified, then the metadata record is empty (has no fields).
• For example, the following code associates a metadata record with Rating and Tags fields
with the text value "Mozart":
[
Composer = "Mozart" meta [ Rating = 5, Tags = {"Classical"} ],
ComposerRating = [Link](Composer)[Rating] // 5
]
In the above example, the expression in the ComposerRating field accesses the metadata record of the value in the Composer field, and then
accesses the Rating field of the metadata record.

14
 Let expression

• The let expression allows a set of values to be computed, assigned names, and then
used in a subsequent expression that follows the in.
• For example, in our sales data example, you could do:
let
Sales2007 =
[
Year = 2007,
FirstHalf = 1000,
SecondHalf = 1100,
Total = FirstHalf + SecondHalf // 2100
],
Sales2008 =
[
Year = 2008,
FirstHalf = 1200,
SecondHalf = 1300,
Total = FirstHalf + SecondHalf // 2500
]
in Sales2007[Total] + Sales2008[Total] // 4600

15
If expression

• The if expression selects between two expressions based on a logical condition.

• For example:

if [Revenue] > 500 and [Revenue] < 900 and

[Category] = "T-Shirts" then "Relevant
Products" else "Other"

Comparison operator

Logical operator

16
 Errors

• An error is an indication that the process of evaluating an expression couldn't produce a value.
• Errors are raised by operators and functions encountering error conditions or by using the error
expression.
• Errors are handled using the try expression.
• When an error is raised, a value is specified that can be used to indicate why the error occurred.

let Sales =
[
Revenue = 2000,
Units = 1000,
UnitPrice = if Units = 0 then error "No Units"
else Revenue / Units
],
UnitPrice = try [Link](Sales[UnitPrice])
in "Unit Price: " &
(if UnitPrice[HasError] then UnitPrice[Error][Message]
else UnitPrice[Value])

17
 Useful Accessing data functions

Name Description
[Link] Returns the contents of a CSV document as a table using the specified encoding.

[Link] Returns the tables in the current Excel Workbook.

[Link] Returns a table representing sheets in the given excel workbook.

[Link] Returns the contents of a JSON document. The contents may be directly passed to the function as text,
or it may be the binary value returned by a function like [Link].
[Link] Returns the binary contents of the file located at a path.

[Link] Returns a table with data relating to the tables in the specified MySQL Database.

[Link] Returns a table with data relating to the tables in the specified Oracle Database.

[Link] Returns a table containing SQL tables located on a SQL Server instance database.

[Link] Returns the contents of an XML document as a hierarchical table (list of records).

18
 Useful Table functions
 Construction  Conversions
Name Description Name Description

[Link] Returns a list of nested lists each

[Link] Returns a table from a list containing nested representing a column of values in the
lists with the column names and values. input table.
[Link] Converts a list into a table by applying the
specified splitting function to each item in [Link] Returns a table into a list by applying
the list. the specified combining function to each
row of values in a table.
[Link] Returns a table from a list of records.
[Link] Returns a list of records from an input
[Link] Creates a table from the list where each table.
element of the list is a list that contains
the column values for a single row. [Link] Returns a nested list of row values from
[Link] Returns a table with a column containing the an input table.
provided value or list of values.
[Link] Splits the specified table into a list of
tables using the specified page size.

19
 Useful Table functions
 Row Operations
Name Description Name Description

[Link] Returns a table that is the result of merging a list [Link] Returns a table containing the rows of the table
of tables. The tables must all have the same row type repeated the count number of times.
structure.
[Link] Returns a table where the rows beginning at an
[Link] Returns a table containing only the rows that have offset and continuing for count are replaced
the specified text within one of their cells or any with the provided rows.
part thereof.
[Link] Returns a table with the rows in reverse order.
[Link] Returns the first row from a table.
[Link] Returns the first column of the first row of the [Link] Returns a table containing only the rows that
table or a specified default value. match a condition.

[Link] Returns a table with the list of rows inserted into [Link] Returns a single row from a table.
the table at an index. Each row to insert must match
the row type of the table.. [Link] Returns a table that does not contain the first
[Link] Returns the last row of a table. row or rows of the table.

[Link] Returns a table with the specified number of rows [Link] Returns a list containing the first count rows
removed from the table starting at an offset. specified and the remaining rows.

20
 Useful Table functions
 Column Operations
Name Description Name Description

[Link] Returns the values from a column in a table. [Link] Given a table and attribute column containing
pivotValues, creates new columns for each of the pivo
[Link] Returns the names of columns from a table. values and assigns them values from the valueColumn.
optional aggregationFunction can be provided to handl
[Link] Returns a list with the names of the columns multiple occurrence of the same key value in the
that match the specified types. attribute column.

[Link] Demotes the header row down into the first row [Link] Given a list of table columns, transforms those colum
of a table. into attribute-value pairs

[Link] Indicates whether the table contains the [Link] Translates all columns other than a specified set int
specified column(s). attribute-value pairs, combined with the rest of the
values in each row.
[Link] Promotes the first row of the table into its
header or column names. [Link] Returns a table with the columns renamed as specified

[Link] Returns a table without a specific column or

[Link] Returns a table that contains only specific columns.
columns.
[Link] Returns a table with specific columns in an [Link] Transforms column names by using the given function.
order relative to one another.

21
 Useful Table functions
 Transformation
Name Description Name Description

[Link] Adds a column named newColumnName to a table. [Link] Replaces the error values in the specified columns wi
the corresponding specified value.
[Link] Returns a table with a new column with a
specific name that, for each row, contains an
index of the row in the table. [Link] Replaces oldValue with newValue in specific columns o
a table, using the provided replacer function, such a
[Link] [Link] merges columns using a
[Link] or [Link].
combiner function to produce a new column.
[Link] is the inverse of
[Link]. [Link] Returns a new set of columns from a single column
applying a splitter function to each value.
[Link] Replaces null values in the specified column or
columns of the table with the most recent non- [Link] Transforms the values of one or more columns.
null value in the column.
[Link] Returns a table from the table specified where [Link] Transforms the column types from a table using a type
the value of the next cell is propagated to the
null values cells above in the column specified.
[Link] Transforms the rows from a table using a transform
[Link] Groups table rows by the values of key columns function.
for each row.
[Link] Returns a table with columns converted to rows and ro
[Link] Joins the rows of table1 with the rows of table2 converted to columns from the input table.
based on the equality of the values of the key
columns selected by table1, key1 and table2,
key2.

22
 Useful Table functions
 Membership  Ordering
Name Description Name Description

[Link] Determines whether the a record appears as a [Link] Returns the largest row or rows from a table using a
row in the table. comparisonCriteria.
[Link] Removes duplicate rows from a table,
ensuring that all remaining rows are
distinct. [Link] Returns the largest N rows from a table. After the ro
are sorted, the countOrCondition parameter must be
[Link] Determines whether a table contains only specified to further filter the result.
distinct rows.
[Link] Determines the position or positions of a [Link] Returns the smallest row or rows from a table using a
row within a table. comparisonCriteria.

[Link] Determines the position or positions of any [Link] Returns the smallest N rows in the given table. After
of the specified rows within the table. the rows are sorted, the countOrCondition parameter
must be specified to further filter the result.
[Link] Removes all occurrences of rows from a
table. [Link] Appends a column with the ranking of one or more othe
columns.
[Link] Replaces specific rows from a table with the
new rows. [Link] Sorts the rows in a table using a comparisonCriteria
a default ordering if one is not specified.

23
 Useful Number functions
 Information, Conversion and Formatting  Rounding and Random
Name Description
Name Description
[Link] Returns true if a value is an even number.
[Link] Returns a nullable number (n) if value is an
[Link] Returns true if a value is [Link]. integer.
[Link] Returns [Link](value) when value >= 0 and
[Link] Returns true if a value is an odd number. [Link](value) when value < 0.
[Link] Returns the largest integer less than or equal to a
[Link] Returns a currency value from the given value. number value.

[Link] Returns a decimal number value from the given value. [Link] Returns [Link](x) when x >= 0 and
[Link](x) when x < 0.
[Link] Returns a Double number value from the given value. [Link] Returns the larger integer greater than or equal to
a number value.
[Link] / Returns a signed 8/16/32/64-bit integer number value
[Link] / from the given value. [Link] Returns a random fractional number between 0 and 1.
[Link] /
[Link] [Link] Returns a random number between the two given number
values.
[Link] Returns a number value from a text value.

[Link] Converts the given number to text.

[Link] Returns a percentage value from the given value.

24
 Useful Number Function
 Operations
Name Description
 Bytes
Name Description
[Link] Returns the absolute value of a number.
[Link] Returns the result of a bitwise AND
[Link] Returns a number representing e raised to a operation on the provided operands.
power.
[Link] Returns the result of a bitwise NOT
[Link] Returns the factorial of a number. operation on the provided operands.
[Link] Returns the result of a bitwise OR
[Link] Divides two numbers and returns the whole part
operation on the provided operands.
of the resulting number.
[Link] Returns the result of a bitwise shift left
[Link] Returns the natural logarithm of a number.
operation on the operands.

[Link] Returns the logarithm of a number to the base. [Link] Returns the result of a bitwise shift right
operation on the operands.
Number.Log10 Returns the base-10 logarithm of a number. [Link] Returns the result of a bitwise XOR
operation on the provided operands.
[Link] Divides two numbers and returns the remainder of
the resulting number.
[Link] Returns the number of total permutations of a
given number of items for the optional
permutation size.
[Link] Returns a number raised by a power.
[Link] Returns 1 for positive numbers, -1 for negative
numbers or 0 for zero.
[Link] Returns the square root of a number.

25
 Useful Text Function

 Extraction  Membership
Name Description Name Description
[Link] Returns the number of characters in a text [Link] Returns true if a text value substring was
value. found within a text value string; otherwise,
[Link] Returns a character starting at a zero-based false.
offset.
[Link] Returns the first occurrence of substring
[Link] Returns a number of characters from a text in a string and returns its position
value starting at a zero-based offset and starting at startOffset.
for count number of characters.
[Link] Returns a logical value indicating whether
[Link] Returns the count of characters from the a text value substring was found at the
start of a text value. beginning of a string.
[Link] Returns the number of characters from the [Link] Returns a logical value indicating whether a
end of a text value. text value substring was found at the end of a
string.
[Link] Returns the text representation of a number,
date, time, datetime, datetimezone, logical,
duration or binary value.

26
 Useful Text Function
 Transformations  Modifications
Name Description
[Link] Returns the portion of text after the specified
delimiter. Name Description
[Link] Returns the portion of text before the [Link] Returns a text value with newValue inserted
specified delimiter. into a text value starting at a zero-based
offset.
[Link] Returns the portion of text between the
specified startDelimiter and endDelimiter. [Link] Removes all occurrences of a character or
[Link] Returns the original text value with non- list of characters from a text value. The
printable characters removed. removeChars parameter can be a character
value or a list of character values.
[Link] Returns the lowercase of a text value.
[Link] Replaces all occurrences of a substring
[Link] Returns the uppercase of a text value. with a new text value.
[Link] Selects all occurrences of the given
[Link] Returns a text value with first letters of character or list of characters from the
all words converted to uppercase. input text value.
[Link] Returns a text value composed of the input
text value repeated a number of times.
[Link] Reverses the provided text.

[Link] Returns a list containing parts of a text

value that are delimited by a separator
text value.
[Link] Removes any occurrences of characters in
trimChars from text.
27
 Useful Date/Time Function

 Date functions
Name Description Name Description
#date Creates a date value from year, month, and [Link] Returns the month from a DateTime value.
day.
[Link] Returns the day for a DateTime value. [Link] Returns the name of the month component.

[Link] Returns a number (from 0 to 6) indicating [Link] Returns a number between 1 and 4 for the
the day of the week of the provided value. quarter of the year from a DateTime value.
[Link] Returns the day of the week name. [Link]
Returns the start of the day.
[Link] Returns a number that represents the day of [Link] Returns the start of the month.
the year from a DateTime value.
[Link] Returns the number of days in the month [Link] Returns the start of the quarter.
from a DateTime value.
[Link] Returns the end of the day. [Link] Returns the start of the week.

[Link] Returns the end of the month. [Link] Returns the start of the year.

[Link] Returns the end of the quarter. [Link] Returns a number for the count of week in the
current month
[Link] Returns the end of the week. [Link] Returns a number for the count of week in the
current year.
[Link] Returns the end of the year.
[Link] Returns the year from a DateTime value.

28
 Useful Date/Time Function

 DateTime and DateTimeZone functions  Duration functions

Name Description
Name Description
[Link] Returns the days portion of a duration.
[Link] Returns a date part from a DateTime
value.
[Link] Returns the hours portion of a duration.
[Link] Returns a datetime value set to the
current date and time on the system. [Link] Returns the minutes portion of a duration.
[Link] Returns a time part from a DateTime
value. [Link] Returns the seconds portion of a duration.
[Link] Returns a DateTimeZone value set to
the current date, time, and timezone [Link] Returns the total magnitude of days from a
offset on the system. Duration value
[Link] Returns the current date and time in [Link] Returns the total magnitude of hours from a
UTC (the GMT timezone). Duration value.
[Link] Returns a DateTime value set to the [Link] Returns the total magnitude of minutes from a
current system date and time.
Duration value .
#datetimezone Creates a datetimezone value from
year, month, day, hour, minute, [Link] Returns the total magnitude of seconds from a
second, offset-hours, and offset- duration value.
minutes.
#duration Creates a duration value from days, hours,
minutes, and seconds.

29
 Exercise-1
Create a new Power BI report, and load data from the [Link] file. Create two custom columns
named Left over minutes and Hours to show the number of hours and minutes for each film.

The resulted output file

Note: The only two columns you'll need to do this exercise are the film title and its length in minutes, so you could if you
like use Query Editor to remove all of the other columns (this is optional).

Use the & symbol to concatenate or join together columns to create one more column and the output
is as follow.

30
 Exercise-2
The dataset [Link] enlists the passengers on the Titanic voyage and categorizes over 800
passengers based on status of survival, sex, age, passenger class. You need to create a single
query that specifies what data to include and how to transform the given dataset to produce a
clean and usable data model to place analytics on top of. Using M language,

 Get data using blank query.

 Power BI Desktop -> Other – Blank Query

 Rename the new Query as SurvivalLog

31
 Exercise-2

• Change case of the character using [Link]/ [Link] / [Link] function

ChangeCase = [Link](Replace1,{"Gender",[Link]})
Exercise-2
 Open View menu -> Advanced Editor

33
 Exercise-2

 Create a new variable named GetPassengers to add the content from csv document file and the
expression as follows:

let GetPassengers = [Link]([Link]("C:\Users\Lenovo\Desktop\De Heus-Power

BI\CSV\[Link]"), [Delimiter=",", Encoding=1252])
in
GetPassengers

34
 Exercise-2
• Remove Column using Table. RemoveColumns

let GetPassengers = [Link]([Link]("C:\Users\Lenovo\Desktop\De Heus-Power

BI\CSV\[Link]"), [Delimiter=",", Encoding=1252]),

RemoveCols = [Link](GetPassengers,"Column7" )
in
RemoveCols

• If you want to remove multiple columns, you can remove list of columns.

RemoveCols = [Link](GetPassengers, {"Column7", "Column8"})

 Exercise-2
• Promote Header using Table. PromoteHeaders

let GetPassengers = [Link]([Link]("C:\Users\Lenovo\Desktop\De Heus-Power

BI\CSV\[Link]"), [Delimiter=",", Encoding=1252]),

RemoveCols = [Link](GetPassengers, {"Column7", "Column8"}),

PromoteCols = [Link](RemoveCols, [PromoteAllScalars=true])

in

PromoteCols

Note: PromoteAllScalars value is only true.

 Exercise-2

• Rename the columns (Pclass as PassengerClass and Sex as Gender value in the list
using [Link]

RenameCols = [Link](PromoteCols,
{{"Pclass","PassengerClass"},{"Sex" ,"Gender"} })
 Exercise-2

• Filter the unclear data in the value of Age Column

• Change the data type of some columns (text data type to Number or Integer)

• Filter the kids that the age is over 5.

FilterNA = [Link](RenameCols, each [Age] <>

"NA"),

ChangeTypes =

[Link](FilterNA,{{"Age",[Link]},

{"PassengerId",[Link]}}),

FilterKids = [Link](ChangeTypes, each [Age] > 5)

 Exercise-2

• Replace values for Survived Column using [Link]

• In this example, the value of Survived Column will be replaced (0 -> No, 1 -> Yes)

Replace0 = [Link](FilterKids, "0","No", [Link], {"Survived"}),

Replace1 = [Link](Replace0, "1","Yes", [Link], {"Survived"})
 Exercise-2

• Adding a calculated columns to calculate the average age of the female passengers
and male passengers and then compare the roundoff age difference between them.

//add calculated column for average age differences between

male and female
Female = [Link](ChangeCase,each [Gender]
="Female"),
AvgFemale = [Link]([Link](Female, "Age")),
Male = [Link](ChangeCase,each [Gender] ="Male"),
AvgMale = [Link]([Link](Male, "Age")),
AddCol = [Link](ChangeCase, "AgeDiff" , each if
[Gender]="Female" then [Age] - AvgFemale else [Age]-AvgMale),
RoundDiff = [Link](AddCol, {"AgeDiff", each
[Link](_,2)})
 Summary

• Explain M language - M Query is a “mashup” query language that can be used to query a lot
of data from many sources.

• Discuss data types – primitive values, list, record, table

• Function and evaluation

 If expression

 Let expression

 Error handling

 Some useful built-in functions: accessing data sources, table, number, date and time
• Power Query M functions detail link : [Link]