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
- In Excel choose Data > Get Data > From Database > From SQL Server Database. In Power BI Desktop choose Get data > SQL Server.
- Server:
learnsql.cpetoday.com,1433. Database:pqlab. Choose Import. - In the Navigator, search for a prefix such as
lab_hdrorlab_mrgto 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
- Sort by
row_idfirst 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, orline_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_utcandlab_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_yearis 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_matrixandlab_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
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
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 |
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.