Ravindra BagaleCourses & study guides मराठी Track your progress

6. Power Query Essentials

6.9 Introduction to the M Language

By Last updated:

Start with a problem

Salman is asked in an interview: Can you read M code? He has only used the ribbon.

Explain simply

Every query is M code in a let … in block. Each line is a named step that usually uses the previous step. You do not need to write M from scratch – read it and make small edits.

Show the data

Step name M function
Source Csv.Document(File.Contents(...))
Removed Top Rows Table.Skip(Source, 2)
Promoted Headers Table.PromoteHeaders(...)
Removed Total row Table.SelectRows(..., each [Order ID] <> "Total")

About this data: This table uses columns: Step name, M function. This table uses columns: Line in Advanced Editor, Meaning. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_raw_pune_export.csv (source: src/practice/).

Demonstrate

  1. Open Home › Advanced Editor on Orders.
  2. Find #"Removed Top Rows" = Table.Skip(Source, 2), – the number 2 is the rows removed.

  3. Change 2 to 3, click Done, and look at the data.

    Your result should look like this:

    First row now
    Order ID row is gone – headers break

  4. Change it back to 2. Note: M is case-sensitive (Table.Skip, not table.skip).

Show the result

Your result should look like this (fictional sample):

Line in Advanced Editor Meaning
let start of steps
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), step uses previous step
in #"Changed Type" value returned

Explain what happened

The step names in Applied Steps are the variable names in M; the last name after in is the query result.

Explore further

  • Custom functions such as fnDeliveryBand are M functions you can invoke from Add Column (Module 8.4).

Detailed reference for this lesson:

Every query is an M expression. Open Home › Advanced Editor to see the full code. M queries are built with a let … in block: each line defines a named step that usually refers to the previous one.

let
    Source = Csv.Document(
        File.Contents("C:\QuickCommerce\Blinkit_Orders_2025.csv"),
        [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]
    ),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {
        {"OrderID", type text},
        {"OrderDateTime", type datetime},
        {"Quantity", Int64.Type},
        {"Amount", Currency.Type},
        {"DeliveryTimeMins", Int64.Type}
    }),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {
        {"OrderID", "Order ID"}, {"OrderDateTime", "Order DateTime"},
        {"DeliveryTimeMins", "Delivery Time Mins"}
    }),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Quantity] > 0),
    #"Added Order Date" = Table.AddColumn(#"Filtered Rows", "Order Date",
        each DateTime.Date([Order DateTime]), type date),
    #"Added Order Hour" = Table.AddColumn(#"Added Order Date", "Order Hour",
        each Time.Hour([Order DateTime]), Int64.Type)
in
    #"Added Order Hour"

Key points about M:

  • M is case-sensitive: Table.SelectRows works, table.selectrows does not.
  • Step names with spaces are written as #"Step Name".
  • each is a shortcut for a function applied to every row; [Column] refers to a column in the current row.
  • The value after in is what the query returns (normally the last step).
  • Comments: // single line and /* multi-line */.

Converting UTC timestamps to IST

Some systems store timestamps in UTC. To convert a UTC column to Indian Standard Time (UTC+5:30), add a custom column like the one below. DateTime.AddZone(…, 0) marks the value as UTC, DateTimeZone.SwitchZone(…, 5, 30) converts it to IST, and DateTimeZone.RemoveZone returns a normal Date/Time. Our sample files are already in IST, so they do not need this step.

= Table.AddColumn(Source, "Order DateTime", each
    DateTimeZone.RemoveZone(
        DateTimeZone.SwitchZone(DateTime.AddZone([OrderDateTimeUTC], 0), 5, 30)
    ), type datetime)

A small custom function

// Query named fnDeliveryBand
(mins as nullable number) as text =>
    if mins = null then "Not delivered"
    else if mins <= 10 then "Within 10 min"
    else if mins <= 20 then "11-20 min"
    else "Over 20 min"

Use it in Add Column › Invoke Custom Function, or in a custom column: fnDeliveryBand([Delivery Time Mins]).

You do not have to memorise M code; you only need to be able to read it. Make the step with the UI, then look at it in the formula bar.

Ravindra Bagale's Tip

Look, friends: M is case-sensitive, and that trips up almost every beginner: text.upper or [amount] fails, while Text.Upper and [Amount] work. Let the UI write the first version of a step, then edit the M code in the formula bar instead of typing everything from memory. Remember this rule.

Fix common mistakes

Wrong result Reason Correction
Expression.Error: The name 'table.skip' wasn't recognized M is case-sensitive Use Table.Skip
Token Comma expected Missing comma between steps Every step line except the last ends with a comma
Step with spaces fails Name not quoted Write #"Step Name"

Practise independently

Practice task

Which M function removes the Total row in our query?

Hints
  • Filter rows.
Solution

Table.SelectRows(..., each [Order ID] <> "Total").

Think like a professional

Reading M lets you fix broken queries others built – a valuable job skill.

Ravi Sir's Cards for this lesson

Introduction to the M language. Every click in Power Query writes code. Reading it helps you fix and adjust steps.Revision card

मराठी cards