Power Query recipes in Power Query Studio
Grouped by task. Each entry gives the query name in the workbook, what it does, what the starter data gives and what to watch for. The list is generated from the same manifest the workbooks are built from.
No recipe matches. Use a shorter word, or choose All levels.
Cleaning
-
Clean text, Thai digits and Buddhist-era dates
Fixes hidden spaces, Thai digits, amounts and mixed date formats
Starter data gives: 366 rows in the starter data; problems are kept in ParseErrors
Watch out: Dates with / are read as day/month/year only. US order is never guessed
-
Keep the latest revision, deterministically
Deduplicates with UpdatedAt, then InputRow as the tie-breaker
Starter data gives: 360 OrderIDs after removing 6 revisions
Watch out: When timestamps tie, the row that comes later in the combined source wins. Define a stable sequence in real work
-
Route bad rows back to the source
Shows the raw value and the reason instead of silently dropping rows
Starter data gives: 6 rows need fixing
Watch out: Fix them in inSales_* and refresh. Never edit the output table
Combining and matching
-
Merge masters, net sales and cost
Builds a sales table ready for reporting
Starter data gives: 354 rows; total net sales on the Dashboard
Watch out: An unknown product is not given a cost of 0, so profit is never overstated
-
Find customers missing from the master
Left anti join for records that fell through
Starter data gives: Finds one row for C999
-
Full outer reconciliation
Separates one-sided items and amount differences
Starter data gives: 4 refs: Matched / Amount differs / Ledger only / Bank only
-
Fuzzy match with a score and a review queue
Finds similar names but leaves the decision to a person
Starter data gives: Candidates and scores come from Excel's own fuzzy engine at refresh
Watch out: Similarity is not a probability. Never approve or overwrite the master automatically
-
As-of join: the price in force on the order date
Uses the latest price effective on or before the order date
Starter data gives: First 20 rows, to show the principle
Watch out: A per-row lookup is expensive on large data. Use a temporal join in the database where you can
-
Compare New / Changed / Deleted
Finds differences against a separately stored snapshot
Starter data gives: A unchanged, B changed, C deleted, D new
Watch out: Refresh does not keep the old snapshot for you. Store history outside the query output
-
Match the master valid at the time
EffectiveFrom includes its start day; EffectiveTo excludes its end day
Watch out: This uses an existing SCD2 table. It is not a system that records history into a database
Summaries and ranking
-
Group by month and channel
Totals with transaction counts and missing cost
Starter data gives: 27 groups (9 months x 3 channels)
Watch out: KnownCost only sums costs that exist; profit is null for any group with missing cost
-
Top N by region
Ranks within a group from a parameter
Starter data gives: TopN products per region, set in Settings
-
Running total by channel
Sorts first, then restarts the total inside each group
Watch out: List.Accumulate per list suits small to medium data. Use a database for large volumes
-
3-month moving average
Averages 3 real calendar months and fills empty months with 0
Watch out: The first 2 months average what exists in the range, and a month with no sales is assumed to be 0
-
Dense rank within a group
Ties share a rank and no rank is skipped
Starter data gives: East: 1,1,2 and West: 1
-
Customer RFM and segments to watch
Summarises last purchase date, frequency and spend
Watch out: The 60-day / 15-order thresholds are for demonstration, not a model that fits every business
-
Cohort retention by first purchase month
Counts returning customers against the starting cohort
Watch out: Cohorts start from the history in this file, not a lifetime first purchase. Empty cells are not filled with 0
-
ABC / Pareto for products
Shows cumulative share and product importance
Watch out: Uses the share before the current row, so a product straddling 80% stays in A
Reshaping tables
-
Unpivot months that keep growing
Turns Jan/Feb/... columns into dated rows
Starter data gives: 9 rows from 3 departments x 3 months
-
Pivot channels into dynamic columns
Builds a comparison without naming each channel
Starter data gives: 9 months with a column per channel
-
Repair a report with blank group headers
Blank to null, then Fill Down in the right order
Starter data gives: 4 rows, TOTAL excluded
-
Split several values in one cell into rows
Splits comma-separated tags and trims spaces
Starter data gives: 5 tags from 3 tickets
-
Accept files whose column names differ
Maps names, selects a common schema and keeps missing columns as null
Starter data gives: A=100 B=200 Region=null
Watch out: The common schema drops Extra on purpose. Never drop important data in real work
-
Unfold a two-row header
Fills the group header across, builds column names, then unpivots
Starter data gives: 8 rows from 2 regions x 2 months x 2 metrics
Dates and calendars
-
Fiscal calendar and business days
Builds a date dimension without a source file
Starter data gives: 273 days from 2026-01-01 up to 2026-10-01
Watch out: FiscalYear is named by the year the period ends. Holidays are sample data
-
Count SLA days, skipping weekends and holidays
States clearly whether the start and end days count
Starter data gives: Start and end days included, business days only
-
Filter a date range with parameters
StartDate included, EndDate excluded, so period edges never overlap
Watch out: Changing the range does not change the Dashboard, which uses all data. q35 is a separate filtered set
-
Fill dates with no transactions via a calendar join
Shows zero-sales days while keeping every date row
Watch out: 0 means no transaction in the file. It does not prove the source sent everything
Advanced techniques
-
Unfold a reporting line with recursion
Builds the path and level, and detects cycles
Starter data gives: 5 employees and their reporting lines
Watch out: The recursive function stops with an error on a cycle or a missing parent
-
Explode a multi-level BOM
Multiplies quantities through every level and sums repeated leaves
Starter data gives: 10 KITs need leaf parts SCREW 140 and PANEL 20
Watch out: LEG is an intermediate part, so it is not in the leaf result. The 10 is a demo run size; change the fxBOM argument
-
Select columns by pattern and transform them together
Converts every column that starts with Amount_ automatically
Starter data gives: 1250 / 40 and 999 / -10
-
A custom incremental window
Half-open filter for reading data in batches
Watch out: This Excel query does not accumulate history and is not a Power BI incremental refresh policy. A local source is still read in full before the filter
JSON, XML, HTML and APIs
-
Unfold nested JSON records and lists
Turns orders/items into a detail table
Starter data gives: 3 order lines totalling 850
-
Offline API pagination
List.Generate fetches the next page until an empty one
Starter data gives: 5 records from 3 pages with data, stopping at empty page4
Watch out: The sample uses page numbers with an empty-page sentinel. A cursor API needs different state
-
Turn XML into a table
Reads XML text with repeating items
Starter data gives: 3 items in the XML
Watch out: Real XML may carry namespaces or attributes. Inspect the structure before expanding
-
Read an HTML table with a CSS selector
Picks only the target table from HTML
Starter data gives: 3 products, no web connection needed
Data quality
-
Profile every column
Checks null / distinct / min / max before using the data
Watch out: The profile covers the rows the query processed in full and can be heavy at a million rows
-
Flag outliers with IQR
Finds extreme values using quartiles
Starter data gives: The value 100 is an outlier
Watch out: Treat it as a signal to review. Never delete data automatically
-
Assertions for invariants and edge cases
Repeatable tests of the logic
Starter data gives: 10 assertions, evaluated by Excel at every refresh
Watch out: Passing assertions does not mean every connector works or every master is complete
-
Time and row counts of the last refresh
Reads row counts and a runtime timestamp after refresh
Watch out: The table is overwritten every time. It is not a running log and does not prove every other query refreshed
Connecting real sources
-
Combine a folder of CSV files with per-file status
Filters extensions and hidden files and records why a file failed
Watch out: c01 is a file check report; use c01b to combine the data. A file with the wrong schema stops c01b
-
Combine Excel tables from many workbooks
Picks the Transactions table from every xlsx
Watch out: Every file needs a Table named Transactions, not just a sheet name. Do not point it at the folder holding this workbook
-
Paged REST with bounded retry
Uses RelativePath/Query and keeps secrets out of the code
Watch out: Needs an API shaped {data:[...]} that ends with an empty page. Adjust auth, Retry-After and cursors to the service contract; fix credentials on 401/403
-
SQL parameters with native query folding
Passes date parameters without concatenating SQL strings
Watch out: Table dbo.Sales must exist. Check View Native Query / the server plan, because EnableFolding does not guarantee every step folds
-
PDF table extraction
Checks which tables the reader found before choosing the data
Watch out: A connector blueprint. Scanned PDFs need OCR first and the schema may change, so pick the Data that matches the table
-
OData entity
Connects to an entity and lets the connector handle OData paging
Watch out: Point the URL at the entity you need and set credentials for the real system
-
HTML table from a live website
Keeps the DOM selector separate from the download
Watch out: The selector is an example and must match the real site. A page rendered by JavaScript may have no data in the downloaded HTML
The workbooks and guides are finished, and the distribution terms are being settled. Until then, every recipe is free to read on this site, with its result and its pitfalls.
Every report's Excel workbook is free to download, in Thai and English.