Table.FromPartitions - Table Function

Table.FromPartitions is a Power Query M function that returns a table resulting from combining a set of partitioned tables. The function returns a combined table with an added partitionColumn.

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

Syntax

Table.FromPartitions(
   partitionColumn as text,
   partitions as list,
   optional partitionColumnType as nullable type,
) as table

Description

The Table.FromPartitions function combines multiple tables into a single table while adding an additional column ( partitionColumn) that labels or categorizes the rows based on their source. You can optionally provide a partitionColumnType to include a data type for the new column. By default, the data type will be any.

This function is particularly useful when you need to merge tables that represent different segments of data—such as different time periods or categories—into a unified dataset, with a clear identifier for each row’s origin.

Examples

Let’s find out how the Table.FromPartitions function works with a few examples.

Initial Setup: The Source Tables

Assume you’re working with the following tables:

TableJan: Contains data for January.
TableFeb: Contains data for February.
TableMar: Contains data for March.

Table.FromPartitions source tables to combine in Power Query M

If your goal is to merge TableJan, TableFeb, and TableMar into a single table, the Table.Combine function is a straightforward choice. However, this approach simply stacks the rows without adding any context about where each row came from.

The Table.FromPartitions function offers a more flexible approach. It allows you to add an additional column to each table, which you can use to label the rows. For example, you might want to add a column that specifies the month abbreviation for each row.

Here’s how you can achieve this:

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

In this code:

“Month”: This is the name of the new column that will be added to each table.
Second Argument: This is a list of lists. Each inner list contains two elements:
- A value that will be stored in the new column ("Jan", "Feb", "Mar"). Notice that this value needs to be unique for each provided list.
- The tables to combine (TableJan, TableFeb, TableMar).

The outcome of this expression is:

Table.FromPartions combines tables and creates column in Power Query M

The result of this expression is a single combined table with a new column named “Month”. This column contains the values “Jan”, “Feb”, and “Mar”, corresponding to the rows from each respective table.

Adding a Data Type to the New Column

By default, the Table.FromPartitions function assigns the data type any to the new column. If you need to specify a particular data type for the “Month” column, you can use the optional third argument:

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

In this example, the “Month” column is explicitly defined as type text. All other columns respect the data types specified in the original tables.

Try it out

To try out the code yourself, you can paste the following query into the advanced editor:

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  }
      }
    ),

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

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

Other functions related to Table.FromPartitions are:

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