Parameters, Query Dependencies & Intro to M

Everything so far has been achievable through the interface. This lesson is about the structure that makes a twenty-query file comprehensible rather than a maze.

A file with three queries needs no organisation. A file with twenty-five does, and every real project ends up with twenty-five.

Without structure you get: hard-coded paths that break on anyone else's machine, the same cleaning logic copy-pasted into six queries that then drift apart, a flat list where staging queries and output tables are indistinguishable, and no way to tell which query feeds which.

Parameters

Home › Manage Parameters › New Parameter. A named value you reference in queries instead of hard-coding it.

Parameter types

• Any value — free text or number.
• List of values — a dropdown, which prevents typos and documents the valid options.
• Query — the list comes from another query, so it stays current.

Reference, duplicate and group

The staging pattern

The standard structure for a maintainable file:

1. Source queries — connect and do minimal shaping. Load disabled.
2. Staging queries — reference the source, do the cleaning. Load disabled.
3. Output queries — reference staging, do any final shaping. These load to the model.

Right-click any query → Enable load to toggle whether it becomes a table in the model. Turning it off for helpers keeps the Data pane clean and the model small.

Groups

Right-click in the Queries pane → New Group. A conventional set:

• 0 Parameters
• 1 Sources
• 2 Staging
• 3 Model — the queries that actually load
• 4 Data Quality — the check queries from Lesson 3
• 9 Functions

The numeric prefixes force a sensible order, since groups sort alphabetically.

Reading the Advanced Editor

Home › Advanced Editor shows the M behind your Applied Steps. It has always been there; this is the point at which reading it becomes useful.

let
    StepName1 = <expression>,
    StepName2 = <expression using StepName1>,
    StepName3 = <expression using StepName2>
in
    StepName3

• let introduces a list of named steps.
• Each step is Name = expression, separated by commas — no comma after the last one.
• in names the step that is the query's result. Usually the last, and it does not have to be.
• Step names with spaces are written #"Step Name". That is where the hash marks come from.
• M is case-sensitive. Table.SelectRows works; table.selectrows does not.

Custom functions

A function is a query that takes arguments. Two ways to make one:

1 · From a parameter (the easy route)

1. Build a query that does the transformation for one case, using a parameter for the thing that varies.
2. Right-click the query → Create Function.
3. Power Query generates a function taking that parameter as its argument.

This is exactly what Combine Files does behind the scenes (Module 2 Lesson 2) — the same pattern, made explicit.

2 · By hand

// CleanText.pq  -  one definition of "cleaned text" for the whole file
(input as nullable text) as nullable text =>
let
    Trimmed  = Text.Trim(input ?? ""),
    Cleaned  = Text.Clean(Trimmed),
    Collapsed = Text.Replace(Cleaned, "  ", " "),
    Result   = if Collapsed = "" then null else Collapsed
in
    Result

Invoke it in a Custom Column: CleanText([FullName]). Or across several columns at once:

Table.TransformColumns(Source, {
    {"FullName", CleanText, type nullable text},
    {"Email",    CleanText, type nullable text},
    {"Country",  CleanText, type nullable text}
})

A well-organised query file, as it appears in the Queries pane.

📁 0 Parameters
   ⚙ DataFolder            "C:\bwb\data\"        (text)
   ⚙ Environment           PROD                     (list: DEV / TEST / PROD)
   ⚙ StartDate             2024-01-01               (date)

📁 1 Sources                                        [load disabled]
   ▤ src_Sales             raw CSV, headers promoted, nothing else
   ▤ src_Products          raw CSV
   ▤ src_Customers         raw CSV

📁 2 Staging                                        [load disabled]
   ▤ stg_Sales             references src_Sales -> reduce, clean, type
   ▤ stg_Products          references src_Products -> dedupe, clean
   ▤ stg_Customers         references src_Customers -> standardise country
   ▤ map_Country           Enter Data: raw -> standard

📁 3 Model                                          [LOADS TO MODEL]
   ▦ Sales                 references stg_Sales
   ▦ Product               references stg_Products
   ▦ Customer              references stg_Customers
   ▦ Date                  generated calendar

📁 4 Data Quality                                   [LOADS TO MODEL]
   ▦ DQ_Issues             unmapped values, duplicate keys, bad conversions

📁 9 Functions
   ƒ CleanText             (text) => text
   ƒ StandardiseCountry    (text) => text

Query Dependencies

View › Query Dependencies draws a diagram of which query feeds which. On a file like the one above it is immediately readable; on a flat unorganised file it is the only way to work out what is going on.

Any of the course files will do for this lesson — the subject is structure rather than content. Using several at once makes the organisational benefit more obvious.

Columns & data types

Questions this dataset can answer

• Which category generates the most revenue, and is it also the most profitable?
• How did monthly revenue trend across the two years, and where is the seasonal peak?
• Which region has the highest revenue per customer?
• What share of revenue comes from discounted orders, and does discounting improve profit?
• Which products account for the top 20% of revenue?

Two more:

• Put a description on every query. Right-click → Properties → Description. It shows as a tooltip in the pane and is the only documentation anyone will actually read.
• Keep source queries genuinely minimal. Connect and promote headers, nothing more. When a source changes shape, having exactly one thin query to fix is worth a great deal.

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

📁 0 Parameters
   ⚙ DataFolder          text     "\\fileserver\finance\data\"
   ⚙ MinOrderValue       decimal  100        <- was hard-coded in Query1 x3

📁 1 Sources                                              [load disabled]
   ▤ src_SalesFolder     Folder connector, filtered to sales*.csv
   ▤ src_Products        CSV via DataFolder
   ▤ src_Customers       CSV via DataFolder

📁 2 Staging                                              [load disabled]
   ▤ stg_Sales           references src_SalesFolder -> combine, reduce, clean, type
   ▤ stg_Products        references src_Products    -> dedupe, clean
   ▤ stg_Customers       references src_Customers   -> merge map_Country
   ▤ map_Country         Enter Data: raw -> standard

📁 3 Model                                                [LOADS]
   ▦ Sales               references stg_Sales, filtered to >= MinOrderValue
   ▦ Product             references stg_Products
   ▦ Customer            references stg_Customers
   ▦ Date                generated calendar

📁 4 Data Quality                                         [LOADS]
   ▦ DQ_Issues           unmapped countries, duplicate keys, orphaned product keys

📁 9 Functions
   ƒ CleanText           (nullable text) => nullable text

Syllabus topics delivered

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.