M Language Basics

Power BI · Power Query (Data Transformation)

Theory

M (the Power Query Formula Language) is the functional language behind every Power Query step. A query is a let expression: each line inside let defines a named step, usually by calling a function on the previous step, and the in clause names the step whose result is returned. Step names with spaces are written as #"Step Name".

Functions follow Category.Action naming, such as Table.SelectRows, Table.AddColumn or Text.Upper. The keyword each creates a small function applied per row, and [Amount] inside it means the Amount field of the current row. So Table.SelectRows(#"Changed Type", each [Amount] > 1000) keeps only orders above ₹1,000. M is case-sensitive: Table.selectrows will fail. You rarely write M from scratch, but reading and small edits in the Advanced Editor are very useful.

Why we use it

The ribbon covers most needs, but knowing M lets you fix broken steps, rename references, use parameters and write logic the UI does not offer. It also makes troubleshooting refresh errors much easier.

When to use it

Use M edits for small tweaks: changing a filter value, adding a custom column, referencing a parameter. Keep business calculations that must respond to slicers in DAX, not M, because M runs only at refresh time.

How it works

  1. Query starts at the Source step
  2. Each step calls a function on the previous step
  3. each [Amount] > 1000 is tested on every row
  4. Condition true for the row? Yes: Keep the row. No: Drop the row.
  5. The in clause returns the final step
  6. Result loads into the model

Syntax

let
    Step1 = <expression>,
    #"Step Two" = Function(Step1, ...)
in
    #"Step Two"

Table.SelectRows(table as table, condition as function) as table
each [Column] > value   // row function shorthand

Example

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    Sales_Table = Source{[Item = "Sales", Kind = "Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table,
        {{"OrderID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Amount] > 1000)
in
    #"Filtered Rows"

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic