Basics of M Query in Power BI

What is M Query?

M Query (or Power Query Formula Language) is a functional programming language used in Power BI, Excel, and other Microsoft products to transform and prepare data before it’s loaded into the data model.

---

🔹 What is M Query?

• M stands for “Mashup”
• It is used in the Power Query Editor window
• You don’t need to write it manually often—Power BI generates it behind the scenes as you perform transformations like filtering, merging, etc.

---

🔹 M Query Syntax Basics

1. Let-In Expression

Every M query follows a basic structure:

let
    Source = ...,
    Step1 = ...,
    Step2 = ...
in
    Step2

• let defines variables/steps
• in returns the final result (usually the last step)

---

🔹 Example

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx")),
    Sheet1 = Source{[Name="Sheet1"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(Sheet1, {{"Date", type date}, {"Amount", type number}})
in
    ChangedTypes

let
    Source = Excel.Workbook(File.Contents("E:\WE-Dash\financial_sample.xlsx"), null, true),
    Sheet1 = Source{[Name="Sheet1"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet1, [IgnoreErrors=true]),
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {{"Date", type date}, {"Gross Sales", type number}})
in
    ChangedTypes

---

🔹 Common Functions

Function	Description
Table.SelectRows	Filters rows
Table.AddColumn	Adds a new column
Table.RemoveColumns	Deletes columns
Table.RenameColumns	Renames columns
Table.Combine	Combines multiple tables
Text.Upper / Text.Lower	Converts text to upper/lower case
Date.AddDays	Adds days to a date
Number.Round	Rounds numbers

---

🔹 Key Concepts

• Case-sensitive: source, Source are different
• Immutability: Each step doesn’t change the previous one—it creates a new result
• Step-by-step: Every transformation is defined in a step and can be reused

---

🔹 Where to Use M Query?

• In Power BI Desktop:
  • Go to Home → Transform data → Advanced Editor
  • Modify the auto-generated M code or write your own

---

🔹 Best Practices

• Give meaningful names to steps (e.g., FilteredRows, RenamedColumns)
• Use comments: // this is a comment
• Avoid hard-coding file paths; use parameters when possible

Applying it on Power BI Financial Sample dataset — it’s a common sample file used for demos and practice in Power BI. It usually includes fields like:

Column Name	Example Value
Segment	Government, Channel Partners
Country	United States, Canada
Product	Paseo, Velo
Discount Band	None, Low, Medium
Units Sold	1618
Manufacturing Price	250
Sale Price	350
Gross Sales	566,300
Discounts	0
Sales	566,300
COGS (Cost of Goods Sold)	283,150
Profit	283,150
Date	1/1/2014

---

🔹 Sample M Query Use Cases for This Dataset

1. Filter Only US Sales

let
    Source = Excel.Workbook(File.Contents("C:\Path\Financial Sample.xlsx")),
    Sheet = Source{[Name="Sheet1"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(Sheet,{{"Country", type text}, {"Sales", type number}}),
    US_Sales = Table.SelectRows(ChangedTypes, each [Country] = "United States")
in
    US_Sales

2. Add a New Column for Profit Margin

let
    Source = Excel.Workbook(File.Contents("C:\Path\Financial Sample.xlsx")),
    Sheet = Source{[Name="Sheet1"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(Sheet,{{"Profit", type number}, {"Sales", type number}}),
    AddProfitMargin = Table.AddColumn(ChangedTypes, "Profit Margin", each [Profit] / [Sales], type number)
in
    AddProfitMargin

3. Group by Country and Sum Sales

let
    Source = Excel.Workbook(File.Contents("C:\Path\Financial Sample.xlsx")),
    Sheet = Source{[Name="Sheet1"]}[Data],
    ChangedTypes = Table.TransformColumnTypes(Sheet,{{"Country", type text}, {"Sales", type number}}),
    Grouped = Table.Group(ChangedTypes, {"Country"}, {{"Total Sales", each List.Sum([Sales]), type number}})
in
    Grouped

---

---