Query Parameters

// ── Pattern 1 — Dynamic file path ─────────────────────────────
// Parameter: FolderPath (Text) = "C:\Data\Sales\"
// Parameter: ReportYear  (Whole Number) = 2024

Source = Csv.Document(
    File.Contents(
        FolderPath & "sales_" & Text.From(ReportYear) & ".csv"
    ),
    [Delimiter=",", Encoding=65001]
)
// Loads: C:\Data\Sales\sales_2024.csv
// Change ReportYear to 2023 → loads sales_2023.csv automatically

// ── Pattern 2 — Filter rows by year parameter ──────────────────
// After Changed Type, filter OrderDate to the ReportYear

#"Filtered Year" = Table.SelectRows(
    #"Changed Type",
    each Date.Year([OrderDate]) = ReportYear
)
// Only rows where OrderDate falls in 2024 are loaded into the model

// ── Pattern 3 — Date range parameters ─────────────────────────
// Parameter: StartDate (Date) = #date(2024,1,1)
// Parameter: EndDate   (Date) = #date(2024,12,31)

#"Filtered Range" = Table.SelectRows(
    #"Changed Type",
    each [OrderDate] >= StartDate and [OrderDate] <= EndDate
)

// ── Pattern 4 — Server and database parameters ─────────────────
// Parameter: ServerName (Text) = "prod-sql-01"
// Parameter: DatabaseName (Text) = "SalesDB"

Source = Sql.Database(ServerName, DatabaseName)
// Switch to dev: change ServerName to "dev-sql-01"
// All queries in the file repoint simultaneously

// ── Pattern 5 — Revenue threshold filter ──────────────────────
// Parameter: MinRevenue (Decimal Number) = 100.0

#"Filtered Revenue" = Table.SelectRows(
    #"Changed Type",
    each [Revenue] >= MinRevenue
)

// Pattern — parameter driven by the latest date in the source
// Step 1: Create a query called LatestOrderDate that returns a date scalar

let
    Source    = Orders,
    MaxDate   = List.Max(Source[OrderDate])
in
    MaxDate   // returns a single date value, e.g. #date(2024,2,20)

// Step 2: Create a Date parameter called EndDate
//   In the parameter dialog, set Suggested Values to "Query"
//   and select LatestOrderDate as the source query
//   Current Value is now driven by the query — updates on refresh

// Step 3: Use EndDate in the Calendar table
let
    StartDate = #date(Date.Year(EndDate), 1, 1),
    EndYear   = #date(Date.Year(EndDate), 12, 31),
    DateCount = Duration.Days(EndYear - StartDate) + 1,
    DateList  = List.Dates(StartDate, DateCount, #duration(1,0,0,0))
    // ... rest of calendar build
in
    DateList

// Parameter: FolderPath (Text) = "C:\Data\Production\Sales\"

// Query: AllSalesFiles — loads and appends every CSV in the folder
let
    Source = Folder.Files(FolderPath),

    // Keep only CSV files
    CSVFiles = Table.SelectRows(
        Source,
        each Text.EndsWith([Name], ".csv")
    ),

    // Add a column that parses each file
    AddContent = Table.AddColumn(
        CSVFiles,
        "Data",
        each Csv.Document([Content], [Delimiter=",", Encoding=65001])
    ),

    // Expand all parsed tables into one
    Expanded = Table.ExpandTableColumn(
        AddContent,
        "Data",
        Table.ColumnNames(AddContent{0}[Data])
    )
in
    Expanded

// To switch from Production to UAT:
//   Change FolderPath to "C:\Data\UAT\Sales\"
//   Click Refresh — all files from the UAT folder load instead
// No other M code changes anywhere in the file