<!-- https://learnsql.cpetoday.com/analyze/power-query-lab -->
# Power Query playground (pqlab)

`pqlab` is a database made for learning and showing off **Power Query**, the data-shaping tool in Excel, Power BI and Fabric. It holds 86 tables and about 142,000 rows, identical on SQL Server and MariaDB, and everything in it is fictional data for a made-up outdoor-gear retailer, Harbor & Pine Outfitters.

It has two layers, and the data is **deliberately imperfect**, the way real extracts are.

| Layer | Tables | What it is for |
|---|---|---|
| `core_*` | 17 | A joinable retail backbone with customers, products, categories, stores, employees, orders, order lines, shipments, returns, inventory, promotions, suppliers, a calendar, a fiscal calendar and reference geography. Mostly clean, with realistic gaps. Use it to build real reports and to practice merges, grouping and custom columns. |
| `lab_*` | 68 plus `lab_catalog` | One table per Power Query skill, each deliberately messy: pasted reports, text in every format, JSON and XML, dates and numbers as text, schema drift, duplicates, null markers, ugly column names. |

`lab_catalog` is an index of every table with its category, level (Beginner, Intermediate or Advanced), techniques and a how-to. The full catalog is at the end of this page.

## Connect from Excel or Power BI

1. In Excel choose **Data > Get Data > From Database > From SQL Server Database**. In Power BI Desktop choose **Get data > SQL Server**.
2. Server: `learnsql.cpetoday.com,1433`. Database: `pqlab`. Choose **Import**.
3. In the Navigator, search for a prefix such as `lab_hdr` or `lab_mrg` to find a family of tables. Pick one table and choose **Transform Data** to open Power Query.

See [Connect Excel and Power BI](/connect) for the sign-in details. The tables are in the `dbo` schema.

## Things to know first

- **Sort by `row_id` first on the spreadsheet-style tables** (`lab_hdr_*`). A SQL source does not guarantee row order, and steps like Remove Top Rows or Use First Row as Headers depend on it. Every lab table has an integer key: `row_id`, or `line_no`.
- **Several tables are all text on purpose.** Setting the types is part of the exercise.
- **Answer keys.** Some tables include the true value so you can check your work: `lab_dt_formats.date_true`, `lab_dt_events_tz.start_utc` and `lab_num_formats.value_true`.
- **`core_*` is a sample.** Orders run from January 2023 to October 2026 (14,000 orders, about $11 million) and are all in US dollars.
- **Known imperfections in `core_*`:** about 45 near-duplicate customers, state spellings that vary (WA, Wash., Washington), tier casing, missing emails, phones, weights and ratings, and a few trailing spaces in product names.
- **Fiscal year.** The retailer's fiscal year starts on February 1. `core_calendar.fiscal_year` is the year in which the fiscal year starts.
- **Odd column names** (spaces, symbols, upper case) exist on purpose in `lab_app_orders_2024`, `lab_piv_budget_matrix` and `lab_err_wide`.

## "I want to practice..."

| Power Query skill | Start with |
|---|---|
| Remove top and bottom rows, use first row as headers | `lab_hdr_title_rows`, `lab_hdr_first_row` |
| Two-level headers, fill right, transpose | `lab_hdr_two_row`, `lab_piv_kpi_rows` |
| Fill down, conditional column on layout | `lab_hdr_blocks`, `lab_hdr_ledger` |
| Stacked records, index and modulo, pivot | `lab_hdr_stacked`, `lab_hdr_alternating` |
| Unpivot columns | `lab_piv_sales_wide`, `lab_piv_survey_wide`, `lab_piv_budget_matrix` |
| Pivot column, with or without aggregation | `lab_piv_sales_long`, `lab_piv_attributes` |
| Split a column (delimiter, position, into rows) | `lab_txt_contacts`, `lab_piv_tags`, `lab_txt_addresses`, `lab_hier_category_paths` |
| Trim, clean, case, replace, extract | `lab_txt_contacts`, `lab_txt_sku_parse`, `lab_txt_names_accents` |
| Add column from examples, merge columns | `lab_txt_sku_parse`, `lab_txt_contacts` |
| Parse JSON, expand records and lists | `lab_json_orders`, `lab_json_product_specs` |
| Parse XML and HTML | `lab_xml_shipments`, `lab_txt_html` |
| Base64 and URL decoding | `lab_txt_encoded` |
| Types with a locale, dates in many formats | `lab_dt_formats`, `lab_num_formats` |
| Time zones and durations | `lab_dt_events_tz` |
| Fill, group by hour, de-duplicate, rolling windows | `lab_dt_sensor` |
| Group by (every aggregation, all rows) and rank | `lab_num_scores`, `core_orders` |
| Append tables whose columns drift | `lab_app_orders_2023` to `_2026`, `lab_app_web_2026_01` to `_05` |
| Merge join kinds and anti joins | `lab_mrg_customers_crm`, `lab_mrg_customers_billing` |
| Fuzzy merge and fuzzy grouping | `lab_mrg_company_variants`, `lab_mrg_company_master` |
| Replace values through a lookup table | `lab_mrg_state_aliases`, `lab_mrg_state_lookup` |
| Merge on keys of different types | `lab_mrg_sales_textkeys` |
| Range and date-effective joins | `lab_mrg_price_history`, `lab_dt_fiscal_periods`, `lab_mrg_commission_tiers` |
| Cross join | `lab_mrg_sizes` with `lab_mrg_colors` |
| Currency conversion (merge and fill down) | `lab_dt_fx_rates` with `lab_app_orders_2025` |
| Remove duplicates and keep the latest | `lab_dup_contacts`, `lab_dup_customer_versions` |
| Parameters and custom functions | `lab_fn_parameters`, `lab_fn_report_requests`, `lab_json_api_pages` |
| Recursive hierarchy and self-merge | `lab_hier_bom`, `core_employees`, `core_categories` |
| Null markers, errors and column profiling | `lab_err_null_markers`, `lab_err_mixed_types`, `lab_err_impossible_values` |
| Rename, choose and reorder columns | `lab_err_wide` |
| Standardize categories | `lab_err_category_variants` |
| Query folding on larger tables | `core_orders`, `core_order_lines`, `core_inventory` |
| Create a custom data type (Excel) | `lab_type_trails`, `core_products`, `core_customers`, `core_employees`, `core_stores` |
| Parse coordinates written many ways | `lab_geo_coordinates_text` |
| Geocode messy addresses by merging to a reference table | `lab_geo_addresses_to_geocode`, `core_geo_postal_codes` |
| Distances between points, GPS breadcrumbs | `lab_geo_route_points`, `lab_geo_distance_pairs` |
| Fiscal years, 4-5-4 retail weeks | `core_fiscal_calendar` |

## Recipes

These are starting points in M, the Power Query language. Paste one into **Home > Advanced Editor** in a blank query. In each recipe, `Source` points at the table named in its `Item`.

### Pasted report: remove the title block, promote headers, drop footers

```m
let
    Source   = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_hdr_title_rows"]}[Data],
    Sorted   = Table.Sort(Source, {{"line_no", Order.Ascending}}),
    Dropped  = Table.RemoveColumns(Sorted, {"line_no"}),
    Skipped  = Table.Skip(Dropped, 4),
    Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
    NoRepeat = Table.SelectRows(Promoted, each [Product] <> null and [Product] <> "Product" and not Text.StartsWith([Product], "Page ")),
    NoFooter = Table.SelectRows(NoRepeat, each [Product] <> "Total" and not Text.StartsWith([Product], "--"))
in
    NoFooter
```

> **Watch out.** The report repeats its header at every page break and ends with a total row and an end-of-report line, so filtering is needed after promoting the headers.

Then remove the `$` and commas and set the types.

### Twelve month columns: unpivot

```m
let
    Source    = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_piv_sales_wide"]}[Data],
    Trimmed   = Table.RemoveColumns(Source, {"row_id", "ytd_total"}),
    Unpivoted = Table.UnpivotOtherColumns(Trimmed, {"product_id", "product_name", "region", "sales_year"}, "month", "sales"),
    NoNulls   = Table.SelectRows(Unpivoted, each [sales] <> null)
in
    NoNulls
```

Remove `ytd_total` first, or it would unpivot as if it were a month.

### Budget and actual in the column names: unpivot, split, pivot back

```m
let
    Source    = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_piv_budget_matrix"]}[Data],
    Trimmed   = Table.RemoveColumns(Source, {"row_id"}),
    Unpivoted = Table.UnpivotOtherColumns(Trimmed, {"department"}, "col", "amount"),
    Split     = Table.SplitColumn(Unpivoted, "col", Splitter.SplitTextByDelimiter(" ", QuoteStyle.None), {"month", "measure"}),
    Pivoted   = Table.Pivot(Split, List.Distinct(Split[measure]), "measure", "amount")
in
    Pivoted
```

### JSON documents: parse safely and expand

```m
let
    Source   = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_json_orders"]}[Data],
    Parsed   = Table.AddColumn(Source, "doc", each try Json.Document([payload]) otherwise null),
    Valid    = Table.SelectRows(Parsed, each [doc] <> null),
    WithItem = Table.AddColumn(Valid, "items", each [doc][items]),
    Expanded = Table.ExpandListColumn(WithItem, "items"),
    Lines    = Table.ExpandRecordColumn(Expanded, "items", {"sku", "qty", "unit_price"}, {"sku", "qty", "unit_price"})
in
    Lines
```

About 1% of the documents are broken on purpose, so the `try ... otherwise` keeps the query from failing. Compare the rows you keep against the 1,500 you started with.

### Dates in many formats: type with a locale

```m
let
    Source  = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_dt_formats"]}[Data],
    Us      = Table.TransformColumnTypes(Source, {{"us_text", type date}}, "en-US"),
    Eu      = Table.TransformColumnTypes(Us, {{"eu_text", type date}}, "en-GB"),
    Serial  = Table.AddColumn(Eu, "from_serial", each try Date.From(Number.FromText([serial_text])) otherwise null, type date),
    Epoch   = Table.AddColumn(Serial, "from_epoch", each try #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Number.FromText([epoch_text])) otherwise null, type datetime)
in
    Epoch
```

Compare each result with `date_true`. The same text, `03/04/2026`, is March 4 in `en-US` and April 3 in `en-GB`, which is why the locale matters.

### Append many same-shaped tables

```m
let
    Source   = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"),
    Parts    = Table.SelectRows(Source, each Text.StartsWith([Item], "lab_app_web_2026_")),
    Tidy     = Table.AddColumn(Parts, "T", each Table.TransformColumnNames([Data], each Text.Replace(Text.Lower(_), " ", "_"))),
    Combined = Table.Combine(Tidy[T])
in
    Combined
```

The five monthly tables differ on purpose: one has an extra column, one has spaced column names and one stores the total as text. Fix the totals after combining.

### Fuzzy merge company names to a master list

```m
let
    Pqlab    = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"),
    Variants = Pqlab{[Schema = "dbo", Item = "lab_mrg_company_variants"]}[Data],
    Master   = Pqlab{[Schema = "dbo", Item = "lab_mrg_company_master"]}[Data],
    Joined   = Table.FuzzyNestedJoin(Variants, {"company_raw"}, Master, {"company_name"}, "match", JoinKind.LeftOuter,
                   [IgnoreCase = true, IgnoreSpace = true, Threshold = 0.8]),
    Result   = Table.ExpandTableColumn(Joined, "match", {"company_id", "company_name"})
in
    Result
```

Forty variants belong to no master company. They are the ones that stay unmatched at a high threshold.

### A custom function over a list of API pages

First create a query named `fnPage`:

```m
(page as number) as table =>
let
    Pages = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_json_api_pages"]}[Data],
    Row   = Table.SelectRows(Pages, each [page_number] = page){0},
    Doc   = Json.Document(Row[payload]),
    Items = Table.FromList(Doc[results], Splitter.SplitByNothing(), {"item"})
in
    Items
```

Then, in a second query, invoke it for every page:

```m
let
    Pages  = List.Numbers(1, 40),
    AsTbl  = Table.FromList(Pages, Splitter.SplitByNothing(), {"page"}),
    Called = Table.AddColumn(AsTbl, "data", each fnPage([page])),
    All    = Table.ExpandTableColumn(Called, "data", {"item"})
in
    All
```

### Coordinates in nine text formats: classify, then parse

```m
let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"){[Schema = "dbo", Item = "lab_geo_coordinates_text"]}[Data],
    Clean  = Table.TransformColumns(Source, {{"coordinate_text", each Text.Trim(Text.Replace(Text.Replace(_, "(", ""), ")", "")), type text}}),
    Kind   = Table.AddColumn(Clean, "style", each
                if Text.Contains([coordinate_text], "°") then "dms"
                else if Text.Contains([coordinate_text], ";") then "european"
                else if Text.StartsWith([coordinate_text], "N ") or Text.StartsWith([coordinate_text], "S ") then "ddm"
                else if Text.Contains([coordinate_text], "N,") or Text.Contains([coordinate_text], "S,") then "hemisphere"
                else "decimal", type text)
in
    Kind
```

Parse each style on its own (split on the delimiter, replace the decimal comma, convert degrees-minutes-seconds with a small function), then compare with `lat_true` and `lon_true`. Some "decimal" rows have longitude first: a latitude must be between -90 and 90, so a value beyond that means the pair is swapped.

### Geocode an address by merging to a reference table

```m
let
    Pqlab   = Sql.Database("learnsql.cpetoday.com,1433", "pqlab"),
    Addr    = Pqlab{[Schema = "dbo", Item = "lab_geo_addresses_to_geocode"]}[Data],
    Zips    = Pqlab{[Schema = "dbo", Item = "core_geo_postal_codes"]}[Data],
    WithZip = Table.AddColumn(Addr, "zip", each try Text.Select(Text.End([raw_address], 6), {"0".."9"}) otherwise null, type text),
    Joined  = Table.NestedJoin(WithZip, {"zip"}, Zips, {"postal_code"}, "geo", JoinKind.LeftOuter),
    Result  = Table.ExpandTableColumn(Joined, "geo", {"latitude", "longitude"}, {"lat_found", "lon_found"})
in
    Result
```

Rows in the "no zip" style have no postal code, so they stay unmatched; fall back to a merge on city and state with `core_geo_places`. Measure how far each guess is from `lat_true` and `lon_true`.

### Create a custom data type (Excel)

In the Power Query editor, load `lab_type_trails` (or `core_products`, `core_employees`, `core_stores`), select the columns that should become fields, choose **Transform > Create Data Type**, and pick a display column such as `trail_name`. After you load it to a worksheet, you can read a field with a formula such as `=A2.Rating`.

## Full catalog

The same list is in the `lab_catalog` table, which you can query and shape in Power Query. Each row says what the table is and which techniques it teaches.

### Core backbone
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `core_categories` | 58 | Beginner | Self-referencing hierarchy (parent_category_id); Merge to self; Split path; Fill down levels | Three-level product category tree. |
| `core_suppliers` | 25 | Beginner | Merge; Group by; Filter; Conditional column | Supplier list with lead times and quality ratings. |
| `core_promotions` | 24 | Beginner | Merge; Date range filter; Conditional column | Promotion codes with discount and date window. |
| `core_products` | 220 | Beginner | Create data type; Merge to categories; Add custom column (margin); Conditional column; Image URLs; Dates | 220 products with cost, price, launch/discontinue dates, image and page URLs. A few trailing spaces and missing weights/ratings. |
| `core_stores` | 9 | Beginner | Create data type (location); Merge; Latitude/longitude; Filter nulls | Nine locations including an online store (no address) and a distribution center. |
| `core_employees` | 66 | Intermediate | Self-merge for manager name; Hierarchy flatten; Create data type; Date arithmetic (tenure); Conditional column | Staff with manager_id, store, hire/termination dates and pay type. |
| `core_customers` | 1,545 | Intermediate | Remove duplicates (~45 near-duplicate customers); Clean state variants; Trim/Case; Null handling; Age from birth_date; Group by tier | 1,500 customers. Mostly clean, but ~3% state spellings vary, tier casing varies, some emails/phones are missing and ~45 customers are duplicates. |
| `core_orders` | 14,000 | Beginner | Merge to customers/stores/employees; Group by month/channel; Date parts; Conditional column; Query folding on a 14k-row table | 14,000 orders from 2023-01 to 2026-10 with channel, status, payment method, promo and totals (all USD). |
| `core_order_lines` | 27,933 | Beginner | Merge to products; Group by order; Custom column (net price); Large-table folding; Index/rank within order | About 36,000 order lines with quantity, unit price and discount. |
| `core_shipments` | 5,774 | Beginner | Merge; Date difference (ship days); Null delivered_date handling; Conditional column | Shipments for web and phone orders with carrier, dates and status. |
| `core_returns` | 856 | Beginner | Merge to lines/orders/products; Group by reason; Percent of total | Returns with reason, condition and refund amount. |
| `core_inventory` | 10,575 | Intermediate | Unpivot/pivot by snapshot date; Group by; Merge to products and stores; Filter latest snapshot | Quarter-end inventory snapshots by store and product. |
| `core_calendar` | 2,191 | Beginner | Date dimension; Merge on date; Fiscal year (starts Feb 1) columns; Holiday flags | Calendar 2022-2027 with fiscal year, quarter, month and holiday names. |
| `core_fiscal_calendar` | 2,191 | Intermediate | Fiscal year, quarter and period; 4-5-4 retail weeks; Merge on date; Map by fiscal period; Seasons | Daily calendar with the February-start fiscal year (named by the year it starts), fiscal weeks, a true 4-5-4 retail calendar and shopping seasons. |

### Headers & layout
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_hdr_title_rows` | 197 | Beginner | Remove top rows; Use first row as headers; Remove bottom rows; Remove blank rows; Filter out repeated header rows; Change type; Replace values ($ and commas); Remove columns | A pasted report export: title block, blank lines, repeated page headers, a total row and an end-of-report footer. Everything is text. |
| `lab_hdr_first_row` | 301 | Beginner | Use first row as headers; Duplicate column names (Amount.1); Rename columns; Trim; Change type; Split column | The classic: the real headers are the first data row. Includes a duplicate header, stray spaces and mixed case. |
| `lab_hdr_two_row` | 10 | Intermediate | Fill right (year across quarters); Transpose; Merge columns (Year-Quarter); Use first row as headers; Unpivot other columns; Split column by delimiter | A crosstab whose header is two rows: years on top (only in the first column of each group) and quarters below. |
| `lab_hdr_blocks` | 129 | Intermediate | Add conditional column; Fill down; Filter rows; Remove blank rows; Remove duplicates (repeated headers); Extract text after delimiter | A grouped report: each store has a title line, its own header line, detail rows and a subtotal, all in the same columns. |
| `lab_hdr_ledger` | 280 | Intermediate | Fill down (dates); Use first row as headers; Parentheses negatives; Merge Debit/Credit into one signed Amount; Replace nulls with 0; Change type with locale | A general-ledger style export: the date appears only on the first line of each day, debits and credits are in separate text columns, balances use parentheses for negatives. |
| `lab_hdr_stacked` | 745 | Advanced | Add index column; Conditional column (record number); Split column by delimiter; Fill down; Pivot column (no aggregation); Remove blank rows | One text column holding 'Label: value' lines, one blank line between records. Records have different fields. |
| `lab_hdr_alternating` | 200 | Intermediate | Add index column; Modulo / Integer-divide index; Pivot column; Keep/Remove alternate rows; Merge queries (odd with even) | Records stacked on alternating lines: name, then phone, then the next name, and so on. |

### Pivot & unpivot
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_piv_sales_wide` | 1,920 | Beginner | Unpivot columns; Unpivot other columns; Rename attribute to Month; Replace nulls; Sort; Merge with calendar; Remove total column | Twelve month columns per product, region and year (2026 is year-to-date, so late months are null). A ready-made unpivot exercise. |
| `lab_piv_sales_long` | 4,719 | Beginner | Pivot column (Sum / Don't aggregate); Group by; Add column from examples (quarter); Merge columns; Percent of total | Tall, tidy data: category x region x channel x month. Good for building a matrix (months across, categories down). |
| `lab_piv_attributes` | 1,083 | Intermediate | Pivot column (Don't aggregate); Replace values (Colour/color); Clean names first; Change type per column; Conditional columns | Entity-attribute-value: one row per product attribute, with inconsistent attribute names (Color / Colour / color). |
| `lab_piv_kpi_rows` | 12 | Intermediate | Transpose; Use first row as headers; Unpivot columns; Replace values (n/a, TBD); Change type with mixed formats; Add custom column | A finance-style scorecard: metrics down the side, fiscal years across the top, values in mixed text formats ($41.2M, 41,200,000, 44.3%). |
| `lab_piv_survey_wide` | 800 | Intermediate | Unpivot other columns; Split column by delimiter (q9_hiking -> hiking); Replace values (Likert to 1-5); Count rows / Group by; Pivot to cross-tab | Survey export with Likert text answers (q1-q8) and one Yes/blank column per checkbox option (q9_*). |
| `lab_piv_tags` | 1,545 | Beginner | Split column by delimiter into rows; Trim; Clean; Capitalize each word; Remove duplicates; Group by (count); Pivot | Multi-value columns: interests separated by ';' (sometimes '; ' or ','), purchase categories by ', '. Casing is inconsistent. |
| `lab_piv_budget_matrix` | 15 | Advanced | Unpivot other columns; Split column by delimiter (Month / Measure); Pivot column back to Budget and Actual; Add column (variance); Date from month name | Budget and actual side by side for every month: 24 columns named like 'Jan Budget' and 'Jan Actual'. |

### Text
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_txt_contacts` | 1,200 | Beginner | Trim; Clean (tabs); Replace non-breaking spaces; Split column by delimiter (name, address); Proper case; Extract (first/last chars, length); Replace values; Add column from examples (area code); Pad zip codes with zeros; Filter invalid emails | A messy contact list: seven name styles, ten phone formats, mixed-case emails with spaces and typos, one-string addresses, state spellings, lost leading zeros on ZIPs, tabs and non-breaking spaces. |
| `lab_txt_sku_parse` | 800 | Beginner | Split column by delimiter (SKU parts); Extract text between delimiters; Text before/after delimiter; First/last N characters; Length; Upper/Lower/Proper; Column from examples; Sort (natural order trap: Item 10 before Item 2) | Coded identifiers to take apart: SKUs with category, sequence, size and color segments (plus lowercase, underscore and padded variants), 'size / color' strings, titles in assorted casing. |
| `lab_txt_html` | 120 | Advanced | Html.Table with CSS selectors; Text.Replace of entities; Extract text between delimiters; Split rows from <li>; Parse HTML table into rows | Product-detail HTML fragments: a name, price span, description with <br/>, a feature list, a specs table and a link, with &amp; entities. |
| `lab_txt_logs` | 5,000 | Advanced | Split column by delimiter; Extract between delimiters; Json.Document on JSON lines; Conditional parsing (try ... otherwise); Parse key=value pairs; Datetime.FromText; Filter rows (contains); Group by hour/level | Five thousand application log lines in four different formats: key=value, JSON lines, syslog style and free text. |
| `lab_txt_addresses` | 600 | Intermediate | Split column by delimiter (comma, pipe, line feed); Split by number of characters; Extract text after last delimiter; Replace line feeds; Conditional column to detect PO boxes; Trim; Standardize country | Free-form address blobs: line-feed separated, comma separated, pipe separated, PO boxes and ALL CAPS, with five spellings of USA. |
| `lab_txt_encoded` | 300 | Advanced | Binary.FromText (Base64); Text.FromBinary; Uri.Parts / Uri.BuildQueryString; Split query string by & then =; Text.Replace for HTML entities; Percent-decoding | Encoded text to decode: Base64 notes, URL query strings with %20, URLs with parameters and HTML-encoded strings. |
| `lab_txt_names_accents` | 400 | Intermediate | Proper-case traps (McDonald, O'Neil, van der Berg); Text.Normalize / remove diacritics via Replace; Split on spaces and hyphens; Trim; Detect non-ASCII with Text.Select; Unicode data types | International names with accents, apostrophes, hyphens, particles (van der, de la) and mixed casing. |

### JSON & XML
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_json_orders` | 1,500 | Advanced | Json.Document; Expand record columns; Expand list to rows; Nested expand (items > attributes); Optional fields (MissingField.UseNull); try ... otherwise for malformed JSON; Type conversion after expand; Column from record | Order documents as an API would deliver them: nested customer (with optional loyalty), an items array with nested attributes, shipping that is null for store sales, a few missing keys, string-typed quantities and about 1% malformed JSON. |
| `lab_json_product_specs` | 220 | Intermediate | Json.Document; Expand record with different fields per row; Record.FieldNames to discover columns; Expand list to rows; Combine lists (Text.Combine); Dynamic column expansion | One JSON object per product whose keys differ by category (tents have capacity and dimensions, boots have sizes and drop, bags have temperature rating). |
| `lab_xml_shipments` | 400 | Advanced | Xml.Tables / Xml.Document; Expand tables with namespaces; Attributes vs elements; Expand nested tables (packages > items); Combine events; Type conversion | Shipment XML with a namespace, attributes (weight_kg, qty), repeating packages and events. |
| `lab_json_api_pages` | 40 | Advanced | Custom function over a list of pages; Invoke custom function; List.Generate / List.Accumulate for pagination; Expand list; next_url loop stop condition; Combine results | Forty pages of a paginated API response. Each row holds one page's JSON, its URL and the next URL (null on the last page). |

### Dates & times
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_dt_formats` | 600 | Beginner | Change type using locale; Date.FromText; Excel serial to date (Date.From of number); Unix epoch to datetime; DateTimeZone.FromText; Time and Duration parsing; Extract year/quarter/month/day/week/day name; Age and Duration.Days; Replace errors | The same date in eleven text formats (ISO, US, EU, long, short, compact, Excel serial, Unix epoch, ISO with offset), plus time and duration strings. date_true is the answer key. |
| `lab_dt_events_tz` | 800 | Advanced | DateTimeZone.FromText; DateTimeZone.SwitchZone / ToLocal; Parse offsets; Duration between start and end; Time zone abbreviations (PT, ET) as text; Datetime to Date and Time; Duration.TotalMinutes | Event start and end times written four different ways (local with a zone abbreviation, ISO with offset, UTC 'Z', US 12-hour) with an IANA zone name and the true UTC start as the answer key. |
| `lab_dt_sensor` | 6,604 | Intermediate | Group by (device, date / hour); Fill down / fill up; Remove duplicates; Replace values (-999 sentinel); Replace errors; Sort; Index column for rolling windows; Date/Time split; Detect gaps; Percent-style text to number | Fifteen-minute readings from five sensors over 14 days with gaps, duplicate rows, out-of-order delivery, sentinel values (-999), blanks, and humidity stored as text ('45%', '45.2', '--'). |
| `lab_dt_fx_rates` | 3,891 | Intermediate | Merge queries on two keys (date + currency); Fill down for weekends/holidays; Generate a date list (List.Dates) and left-join; Pivot currency to columns; Custom function for latest-rate lookup | Daily FX rates (USD per unit) for CAD, EUR, GBP and JPY. Weekdays only, plus a few random missing days, so a naive join leaves weekend orders without a rate. |
| `lab_dt_fiscal_periods` | 72 | Intermediate | Merge on a date range (custom column filtering period_start <= date <= period_end); Table.SelectRows inside a custom column; Expand; Cross join; Date arithmetic | Fiscal periods (the fiscal year starts in February) with start and end dates, for range-based lookups. |

### Numbers
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_num_formats` | 800 | Intermediate | Change type using locale (en-US / de-DE / fr-FR); Replace values ($ , parentheses, trailing minus, currency words); Percent text to number; Abbreviations (K/M/B) via conditional column; Scientific notation text; Number.FromText with culture; Replace errors; Round / Number.Mod | Money, quantities and percentages written every way a spreadsheet or a European colleague might: $1,234.50, 1.234,50 €, (1,234.50), 1,234.50-, 1.2K, 12%, 8.50E+11 UPCs. value_true is the answer key. |
| `lab_num_scores` | 560 | Beginner | Group by (sum, average, median, std dev, count distinct, all rows); Rank / Index within group; Percent of total; Add column (attainment %); Number bins via conditional column; Statistics (Transform > Statistics); Running total | Sales reps by quarter with sales, quota, calls and deals. Built for aggregation, ranking, attainment %, and percentile or bucket work. |

### Append
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_app_orders_2023` | 3,391 | Beginner | Append queries; Rename columns to match; Change type; Table.Combine across a list of tables | Order summary for 2023: the oldest layout (cust_id, total). |
| `lab_app_orders_2024` | 3,550 | Intermediate | Append with mismatched column names; Rename; Dates and money stored as text; Change type with locale | 2024 orders: column names with spaces and camel case (Order ID, CustomerID), dates as MM/DD/YYYY text, totals as '$1,234.50' text. |
| `lab_app_orders_2025` | 3,941 | Intermediate | Append; Added columns (currency); Currency conversion with lab_dt_fx_rates; Merge on date + currency | 2025 orders with a currency column; about 6% of rows are billed in CAD or EUR and the amount is in that currency. |
| `lab_app_orders_2026` | 3,118 | Intermediate | Append with uppercase names and different column order; Reorder columns; Rename; Table.Combine with MissingField handling | 2026 year-to-date orders: UPPER_CASE column names, columns in a different order and an extra STATUS column. |
| `lab_app_web_2026_01` | 247 | Intermediate | Navigation table filter on name (Text.StartsWith); Table.Combine over many tables; Custom function to normalize each table; Missing/extra columns; Rename drift; Type drift | Web-store orders for 2026-01. One of several same-shaped monthly tables. |
| `lab_app_web_2026_02` | 281 | Intermediate | Navigation table filter on name (Text.StartsWith); Table.Combine over many tables; Custom function to normalize each table; Missing/extra columns; Rename drift; Type drift | Web-store orders for 2026-02. One of several same-shaped monthly tables. |
| `lab_app_web_2026_03` | 236 | Advanced | Navigation table filter on name (Text.StartsWith); Table.Combine over many tables; Custom function to normalize each table; Missing/extra columns; Rename drift; Type drift | Web-store orders for 2026-03. One of several same-shaped monthly tables (this one has an extra coupon_code column). |
| `lab_app_web_2026_04` | 274 | Advanced | Navigation table filter on name (Text.StartsWith); Table.Combine over many tables; Custom function to normalize each table; Missing/extra columns; Rename drift; Type drift | Web-store orders for 2026-04. One of several same-shaped monthly tables (this one has spaced, title-case column names). |
| `lab_app_web_2026_05` | 324 | Advanced | Navigation table filter on name (Text.StartsWith); Table.Combine over many tables; Custom function to normalize each table; Missing/extra columns; Rename drift; Type drift | Web-store orders for 2026-05. One of several same-shaped monthly tables (this one stores order_total as text). |

### Merge & join
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_mrg_customers_crm` | 800 | Intermediate | Merge queries (Inner, Left outer, Right outer, Full outer, Left anti, Right anti); Merge on email; Trim/lowercase keys before merging; Expand merged column | A CRM customer list (800 customers). About 560 also exist in lab_mrg_customers_billing; 240 CRM customers have no billing account. |
| `lab_mrg_customers_billing` | 760 | Intermediate | Fuzzy merge (similarity threshold, transformation table); Merge on cleaned email; Right anti; Full outer to find mismatches; Group by to find duplicates | A billing system's accounts: names in different styles with occasional typos, emails in mixed case or with spaces, some changed emails. 200 accounts are not in the CRM. |
| `lab_mrg_company_variants` | 502 | Advanced | Fuzzy grouping (Group By with fuzzy matching); Fuzzy merge to master list; Transformation table; Similarity threshold; Trim/Clean/Case before fuzzy; Remove duplicates | About 500 company names that are really ~120 companies: typos, swapped letters, suffix variants (Inc / Incorporated), casing and extra spaces. Forty don't belong to any master company. |
| `lab_mrg_company_master` | 120 | Intermediate | Fuzzy merge target; Lookup table; Merge and expand; Left outer join to find unmatched | The clean list of 120 companies to match the variants against. |
| `lab_mrg_state_aliases` | 268 | Intermediate | Replace values using a lookup table (Merge instead of 40 Replace steps); Trim; Case-insensitive merge; List.ReplaceMatchingItems; Fix state spelling variants | Every spelling of a state seen in the wild (WA, wa, Washington, Wash., Washinton) mapped to its two-letter code. |
| `lab_mrg_state_lookup` | 50 | Beginner | Merge (left outer) to add state name and region; Lookup table; Expand; Group by region | Fifty states with region and approximate sales tax rate. |
| `lab_mrg_sales_textkeys` | 2,000 | Intermediate | Merge on keys of different types (text with leading zeros vs integer); Text.PadStart / Number.FromText; Extract number from 'C-00123'; Left anti join to find orphan keys; Date from compact text | Sales whose keys are text: product_key '0042' and customer_key 'C-00123'. About 5% point at products that don't exist; some keys are null. |
| `lab_mrg_price_history` | 782 | Advanced | Range join (date between effective_from and effective_to); Custom column with Table.SelectRows; Open-ended ranges (null effective_to); Merge then filter; Slowly changing dimension | Price changes per product with effective_from / effective_to. The current price has a null end date. |
| `lab_mrg_commission_tiers` | 5 | Intermediate | Approximate (range) lookup; Cross join then filter; Custom column over a table; Conditional column alternative; Last-row lookup with List.Max | Commission tiers with minimum and maximum sales (open-ended top tier). |
| `lab_mrg_sizes` | 7 | Beginner | Cross join (Custom column holding another table, then expand); Generate all combinations; Sort order lookup; Merge | Seven apparel sizes with a sort order. |
| `lab_mrg_colors` | 10 | Beginner | Cross join; Merge; Add custom column from hex code; Data type color text | Ten product colors with codes and hex values. |

### Duplicates
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_dup_contacts` | 900 | Beginner | Remove duplicates (exact vs after cleaning); Keep duplicates; Trim/Lowercase before dedupe; Group by (count) to find duplicates; Sort then Remove Duplicates (keep newest); Table.Buffer | 900 contact rows for 600 people: 300 duplicates that differ only by email casing/spacing, first-name casing or initials, source and created date. |
| `lab_dup_customer_versions` | 3,340 | Intermediate | Group by customer with Max(updated_at) then merge back; Sort descending + Remove Duplicates on customer_id (keep latest, with Table.Buffer); Rank within group; Index; Keep top N rows per group (all rows); Compare previous row | Up to five versions per customer (history of changes), delivered in random order. Pick the current record, or compare consecutive versions. |

### Functions & parameters
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_fn_parameters` | 12 | Intermediate | Parameters table pattern; Drill down to a single value (Table.SelectRows then {0}[param_value]); Convert text to typed parameter; Reference queries; Dynamic filter values | Report settings stored as name / value / type rows. Drive your queries from here instead of hard-coding filters. |
| `lab_fn_report_requests` | 40 | Advanced | Create a custom function; Invoke custom function per row; Pass columns as parameters; Group and combine results; Table.AddColumn with function call; Error handling per row | A work list of report requests (store, month, report type). Run a function once per row to build each report. |

### Hierarchy
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_hier_bom` | 33 | Advanced | Recursive expansion with a custom function or List.Generate; Self-merge on parent/child; Flatten to levels; Table.Buffer; Quantity roll-up (multiply down the tree); Detect leaves vs assemblies | A bill of materials: kits contain sub-assemblies, which contain products (up to three levels). Component can itself be an assembly. |
| `lab_hier_category_paths` | 58 | Beginner | Split column by delimiter into columns (uneven depth); Extract last level; Count levels (Text.Split + List.Count); Fill down parent levels; Trim; Handle '>' with and without spaces | Category breadcrumbs ('Camping > Tents > Backpacking Tents') of different depths, some written without spaces around the arrow. |

### Errors & nulls
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_err_null_markers` | 1,000 | Beginner | Replace values (many null markers to null); Replace errors; Remove errors; Remove empty; Column quality / distribution / profile; Fill down/up; Change type with error handling; Keep rows with errors | Thirteen different ways of saying 'no value' (NULL, n/a, #N/A, '-', blanks) across eight columns, plus junk in numeric and date columns ('ERR', '#DIV/0!', 0000-00-00). |
| `lab_err_mixed_types` | 800 | Intermediate | Change type errors; Conditional column to detect type (try Number.From otherwise ...); Replace values for booleans (Y/yes/TRUE/1); Remove errors; Number.From with culture; Detect with Value.Is | One column holding integers, decimals, words, dates, booleans, blanks and thousands-separated numbers; plus boolean and integer columns in many spellings. |
| `lab_err_wide` | 300 | Beginner | Choose columns; Remove other columns; Rename columns (clean names, remove symbols); Reorder columns; Table.TransformColumnNames; Duplicate column; Data profiling on wide tables; Promote/demote headers | A 40-column extract with ugly column names (double spaces, trailing spaces, #, $, /, parentheses, dashes) to practice column management. |
| `lab_err_impossible_values` | 600 | Intermediate | Filter rows (between, greater than, equals); Replace values with null; Conditional column for flags; Keep rows / Remove rows; Date sentinel handling; Statistics for outliers; Group by to count problems; Add column (is_valid) | Sales rows with logically impossible values: negative or huge quantities, zero and negative prices, discounts over 100%, ship dates before order dates, sentinel dates (1900, 2099, 9999). |
| `lab_err_category_variants` | 1,000 | Intermediate | Replace values; Trim/Clean/Capitalize Each Word; Group by to find distinct spellings; Fuzzy grouping; Merge to a mapping table; Conditional columns; List.Distinct; Column distribution | Five categorical columns each spelled several ways (casing, spaces, typos, abbreviations like CC/Visa). |

### Geospatial
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `core_geo_states` | 51 | Beginner | Merge to customers and stores; Map by state; Region grouping; Filled map | The 50 states and DC with FIPS codes, Census region and division, centroid coordinates and capital. |
| `core_geo_places` | 29 | Beginner | Merge on city and state; Bubble map; County and metro rollups; Time zones | Cities used in the data with county, centroid coordinates, population, time zone and metro area. |
| `core_geo_postal_codes` | 895 | Intermediate | Merge on postal code; Postal-centroid maps; Fix leading zeros first | A centroid for every postal code in core_customers and core_stores. The codes are fictitious (a real city prefix plus random digits), so centroids are synthetic. |
| `lab_geo_coordinates_text` | 600 | Intermediate | Split column by delimiter; Parse degrees-minutes-seconds with a custom function; Detect swapped latitude/longitude with a range check; Replace decimal commas; Trim; Extract numbers; Conditional column by format | Coordinates written nine ways (decimal, swapped, DMS, degrees-decimal-minutes, with N/W letters, European commas, padded, in parentheses). lat_true and lon_true are the answer key. |
| `lab_geo_addresses_to_geocode` | 800 | Intermediate | Standardize addresses (case, abbreviations, suites); Split into street, city, state, ZIP; Merge to core_geo_postal_codes or core_geo_places to geocode; Compare to the answer key; Fuzzy merge on street with typos | Eight hundred customer addresses in six messy styles (lowercase, spelled-out, no ZIP, suite added, typo), each with the true coordinates and quality. |
| `lab_geo_route_points` | 6,989 | Advanced | Sort by route and sequence; Index within group; Distance between consecutive points with a custom function (haversine); Remove duplicates; Detect GPS glitches (impossible jumps); Group by route (total miles, duration, stops); Fill gaps; Lines on a map | GPS breadcrumbs for 40 delivery routes from the Kent distribution center, with dropped points, duplicate timestamps and a few GPS glitches. Stops carry the customer served. |
| `lab_geo_distance_pairs` | 2,100 | Intermediate | Cross join stores and customers; Group by and keep the minimum (nearest store); Rank within group; Distance bands via conditional column; Catchment analysis; Lines from origin to destination on a map | Every physical store paired with 300 customers: straight-line miles, estimated drive miles and minutes, and a flag for each customer's nearest store. |

### Custom data types
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_type_trails` | 120 | Intermediate | Create data type (Excel: Transform > Create Data Type); Record columns; Display column choice; Linked data type fields (latitude/longitude, url, image); Table.ToRecords; Add column from record fields | Trailheads with name, park, location, ratings, URL and image: a natural 'card' entity for an Excel data type. |

### Index
| Table | Rows | Level | Techniques | What it is |
|---|---:|---|---|---|
| `lab_catalog` | 86 | Beginner | Split column by delimiter into rows (techniques); Group by category; Filter by difficulty; Count distinct techniques | Index of all tables: category, difficulty, techniques (';' separated), description, row count and how to use each table. |
