Day 21: Power Query M Language Basics

M (also called Power Query Formula Language) is the functional language behind Power Query transformations in Power BI and Excel. Every step you create in the Power Query Editor through the graphical interface generates M code behind the scenes. You can view and edit this code via View → Advanced Editor in the Power Query Editor. M is case-sensitive, lazy-evaluated, and functional — each step is an expression that transforms the previous step's output. Understanding M allows you to write transformations that the graphical interface cannot express.

Every M query follows the let ... in structure: let Source = Excel.Workbook(File.Contents("C:\data.xlsx"), null, true), Sheet1 = Source{[Item="Sales",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Revenue", type number}}) in #"Changed Type" Each named step references the previous step. The final step after "in" is what gets loaded. Comments use // for single line.

In the Advanced Editor, add a custom step after your existing steps: #"Added Custom" = Table.AddColumn(#"Previous Step", "Category", each if [Revenue] > 100000 then "High" else if [Revenue] > 50000 then "Medium" else "Low", type text) Table.AddColumn takes: the previous table, the new column name, a function using "each" (shorthand for a row-by-row function), and optionally the column type. The "each" keyword and the underscore (_) or field reference ([Revenue]) pattern is fundamental M syntax.

Power Query Parameters let you create reusable values that queries reference. Home → Manage Parameters → New Parameter → set Name, Type, and Default Value. Use in queries by referencing the parameter name as a variable. Common use: a file path parameter so the same query works on different machines: Source = Excel.Workbook(File.Contents(FilePath), null, true) where FilePath is the parameter. In Power BI Service, parameters can be updated when refreshing — useful for changing the date range or environment (dev/prod database) without editing the query.

A custom function in M is a query that takes parameters and returns a transformed table. Example — a function to load and clean any monthly sales file: (filePath as text) => let Source = Csv.Document(File.Contents(filePath)), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}, {"Revenue", type number}}) in #"Changed Type" Save this as a query named "CleanSalesFile". Then in another query: Table.AddColumn(FileList, "Data", each CleanSalesFile([Path])). This is how the "Combine Files from Folder" feature works internally.