Data Types in M Language

When working with Power Query in tools like Power BI, Excel, or other Microsoft platforms, understanding the M language data types is essential for creating efficient and reliable data transformations. M is the backbone of Power Query, and mastering its data types enables better control over your data.

What Are Data Types in M?

In Power Query, data types define how data is stored, processed, and displayed. They can be categorized into primitive types (like numbers and text), complex types (like tables and lists), and special types (like binary).

1. Number

• • Definition: Represents numeric values such as integers, decimals, and floating-point numbers.

• • Syntax: Simply write the number (e.g. 42, 3.14, 1.2e6).

Example:

let
    IntegerValue = 42,
    DecimalValue = 3.14,
    LargeNumber = 1.2e6
in
    {IntegerValue, DecimalValue, LargeNumber}

Result: {42, 3.14, 1200000}

When to Use: Use numbers for calculations, aggregations, and comparisons in your data transformations.

2. Text

• • Definition: Represents a sequence of characters (strings).

• • Syntax: Enclose text in double quotes ("Hello, World!").

Example:

let
    Greeting = "Hello, World!"
in
    Greeting

Result: "Hello, World!"

When to Use: Use text data for names, descriptions, or when concatenating values.

3. Logical

• • Definition: Represents Boolean values (true or false).

• • Syntax: Use the keywords true or false.

Example:

let
    IsActive = true,
    IsExpired = false
in
    {IsActive, IsExpired}

Result: {true, false}

When to Use: Use logical values for filters, conditional logic, and decision-making operations.

4. Null

• • Definition: Represents missing or undefined values.

• • Syntax: Use the keyword null.

Example:

let
    MissingData = null
in
    MissingData

Result: null

When to Use: Use null for handling incomplete datasets or indicating missing values.

5. Date

• • Definition: Represents calendar dates.

• • Syntax: Use the #date(year, month, day) function.

Example:

let
    Today = #date(2024, 12, 14)
in
    Today

Result: 2024-12-14

When to Use: Use date values for filtering, grouping, or performing time-based calculations.

6. Time

• • Definition: Represents a specific time of day.

• • Syntax: Use the #time(hour, minute, second) function.

Example:

let
    CurrentTime = #time(12, 30, 45)
in
    CurrentTime

Result: 12:30:45

When to Use: Use time values when working with schedules or time-sensitive data.

7. DateTime

• • Definition: Represents a combination of date and time.

• • Syntax: Use the #datetime(year, month, day, hour, minute, second) function.

Example:

let
    Now = #datetime(2024, 12, 14, 12, 30, 45)
in
    Now

Result: 2024-12-14T12:30:45

When to Use: Use DateTime when you need both date and time in operations like logging or scheduling.

8. Duration

• • Definition: Represents a time span (e.g., the difference between two dates or times).

• • Syntax: Use the #duration(days, hours, minutes, seconds) function.

Example:

let
    TimeSpan = #duration(1, 2, 30, 45) // 1 day, 2 hours, 30 minutes, 45 seconds
in
    TimeSpan

Result: 1.02:30:45

When to Use: Use duration for calculations like determining time differences or tracking periods.

9. List

• • Definition: Represents an ordered collection of values.

• • Syntax: Use curly braces {value1, value2, ...}.

Example:

let
    NumberList = {1, 2, 3, 4}
in
    NumberList

Result: {1, 2, 3, 4}

When to Use: Use lists for iterations or applying bulk transformations to multiple values.

10. Record

• • Definition: Represents a collection of fields (key-value pairs).

• • Syntax: Use square brackets [Field1 = Value1, Field2 = Value2, ...].

Example:

let
    Person = [Name = "John", Age = 30, IsActive = true]
in
    Person

Result: [Name = "John", Age = 30, IsActive = true]

When to Use: Use records for organizing structured data like profiles or settings.

11. Table

• • Definition: Represents structured data in rows and columns.

• • Syntax: Use Table.FromRecords({...}).

Example:

let
    Data = Table.FromRecords({
        [Name = "John", Age = 30],
        [Name = "Jane", Age = 25]
    })
in
    Data

Result:

Name	Age
John	30
Jane	25

When to Use: Use tables for tabular data transformations, such as filtering and aggregations.

12. Function

• • Definition: Represents reusable logic or transformations.

• • Syntax: Use (parameters) => expression.

Example:

Mlet
    Add = (x, y) => x + y,
    Result = Add(5, 10)
in
    Result

Result: 15

When to Use: Use functions to define custom calculations or reusable logic.

13. Binary

• • Definition: Represents raw binary data, such as files or images.

• • Syntax: Use Binary.FromText("text", encoding).

Example:

let
    FileContent = Binary.FromText("Hello World", BinaryEncoding.Base64)
in
    FileContent

Result: Binary value.

When to Use: Use binary for handling raw data, like files or images.

Final Thoughts

Understanding the various data types in M is crucial for creating robust Power Query transformations. Each data type serves a specific purpose, from handling numeric calculations and text processing to managing complex datasets. By mastering these types, you can unlock the full potential of Power Query and streamline your data workflows.

What’s your experience with these M language data types? Share your insights in the comments!