M language supports a variety of primitive and complex value types, including the common value types (e.g., Number, Text and DateTime) that can be found in most of general programming languages and the value types specifically designed to support dataset handling (e.g., Table, Record and Binary).
A primitive value is a single piece, atomic value of a single value type. M supports the following primitive value types:
A table value is arguably the most important object in a Power Query query. Many dataset transformations are operated on a table value. A table value is a 2-dimensional structure consisting of an ordered sequence of rows (Records) in vertical direction and an a list of columns (Lists) in horizontal direction.
A table value is normally created by the data extraction operation that reads data from a data source such as relational databases or text files. Alternatively, a table value can be created manually using #table.
Once a table value is defined, a collection of Table functions can be called to handle the operations on the table value. For example, we can filter the table by rows using the SelectRows function or by columns using the SelectColumns function. The full list of Table functions can be found here.
From the table value, we can access a specific row in the table.
The returned row is in Record type. A record is an ordered sequence of fields and a field is a key-value pair with the column name as the key and the value of that column in the record as the value, such as [Name= “Mike”, Age=39]. A field value can be any kind of type, not only the primitive types but also the structured types.
From the table value, we can also access a specific column in the table.
If we check the type of the return column, we can find that the column with in a table is a List value.
List values can be very useful at the intermediate steps of dataset transformations. For example, we can calculate the average value of a column and use it as benchmark for other calculations.
Apart from accessing list values from a table value, we can also create lists from scratch. One common use case is to generate a sequence of consecutive date list for building the Date dimension table.
A binary value represents a sequence of bytes that is often used for file format data, such as csv files.
M language supports a collection of functions for transforming the binary value data to table value data, such as Csv.Document.
M is a functional programming language that construct a program through the evaluation of functions. A function value is a mapping between a set of input arguments and an output value that can be define as:
(arg1, arg2) => Let result = do something In result
Functions can be very useful when working with complex transformation where some logic needs to be called multiple times.