Power BI Lesson 30 – Introduction to M Language | Dataplexa

Power Query · Lesson 30

The M Language in Depth

Every step you create in the Power Query UI generates M code behind the scenes. You have been reading and lightly editing that code throughout this section. This lesson goes deeper — covering how M is actually structured, how queries reference each other, how to write reusable functions, and the patterns that distinguish M code that is easy to maintain from M code that breaks without warning.

The let…in Structure

Every Power Query query is a single M expression. That expression is almost always a let…in block. The let section defines named values in order. The in section specifies which named value is the final output of the query — usually the last step.

// Anatomy of a Power Query M query
let
    // Each line is: StepName = expression,
    // Steps are comma-separated (last step has no trailing comma)
    // Each step can reference any earlier step by name

    Source          = Csv.Document(File.Contents("C:\sales.csv"),
                          [Delimiter=",", Encoding=65001]),

    PromotedHeaders = Table.PromoteHeaders(Source,
                          [PromoteAllScalars=true]),

    ChangedType     = Table.TransformColumnTypes(PromotedHeaders,
                          {{"OrderID",   Int64.Type},
                           {"Revenue",   type number},
                           {"OrderDate", type date}}),

    FilteredRows    = Table.SelectRows(ChangedType,
                          each [Revenue] > 0),

    RenamedCols     = Table.RenameColumns(FilteredRows,
                          {{"OrderID", "Order_ID"}})

// The "in" value is what the query returns —
// always the name of the last (or desired output) step
in
    RenamedCols

Concept	Rule
Step names	Any valid identifier — no spaces unless wrapped in `#"Step Name With Spaces"`. The UI wraps names with spaces automatically.
Step order	Steps are evaluated lazily — M only computes a step when it is needed. In practice, evaluation follows dependency order, not physical order. A step can only reference steps defined before it.
Commas	Each step ends with a comma except the last one before `in`. Missing or extra commas are the most common M syntax error.
The in value	The query returns whatever expression follows `in` — typically the last step name, but can be any step or expression. Changing the `in` value to an earlier step name is how you debug a query mid-pipeline.
Case sensitivity	M is case-sensitive. `Table.AddColumn` and `table.addcolumn` are different — the second does not exist. Column names referenced in square brackets are also case-sensitive.

M Data Types

M is a strongly typed language. Every value has a type. Understanding the core types prevents the most common errors — particularly the difference between a table, a list, and a record, which are all distinct and not interchangeable.

Table

Rows and columns — the primary output type of most queries. Access a column as a list: table[Column]. Access a row as a record: table{0} (zero-indexed).

List

An ordered sequence of values in curly braces: {1, 2, 3} or {"a","b"}. Access by index: list{0}. Used for column names, allowed values, and date sequences.

Record

Named field/value pairs in square brackets: [Name="Alice", Age=30]. Access a field: record[Name]. Each row of a table is a record when referenced inside each.

Scalar types

type text, type number, Int64.Type, type date, type logical, type null. Used in Table.TransformColumnTypes and Table.AddColumn as the third argument.

Function

Functions are first-class values in M — they can be assigned to variables, passed as arguments, and returned from expressions. each [Revenue] * 2 is shorthand for (_) => [Revenue] * 2.

Null

The absence of a value. Distinct from zero, empty string, and false. Most arithmetic with null produces null. Most text operations with null produce an error. Check explicitly with [x] = null.

each and _ — How Row-Level Logic Works

each is syntactic sugar — a shortcut for writing a single-argument function where the argument is the current row. Inside each, the current row is always available as a record. Column values are accessed with [ColumnName]. The underscore _ refers to the entire current row (or value when used in List functions).

// These two expressions are identical — "each" is shorthand for (_) =>
Table.SelectRows(Source, each [Revenue] > 0)
Table.SelectRows(Source, (_) => _[Revenue] > 0)

// Inside Table.AddColumn, _ refers to the current row record
Table.AddColumn(Source, "RevenuePerUnit",
    each [Revenue] / [Quantity])           // column access via [Name]

Table.AddColumn(Source, "RevenuePerUnit",
    (_) => _[Revenue] / _[Quantity])       // explicit _ row record

// In List functions, _ refers to the current list element (not a row)
List.Select({1, 2, 3, 4, 5}, each _ > 3)
// Returns: {4, 5}  — _ is each number in the list

List.Transform({"alice", "bob", "carol"}, each Text.Proper(_))
// Returns: {"Alice", "Bob", "Carol"}  — _ is each text value

// When you need to call a function that takes a value (not a row),
// use the full (_) => form for clarity
Table.TransformColumns(Source, {
    {"Revenue",  each _ * 1.1},     // _ is the cell value (a number)
    {"Status",   each Text.Upper(_)} // _ is the cell value (text)
})

Query References — Building on Other Queries

Any query in the Queries pane can reference any other query by name — just use the query name as a value. This is how staging queries, helper tables, and modular pipelines are built. The reference is live: if the referenced query changes, the referencing query picks up the change automatically.

// Query: RawOrders — loads the CSV
let
    Source = Csv.Document(File.Contents(FolderPath & "orders.csv"),
                 [Delimiter=",", Encoding=65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    Promoted

// Query: CleanOrders — references RawOrders and cleans it
let
    Source      = RawOrders,               // ← query reference by name
    Trimmed     = Table.TransformColumns(Source,
                      {{"CustomerName", Text.Trim},
                       {"Status",       Text.Trim}}),
    ChangedType = Table.TransformColumnTypes(Trimmed,
                      {{"Revenue",   type number},
                       {"OrderDate", type date}})
in
    ChangedType

// Query: UKOrders — filters CleanOrders for UK region
let
    Source = CleanOrders,                  // ← references CleanOrders
    UK     = Table.SelectRows(Source,
                 each [Region] = "UK")
in
    UK

// Query: USOrders — filters CleanOrders for US region
let
    Source = CleanOrders,
    US     = Table.SelectRows(Source,
                 each [Region] = "US")
in
    US

Query dependency chain — how references flow

CSV File

RawOrders

CleanOrders

UKOrders

USOrders

Cleaning logic lives once in CleanOrders · UKOrders and USOrders inherit all cleaning automatically · fix a bug in CleanOrders and both region queries update · disable load on RawOrders and CleanOrders to keep the model clean

Writing Reusable Functions

Any query that returns a function value — rather than a table — becomes a reusable function that other queries can call. Define it once, call it from ten queries. When the logic changes, update the function and every query that calls it updates automatically.

// Function query: fnCleanText
// Returns a function that trims, cleans, and proper-cases a text value
// Create this as a new blank query named "fnCleanText"

(inputText as text) as text =>
    Text.Proper(Text.Clean(Text.Trim(inputText)))

// Usage in any other query:
Table.TransformColumns(Source, {
    {"CustomerName", fnCleanText},
    {"City",         fnCleanText},
    {"Status",       fnCleanText}
})

// ─────────────────────────────────────────────────────────────
// Function with multiple parameters and default value
// Function query: fnSafeDiv
// Returns a / b, or a default value if b = 0 or either is null

(a as nullable number, b as nullable number,
 optional defaultVal as nullable number) as nullable number =>
let
    FallbackVal = if defaultVal = null then null else defaultVal
in
    if a = null or b = null or b = 0
    then FallbackVal
    else a / b

// Usage:
Table.AddColumn(Source, "RevenuePerUnit",
    each fnSafeDiv([Revenue], [Quantity], null))

// ─────────────────────────────────────────────────────────────
// Function that accepts a table and returns a cleaned version
// Function query: fnStandardClean

(tbl as table) as table =>
let
    Trimmed     = Table.TransformColumns(tbl,
                      List.Transform(Table.ColumnNames(tbl),
                          each {_, each if _ is text
                                        then Text.Trim(_) else _})),
    Typed       = Table.TransformColumnTypes(Trimmed,
                      {{"OrderDate", type date},
                       {"Revenue",   type number}})
in
    Typed

// Usage — apply the same cleaning to multiple source tables:
CleanSales   = fnStandardClean(RawSales),
CleanReturns = fnStandardClean(RawReturns)

fnCleanText applied to a CustomerName column

Raw CustomerName	After fnCleanText
" alice brown "	Alice Brown
"BOB SINGH"	Bob Singh
"carol lee" (tab char)	Carol Lee
" DAVID KIM "	David Kim

One function defined once · called on CustomerName, City, Status in one step · update fnCleanText and all three columns update everywhere it is used

Key M Operators and Expressions

// ── Comparison operators ───────────────────────────────────────
=    // equals               "Alice" = "Alice" → true
<>   // not equals           "Alice" <> "Bob"  → true
>    // greater than         5 > 3             → true
>=   // greater or equal     5 >= 5            → true
<    // less than            3 < 5             → true
<=   // less or equal        3 <= 3            → true

// ── Logical operators ──────────────────────────────────────────
and  // both true            true and false    → false
or   // either true          true or false     → true
not  // negation             not true          → false

// ── Text operator ─────────────────────────────────────────────
&    // concatenation        "Hello" & " World" → "Hello World"
     // WARNING: null & text → error, use null checks

// ── List constructors ──────────────────────────────────────────
{1, 2, 3}                    // literal list
{1..10}                      // range: {1,2,3,4,5,6,7,8,9,10}
{"a".."e"}                   // char range: {"a","b","c","d","e"}
{"A".."Z"}                   // uppercase alphabet list

// ── Record constructors ────────────────────────────────────────
[Name = "Alice", Age = 30]   // literal record
[Name = "Alice", Age = 30][Name]  // access field → "Alice"

// ── Table row / column access ──────────────────────────────────
MyTable{0}                   // first row as a record
MyTable{0}[Revenue]          // Revenue value of first row → 1200
MyTable[Revenue]             // entire Revenue column as a list
List.Sum(MyTable[Revenue])   // sum of Revenue column

// ── Type checking ──────────────────────────────────────────────
1 is number                  // → true
"hello" is text              // → true
null is null                 // → true
Value.Is(1, type number)     // → true  (programmatic form)

Debugging M — Techniques That Actually Work

Change the in value

In the Advanced Editor, change in FinalStep to in SuspectStep. The preview shows the table at that point in the pipeline — you can inspect the data before the error occurs. Change it back when done.

Click the step in Applied Steps

Click any step name in the Applied Steps panel to see the table state at that step. Click an error cell to read the full error message in the status bar below the grid. This is the fastest first-pass debugging technique.

Use a diagnostic scalar step

Add a step that returns a single value — RowCount = Table.RowCount(#"Previous Step") — and set it as the in value. The preview shows the number. Useful for verifying filter counts mid-pipeline without loading the full table.

Column Quality + Keep Errors

Enable Column Quality (View tab) to see error percentages at a glance. Then use Home → Keep Rows → Keep Errors on the problematic column to isolate just the error rows. Read the raw values causing the errors before deciding how to handle them.

Teacher's Note: The moment you understand that every Applied Step is just a named variable in a let…in block, Power Query stops being a black box and becomes a programming environment you can read, debug, and reason about. The UI generates the code; you read and occasionally edit it. You are never obligated to use the UI for anything — every transformation that exists in the UI can also be written directly in M, and there are many transformations that only exist in M. Start treating the Advanced Editor as a first-class tool, not a last resort.

Practice

Practice 1 of 3

In a let…in block, every step definition ends with a comma except for the ___ step before the in keyword.

Practice 2 of 3

Inside an each expression, column values are accessed using square brackets like [Revenue] — and the entire current row is also available as the underscore variable _, which is shorthand for the single argument of the anonymous function ___ => expression.

Practice 3 of 3

To quickly inspect the table state at a specific mid-pipeline step without deleting later steps, you open the Advanced Editor and temporarily change the in value to the ___ of the step you want to examine.

Lesson Quiz

Quiz 1 of 3

A colleague's query throws a syntax error: "Token Comma expected." They paste their M code and it looks correct. What is the most likely cause?

M requires a semicolon at the end of every step — the colleague is using commas which are only valid inside list and record literals, not as step separators in a let block The last step before in has a trailing comma, or one of the middle steps is missing its comma. M requires commas between all steps except after the very last step before in. The step names contain spaces — M does not allow spaces in step names unless the entire query is wrapped in a Section declaration at the top of the file The query references another query by name — cross-query references require an explicit import statement at the top of the let block or M will treat the reference as an undefined identifier

Quiz 2 of 3

You write a query called fnSafeDiv that returns a function. When you try to load it into the model, Power BI shows an error saying it cannot load a function value as a table. What should you do?

Wrap the function in Table.FromValue() — this converts any M value, including a function, into a single-cell table that can be loaded into the model Functions must always return a table — rewrite fnSafeDiv to return Table.FromList({result}) so Power BI can load the output Right-click the fnSafeDiv query in the Queries pane and uncheck "Enable Load." Function helper queries should never be loaded into the model — they exist only to be called by other queries. Add a type table annotation to the function's return type declaration — Power BI requires an explicit table return type on all queries that appear in the Queries pane

Quiz 3 of 3

You have a query called CleanOrders that three other queries reference. You discover a bug in the trimming logic inside CleanOrders. What happens when you fix it?

All three queries that reference CleanOrders automatically inherit the fix on the next refresh — query references are live and always use the current output of the referenced query, not a cached copy from when the reference was created. Only queries that were created after the bug was introduced will pick up the fix — queries that existed before the bug was introduced cached the correct output and continue using it The three referencing queries must be manually refreshed individually — fixing the source query does not trigger a cascade refresh in dependent queries unless "Enable Query Folding" is turned on The fix only applies if you right-click each referencing query and choose "Refresh Preview" — query references store a snapshot of the source query output rather than a live link

Next up — Lesson 31 begins Section III and introduces DAX, covering what makes it different from Excel formulas and Power Query M, how the formula engine evaluates expressions, and why understanding evaluation context is the single most important concept in the entire DAX language.

← Previous Course Index Next →

Power BI Course