Power Query sample data
Fictional datasets with realistic mess, free to download for practice in Excel or Power BI, or to open in the M playground. Each one lists what's wrong with it and what to try. For bigger, multi-file scenarios with tested queries, see the 16 practice packs.
Kite shop orders
Weekly order lines from a fictional kite retailer with three regional stores.
25 rows · 7 columns · 4 deliberate problems
Staff roster
An HR export from a fictional logistics firm, with names typed inconsistently.
18 rows · 9 columns · 3 deliberate problems
Expense claims
Monthly expense claims from a fictional consultancy, with UK-style dates.
20 rows · 6 columns · 2 deliberate problems
Monthly units (wide)
A planning sheet with one column per month, from a fictional café chain.
6 rows · 2 columns · 2 deliberate problems
Support tickets
Helpdesk tickets from a fictional telecom provider, some still open.
20 rows · 7 columns · 2 deliberate problems
Store report with subtotals
A printed-style sales report: region shown once per block, with subtotal and total rows.
12 rows · 3 columns · 2 deliberate problems
Taking a sample of rows in Power Query
Looking for how to sample data inside a query, rather than a dataset to download? These are the common ways in M. Each example runs in our simulator on every build.
The first rows
Table.FirstN keeps the first N rows. Good for a quick look; not representative if the file is sorted.
Table.FirstN(#table({"OrderID", "Region", "Amount"}, List.Transform({1..20}, each {Text.PadStart(Text.From(_), 4, "0"), if Number.Mod(_, 3) = 0 then "North" else "South", _ * 10})), 5)Returns 5 rows: OrderID, Region, Amount.
Every 5th row
An index column plus Number.Mod gives a systematic sample spread across the whole table.
Table.RemoveColumns(Table.SelectRows(Table.AddIndexColumn(#table({"OrderID", "Region", "Amount"}, List.Transform({1..20}, each {Text.PadStart(Text.From(_), 4, "0"), if Number.Mod(_, 3) = 0 then "North" else "South", _ * 10})), "i", 0, 1), each Number.Mod([i], 5) = 0), {"i"})Returns 4 rows: OrderID, Region, Amount.
A slice from the middle
Table.Range(table, offset, count) takes rows from any position, for example to compare the start and end of a long export.
Table.Range(#table({"OrderID", "Region", "Amount"}, List.Transform({1..20}, each {Text.PadStart(Text.From(_), 4, "0"), if Number.Mod(_, 3) = 0 then "North" else "South", _ * 10})), 10, 3)Returns 3 rows: OrderID, Region, Amount.
Generate your own test table
#table with List.Transform builds a table of any size from a rule, handy for testing a query on more rows than the sample has.
#table({"OrderID", "Region", "Amount"}, List.Transform({1..20}, each {Text.PadStart(Text.From(_), 4, "0"), if Number.Mod(_, 3) = 0 then "North" else "South", _ * 10}))Returns 20 rows: OrderID, Region, Amount.
In Power BI Desktop and Excel, the editor previews only the first 1,000 rows of each step; column profiling can be switched to the whole dataset at the bottom of the editor (Column profiling based on entire data set).