Downloads

Power Query playground (pqlab)

86 tables built for practicing Power Query: a retail backbone plus deliberately messy tables (now with geospatial exercises), a technique index and M recipes.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Practice Power Query on 86 tables, several of them deliberately messy.
  • Work through the technique index: pivots, JSON, dates, fuzzy merges and geospatial data.
  • Apply the M recipes to the tables that need them.
  • Use the catalog to find the exercise for each technique.

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 for the sign-in details. The tables are in the dbo schema.

Things to know first

"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

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

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

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

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

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

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

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:

(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:

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

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

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
  • ; Parse HTML table into rows
  • Product-detail HTML fragments: a name, price span, description with
    , a feature list, a specs table and a link, with & 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.

    Try it on the data

    Open the pqlab database page, browse it in the Playground, write your own queries in the SQL editor, see how the tables connect in the schema explorer, or ask an AI assistant.