Table.Partition Function (Power Query M)

Table.Partition splits a table into a list of smaller tables, based on a column value and a hash function. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to break one table into a fixed number of separate tables, where each row is routed by its value in a column, this is the function you reach for.

Syntax of Table.Partition Function

Table.Partition(table as table, column as text, groups as number, hash as function) as list

where

  • table (required, table). The table you want to split apart.
  • column (required, text). The name of the column whose value decides where each row goes.
  • groups (required, number). How many tables to split into. You always get exactly this many tables back.
  • hash (required, function). A function applied to the column value of each row. The result modulo groups picks which table the row lands in.

Returns: a list of exactly groups tables. Each input row goes into the table at position hash value modulo groups. Some returned tables can be empty.

In plain terms, you pick a column and a number of buckets, and the hash decides which bucket each row falls into.

Example 1: Count the partitions you get back

You have a Tickets query with a Ticket and a Priority column, and you want to split it into 3 tables by priority.

Here is the starting data:

TicketPriority
T-1001
T-1012
T-1021
T-1033
T-1042

Partition by Priority into 3 groups, using each _ as the hash so each priority value hashes to itself:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Priority",Int64.Type}}),
    Partitions = Table.Partition(Typed,"Priority",3,each _),
    Result = List.Count(Partitions)
in
    Result

Result: 3

You asked for 3 groups, so you get a list of 3 tables back, no matter how the rows spread across them. Wrapping the result in List.Count confirms the count.

Example 2: Count rows in the first partition

Same Tickets2 data, but now you want to know how many rows landed in the first table.

Here is the starting data:

TicketPriority
T-1003
T-1016
T-1021
T-1039
T-1042

Index the list with {0} and count its rows:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets2"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Priority",Int64.Type}}),
    Partitions = Table.Partition(Typed,"Priority",3,each _),
    Result = Table.RowCount(Partitions{0})
in
    Result

Result: 3

Priorities 3, 6, and 9 all hash to 0 (each is divisible by 3), so those three rows share the first partition.

Example 3: Pull out a single partition as a table

This time you want the actual table from the first partition, not just a count.

Here is the starting data:

TicketPriority
T-1003
T-1016
T-1021
T-1039
T-1042

Index the list with {0} to get one of the tables:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets3"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Priority",Int64.Type}}),
    Partitions = Table.Partition(Typed,"Priority",3,each _),
    Result = Partitions{0}
in
    Result

The first partition holds every row whose Priority is divisible by 3:

TicketPriority
T-1003
T-1016
T-1039

Each item in the returned list is a full table with the same columns as the input.

Example 4: Route rows with a custom hash

The hash does not have to be each _. Here you have a Stock query and want to split items into a low bucket and a high bucket at 75 units.

Here is the starting data:

ItemUnits
Bolts40
Nuts120
Washers75
Screws200
Nails60

Use each Number.From(_>75) so items over 75 hash to 1 and the rest hash to 0, then count the high bucket:

let
    Source = Excel.CurrentWorkbook(){[Name="Stock"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type}}),
    Partitions = Table.Partition(Typed,"Units",2,each Number.From(_>75)),
    Result = Table.RowCount(Partitions{1})
in
    Result

Result: 2

Number.From turns true/false into 1/0, so Nuts (120) and Screws (200) land in partition 1. Washers at exactly 75 is not over 75, so it stays in partition 0. If you only wanted the high rows, Table.SelectRows would filter to one table instead of splitting into many.

Example 5: Ask for more groups than the hash produces

You can request more groups than your hash actually fills. The extra tables come back empty.

Here is the starting data:

ItemUnits
Bolts40
Nuts120
Washers75
Screws200
Nails60

Ask for 3 groups even though the hash only ever returns 0 or 1:

let
    Source = Excel.CurrentWorkbook(){[Name="Stock2"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units",Int64.Type}}),
    Partitions = Table.Partition(Typed,"Units",3,each Number.From(_>75)),
    Result = Table.RowCount(Partitions{2})
in
    Result

Result: 0

The hash never returns 2, so the third partition is a valid but empty table.

Example 6: See the raw list of tables

The other examples wrapped the result in a count or an index. Drop the wrapper and you see what Table.Partition really hands back: the list of tables itself.

Here is the starting data:

TicketPriority
T-1001
T-1012
T-1021
T-1033
T-1042

Return the partitions directly instead of counting or indexing them:

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets6"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Priority",Int64.Type}}),
    Partitions = Table.Partition(Typed,"Priority",3,each _)
in
    Partitions

The result is a list of 3 tables. With each _ and 3 groups, the hash is Priority modulo 3: partition 0 holds the priority 3 row (T-103), partition 1 holds the priority 1 rows (T-100, T-102), and partition 2 holds the priority 2 rows (T-101, T-104). In the editor each item shows as a clickable Table link you can drill into.

Things to keep in mind with Table.Partition

  • The hash is applied to the column value, never the whole row. If you need to route by more than one column, build a single key column first (for example with Table.AddColumn) and partition on that.
  • Number your buckets from the hash result modulo groups. A hash returning 5 with groups of 3 lands the row in partition 2, not partition 5. Indexing past groups - 1 throws Expression.Error: There weren't enough elements in the enumeration to complete the operation.
  • The hash must return a number. A true/false or text result throws, so wrap logical tests in Number.From (Example 4) and text keys in something like Text.Length or a numeric code.
  • Freshly loaded columns are type any. Hashing them can misbehave or error, so set the column type first with Table.TransformColumnTypes (every example does this with Int64.Type).
  • Rows keep their original order within each partition. Table.Partition only routes rows, it never sorts them. Sort each partition afterward with Table.Sort if you need an order.

Common questions about Table.Partition

What is the difference between Table.Partition and Table.Group?

Table.Group groups by distinct values in a column and lets you aggregate each group, returning one table. Table.Partition always returns a fixed number of separate tables, routed by a hash, and does no aggregation.

How is it different from Table.Split?

Table.Split cuts a table into chunks of a set row count in order (first N rows, next N, and so on). Table.Partition decides each row’s table from its column value, so a row’s position in the table does not matter.

Related Power Query Functions / Articles:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.