Power Query

// Every M query follows let…in
let
    // Each step = named transformation
    Source = Csv.Document(
                File.Contents("data.csv"),
                [Delimiter=",",
                 Encoding=1252]
             ),
    PromotedHeaders =
        Table.PromoteHeaders(Source),
    ChangedTypes =
        Table.TransformColumnTypes(
            PromotedHeaders,
            {{"Date", type date},
             {"Sales", type number}})
in
    ChangedTypes   // last step = output

// Case-sensitive — "text" ≠ "Text"
// Each step references the previous
// Steps separated by commas
// Last step after "in" = query output

// Data types
type text
type number
type date
type datetime
type logical    // true/false
type duration
type any        // untyped
type null

// Comments
// single line
/* multi
   line */

// Primitive values
"hello"          // text
42               // number
3.14             // decimal
true             // logical
null             // null
#date(2026,1,15)  // date literal

// List   — curly braces
{1, 2, 3}
{"a", "b", "c"}

// Record — square brackets
[Name="Alice", Age=30]

// Access record field
myRecord[Name]

// Add custom column
Table.AddColumn(Source,
    "Profit",
    each [Revenue] - [Cost])

// Add conditional column
Table.AddColumn(Source,
    "Tier",
    each if [Sales] > 10000
         then "Gold"
         else if [Sales] > 5000
         then "Silver"
         else "Bronze")

// Transform existing column
Table.TransformColumns(Source,
    {{"Name", Text.Upper},
     {"Sales", each _ * 1.1}})

// Split column by delimiter
Table.SplitColumn(Source,
    "FullName",
    Splitter.SplitTextByDelimiter(
        " ", QuoteStyle.Csv),
    {"First", "Last"})

// Replace value in column
Table.ReplaceValue(Source,
    "N/A", null,
    Replacer.ReplaceText,
    {"Region"})

// Fill down (replace nulls)
Table.FillDown(Source,
    {"Category"})

// Remove duplicates
Table.Distinct(Source)

// Remove blank rows
Table.SelectRows(Source,
    each not List.IsEmpty(
        List.RemoveMatchingItems(
            Record.FieldValues(_),
            {null, ""})))

Table.TransformColumnTypes(
    Source,
    {
        {"OrderDate", type date},
        {"Revenue",   type number},
        {"CustomerID",type text},
        {"IsActive",  type logical}
    }
)

// Common type errors to fix
// Numbers stored as text →
//   change to type number
// Dates as text →
//   change to type date
//   (locale matters!)

// Detect types automatically
Table.TransformColumnTypes(
    Source,
    Table.Schema(Source)
    [[Name],[TypeName]]
)

// Table.NestedJoin(
//   table1, key1, table2, key2,
//   newColName, joinKind)

Merged = Table.NestedJoin(
    Sales, {"ProductID"},
    Products, {"ID"},
    "ProductsTable",
    JoinKind.LeftOuter
),

// Then expand the nested table
Expanded =
    Table.ExpandTableColumn(
        Merged,
        "ProductsTable",
        {"Name", "Category"}
    )

// Append two tables vertically
Table.Combine(
    {Sales2024, Sales2025}
)

// Append multiple tables
Table.Combine(
    {Q1, Q2, Q3, Q4}
)

// Append all files in a folder
// Home → New Source → Folder
// → Combine & Transform
// Power Query auto-creates
// a Sample File + Transform fn

// UI shortcut: Home →
// Append Queries (as new)

// Create via: Home → Manage Parameters
// Use in M code as a variable

// Example: paramServerName = "PROD"

let
    Source = Sql.Database(
        paramServerName,
        paramDatabase
    )
in
    Source

// Use parameter in filter
Table.SelectRows(Source,
    each [Year] = paramYear)

// Dynamic file path
File.Contents(paramFolderPath
    & "data.csv")

// Query folding = steps translated
// to SQL and run on the server
// → much faster for large data

✓ Folds to SQL (fast)
Table.SelectRows     WHERE
Table.SelectColumns  SELECT
Table.Sort           ORDER BY
Table.Group          GROUP BY
Table.NestedJoin     JOIN

✗ Breaks folding (run in PQ)
Table.AddColumn (custom formula)
Table.Buffer    (forces in-memory)
CSV/Excel source (no server)

// Check folding: right-click step
// → View Native Query (greyed = no fold)

✓ Filter & select cols EARLY
  → reduces data loaded

✓ Set types explicitly
  → avoids silent errors

✓ Name steps descriptively
  FilteredToNorth
  RemovedNullRows

✓ Use Connection Only for
  intermediate queries

✓ Disable load for staging
  queries (right-click query
  → Enable Load = off)

✓ Table.Buffer() when a table
  is referenced many times
  → caches in memory