Table.Partition Function in Power Query

The Table.Partition function in Power Query, partitions the table into a list of groups number of tables, based on the value of the column of each row and a hash function.

Syntax

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

The function has the following parameters:

table: The table to partition.
column: The column to hash to determine which returned table the row is in.
groups: The number of tables the input table will be partitioned into.
hash: The function applied to obtain a hash value.

How It Works

Take the value from the specified column in each row.
Apply the hash function to that value.
Use the formula: hashValue mod groups to decide which partition (table) the row goes into.
Returns a list of groups number of tables, each containing rows assigned to that partition.

Example: Partitioning by Name Length.

Power Query M

let
  Source = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = "Katrina", Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = "Mohini", Marks = 278], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = 875], 
      [CustomerID = 8, Name = "Anjali", Marks = 380]
    }
  ), 
  return = Table.Partition(
    Source,
    "Name",
    3,
    each Text.Length(_)
)
in
  return

How it works:

The hash is Text.Length(_) (length of the Name).
Modulo 3 → Text.Length(_) mod 3 determines which partition the row goes to.
Partition Index = Text.Length(Name) mod 3

Partitioned output (list of 3 tables):

{
    // Partition 0: Names with length mod 3 = 0 (6, 9)
    Table.FromRecords({
        [CustomerID = 1, Name = "Ashish", Marks = 568],
        [CustomerID = 5, Name = "Mohini", Marks = 278],
        [CustomerID = 6, Name = "Meenakshi", Marks = 289],
        [CustomerID = 8, Name = "Anjali", Marks = 380]
    }),
    
    // Partition 1: Names with length mod 3 = 1 (4, 7)
    Table.FromRecords({
        [CustomerID = 2, Name = "Katrina", Marks = 855],
        [CustomerID = 3, Name = "Alia", Marks = 380],
        [CustomerID = 7, Name = "Esha", Marks = 875]
    }),
    
    // Partition 2: Names with length mod 3 = 2 (5)
    Table.FromRecords({
        [CustomerID = 4, Name = "Vicky", Marks = 458]
    })
}

Example: Partitioning by CustomerID (Even/Odd).

Power Query M

let
  Source = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Marks = 568], 
      [CustomerID = 2, Name = "Katrina", Marks = 855], 
      [CustomerID = 3, Name = "Alia", Marks = 380], 
      [CustomerID = 4, Name = "Vicky", Marks = 458], 
      [CustomerID = 5, Name = "Mohini", Marks = 278], 
      [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
      [CustomerID = 7, Name = "Esha", Marks = 875], 
      [CustomerID = 8, Name = "Anjali", Marks = 380]
    }
  ), 
  return = Table.Partition(
    Source,
    "CustomerID",
    2,
    each _ // identity hash function
)
in
  return

How it works:

The hash function is each _ (returns the value of CustomerID).
Modulo 2 → CustomerID mod 2 determines the partition.

Partitioned output (list of 2 tables):

{
    // Partition 0: Even CustomerIDs (mod 2 = 0)
    Table.FromRecords({
        [CustomerID = 2, Name = "Katrina", Marks = 855],
        [CustomerID = 4, Name = "Vicky", Marks = 458],
        [CustomerID = 6, Name = "Meenakshi", Marks = 289],
        [CustomerID = 8, Name = "Anjali", Marks = 380]
    }),

    // Partition 1: Odd CustomerIDs (mod 2 = 1)
    Table.FromRecords({
        [CustomerID = 1, Name = "Ashish", Marks = 568],
        [CustomerID = 3, Name = "Alia", Marks = 380],
        [CustomerID = 5, Name = "Mohini", Marks = 278],
        [CustomerID = 7, Name = "Esha", Marks = 875]
    })
}

Accessing Partitions We can access each partition using list indexing syntax:

Power Query M

PartitionList{Index}

Explanation:

PartitionList is the list returned by Table.Partition(...).
Indexing in Power Query is zero-based: first partition = {0}, second = {1}, etc.
For example, PartitionList{0} returns the first partition table, and PartitionList{1} returns the second.

Example: Accessing individual partitions.

Power Query M

let
    Source = Table.FromRecords({
        [CustomerID = 1, Name = "Ashish", Marks = 568], 
        [CustomerID = 2, Name = "Katrina", Marks = 855], 
        [CustomerID = 3, Name = "Alia", Marks = 380], 
        [CustomerID = 4, Name = "Vicky", Marks = 458], 
        [CustomerID = 5, Name = "Mohini", Marks = 278], 
        [CustomerID = 6, Name = "Meenakshi", Marks = 289], 
        [CustomerID = 7, Name = "Esha", Marks = 875], 
        [CustomerID = 8, Name = "Anjali", Marks = 380]
    }),

    PartitionList = Table.Partition(Source, "CustomerID", 2, each _),  // list of 2 tables

    Partition0 = PartitionList{0},  // Access first partition (even CustomerIDs)
    Partition1 = PartitionList{1}   // Access second partition (odd CustomerIDs)
in
    Partition0  // or Partition1 depending on which you want to return

Note: If we want to combine all partitions back into a single table, use:

Power Query M

Table.Combine(PartitionList)

Previous Next