Power QueryExercises

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.

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).