Table.PartitionValues

Table.PartitionValues is a Power Query M function that retrieves information about how a table is partitioned. The function returns a table where each column is a partition column and each row corresponds to a partition in the original table.

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

Syntax

Table.PartitionValues( table as table ) as table

Description

Table.PartitionValues provides information about how a table is partitioned. At the time of writing, it can only be used in combination with the Table.FromPartition function. The function returns a table where each column is a partition column from the original table and each row corresponds to a partition.

Examples

Let’s see an example of how you can use Table.PartitionValues. To get started, you need a table creates with Table.FromPartitions.

For instance, let’s say we have the following tables:

The following code combines these tables into a single table using Table.FromPartitions:

Table.FromPartitions(
  "Month",
  { 
    { "Jan", TableJan }, 
    { "Feb", TableFeb }, 
    { "Mar", TableMar } 
  },
  type text
)

The combined table looks as follows:

To find out how the resulting table was created, you can make use of the Table.PartitionValues function. If output of previous code was in a table called CombinedTable you could do the following:

Table.PartitionValues( CombinedTable )

The resulting table look as follows:

The output shows all the values used as partitionColumn by the Table.FromPartitions function.

Try it yourself

To try this function yourself, you can use the following code:

let
  TableJan = 
    #table(
    type table[ Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
    {
        { "Product A", #date( 2024, 1, 12 ), 150 },
        { "Product B", #date( 2024, 1, 15 ), 200 },
        { "Product C", #date( 2024, 1, 29 ), 175 }
    }
),
    TableFeb = #table(
    type table[ Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
    {
        { "Product A", #date( 2024, 2, 10 ), 160 },
        { "Product A", #date( 2024, 2, 19 ), 220 },
        { "Product B", #date( 2024, 2, 20 ), 195 }
    }
),
    TableMar = #table(
    type table[ Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
    {
        { "Product C", #date( 2024, 3, 5  ), 60 },
        { "Product A", #date( 2024, 3, 9  ), 335 },
        { "Product B", #date( 2024, 3, 14 ), 95 }
    }
),
    CombinedTable = Table.FromPartitions(
  "Month",
  { 
    { "Jan", TableJan }, 
    { "Feb", TableFeb }, 
    { "Mar", TableMar } 
  },
  type text
),
    PartitionValues = Table.PartitionValues( CombinedTable )
in
    PartitionValues

Related functions

Other functions related to Table.PartitionValues are:

• Table.Partition
• 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-partitionvalues