Power Query M in Depth

💡 Key concepts

• M structure:

  let
      Source   = Csv.Document(File.Contents(DataFolder & "milestones.csv"), [Delimiter=",", Encoding=65001]),
      Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
      Typed    = Table.TransformColumnTypes(Promoted, {{"BaselineDate", type date}, {"ForecastDate", type date}, {"ActualDate", type date}}),
      AddSlip  = Table.AddColumn(Typed, "SlipDays", each Duration.Days([ForecastDate] - [BaselineDate]), Int64.Type)
  in
      AddSlip
  

• Values: records [a=1], lists {1,2,3}, tables. Field access with [Column], list access {0}.
• each keyword: shorthand for (_) =>, a function applied to each row.
• Custom functions:

  // fnFiscalYear
  (d as nullable date) as nullable number =>
      if d = null then null else Date.Year(d) + (if Date.Month(d) >= 10 then 1 else 0)
  

  // fnParseMoney: "$1.2M" → 1200000
  (t as nullable text) as nullable number =>
  let
      s    = if t = null then "" else Text.Upper(Text.Remove(t, {"$", ",", " "})),
      mult = if Text.EndsWith(s, "M") then 1e6 else if Text.EndsWith(s, "K") then 1e3 else 1,
      n    = try Number.From(Text.TrimEnd(s, {"M","K"})) otherwise null
  in
      if n = null then null else n * mult
  

• Parameters: DataFolder, ReportingPeriodEnd. They can be changed in the Service without editing queries.
• Staging pattern: stg_ queries (connection only) → dim_ / fact_ queries that load. Keeps logic modular.
• Query folding: With database sources, steps get pushed back to the source as SQL. Keep foldable steps early. (Files don't fold, but the habit matters.)
• Robust headers: Table.RenameColumns(t, renames, MissingField.Ignore). Use Table.SelectColumns(..., MissingField.UseNull) to enforce a schema.
• Errors: try, otherwise, Table.RemoveRowsWithErrors, plus a separate "errors" query for auditing bad rows.

🛠 Hands-on exercise: Build a Date Dimension in M

Output columns: Date, Year, MonthNum, MonthName, MonthStart, FiscalYear, FYMonth (Oct=1), FYQuarter (FYQ1=Oct–Dec), FYLabel ("FY26"), IsWorkday, IsCurrentFY.

1. Create a blank query that generates a list of dates from 2023-10-01 to 2030-09-30 with List.Dates.
2. Convert it to a table and add each column with Table.AddColumn. Use your fnFiscalYear.
3. Add FYQuarter = "Q" & Text.From(Number.RoundUp(FYMonth/3)).
4. Load it as dim_Date.

📦 Mini-project: Schema-Proof Status Ingestion

Rebuild the status_drops/ pipeline in Power BI as: stg_StatusFiles (folder list, filtered to .xlsx, files whose names match the PRG-###_Status_YYYY-MM pattern) → fnCleanStatusFile(content as binary) (custom function: header fix, renames with MissingField.Ignore, RAG mapping, money parsing) → fact_Status. Add SourceFile and ReportMonth (parsed from the file name) columns. Add a qa_StatusErrors query listing rows with unparseable dates or money.