Ravindra BagaleCourses & study guides

6. Power Query Essentials

6.9 Introduction to the M Language

By Last updated:

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]).

M code path karaycha nahi, fakt vachta aala pahije. UI vaprun step banva, aani formula bar madhe bagha.

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.