Table.Partition

Table.Partition is a Power Query M function that divides a table into a list of groups based on a column value and a hash function. The function returns a list of tables that have been partitioned according to the specified parameters.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.Partition function is designed to split a table into a list of multiple smaller tables based on a specified logic. This function is particularly useful when you need to segment your data into distinct groups.

It takes the following key arguments:

1. Table: This is the table you want to partition.
2. Column: The name of the column based on which the partitioning will occur.
3. Groups: The total number of tables you want to create. Each unique value produced by the partitioning logic will direct rows into a different table.
4. Partition Logic: This is where the magic happens. You define a function that determines how rows are split across the groups. The function should return a number corresponding to the group into which the row should be placed.

Examples

Let’s have a look at how you can make use of the Table.Partition function. The following table contains an overview of the stock levels of different products over time.

Now suppose that you company policy is that stock levels should never reach below 75. Your manager has asked you to create an overview of the dates where different products violated this policy.

To do this, you can make use of the Table.Partition function. It allows you to easily split the table into two based on the stock level. Here’s how:

Table.Partition(  
  Source, 
  "StockLevel", 
  2, 
  each Number.From( _ > 75 ) 
)

What this code does is:

• Source: this is the table to partition.
• “StockLevel”: refers to the column used for partitioning.
• 2: specifies the number of tables to create.
• each Number.From( _ > 75 ): the function used to partition the table. The outcome of this value should always be a number.

The result of this operation is a list of two tables. The first table contains all rows that have a stock level smaller or equal to 75. All other rows are stored in table 2.

We could also have achieved this using Table.Group with a custom comparer function. However, the code would have been much more complicated and verbose:

Table.Group(
  Source, 
  {"StockLevel"}, 
  { { "Details", each _, type table } },
  GroupKind.Global,
  ( x, y ) => 
    let myFun = (y) => if y > 75 then 1 else 0 in 
      Value.Compare ( myFun( x[StockLevel] ), myFun( y[StockLevel] ) ) 
)

Notice that if you would have instructed the Table.Partition function to create 3 groups, but the logic in the fourth argument only specifies 2 unique values, the third table it creates would be empty. To be able to fit values into the correct groups, it requires a unique hash value (created by the fourth argument) for each group.

Try it yourself

To try this yourself, you can make use of the following query:

let
  Source = 
    #table(
      type table[ Date = Date.Type, Product = Text.Type, StockLevel = Int64.Type ],
      {
          { #date(2024, 9, 1),  "Product A", 120 },
          { #date(2024, 9, 2),  "Product B", 80  },
          { #date(2024, 9, 3),  "Product C", 60  },
          { #date(2024, 9, 4),  "Product D", 150 },
          { #date(2024, 9, 5),  "Product E", 90  },
          { #date(2024, 9, 6),  "Product F", 110 },
          { #date(2024, 9, 7),  "Product G", 95  },
          { #date(2024, 9, 8),  "Product H", 50  },
          { #date(2024, 9, 9),  "Product I", 130 },
          { #date(2024, 9, 10), "Product J", 70  },
          { #date(2024, 9, 11), "Product K", 160 },
          { #date(2024, 9, 12), "Product L", 85  }
      }
    ),
    Partition = Table.Partition(  Source, "StockLevel", 2, each Number.From( _ > 75 ) )
in
    Partition

Related functions

Other functions related to Table.Partition are:

• Table.PartitionValues
• Table.Split
• Table.SplitAt
• Table.ToColumns
• Table.ToList
• Table.ToRecords
• Table.ToRows
• Table.Transpose

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-partition