Table.Partition

What is Table.Partition?

Table.Partition is an M function that allows you to split a large table into smaller tables based on a specified partitioning column. This partitioning column is used to group the data in the original table, creating smaller tables that can be processed more efficiently. This function is commonly used when working with large datasets that would otherwise take a long time to load or manipulate.

The M Code Behind Table.Partition

The M code for Table.Partition is relatively straightforward. Here’s an example that partitions a table called “Sales” based on the “Region” column:

In this code, the “Source” variable refers to the original table. The “Table.Partition” function is then applied to the “Source” table, with “Region” specified as the partitioning column. The resulting table is then stored in the “Partitioned” variable.

How to Use Table.Partition Effectively

Now that we understand the M code behind Table.Partition let’s explore how to use it effectively. Here are some tips:

Choose the Right Partitioning Column

The key to using Table.Partition effectively is to choose the right partitioning column. This column should have a relatively low number of distinct values, so the resulting tables are not too small or too large. You should also consider the data type of the partitioning column, as some data types may work better than others.

Use Parallel Processing

When processing large datasets, it’s essential to take advantage of parallel processing to speed up the process. Table.Partition automatically parallelizes the data processing across multiple CPU cores if your computer has them. This can significantly improve the performance of your queries.

Be Careful with Memory Usage

When partitioning a table, you’re essentially creating multiple tables from the original data. This can quickly consume a lot of memory, especially if you’re working with a large dataset. Be sure to monitor your computer’s memory usage and adjust your partitioning strategy as necessary to avoid running out of memory.

Combine the Results

After partitioning a table, you’ll typically want to combine the results back into a single table for further processing. The easiest way to do this is to use the “List.Combine” M function. Here’s an example:

In this code, the “List.Combine” function is used to combine the partitioned tables into a list. The resulting list is then converted back to a table using the “Table.FromList” function. Finally, the table is expanded using the “Table.ExpandRecordColumn” function to create separate columns for each of the original table’s columns.

Table.Partition is an essential M function for optimizing your Power Query queries when working with large datasets. By partitioning your data into smaller chunks, you can take advantage of parallel processing and improve query performance. When using Table.Partition, be sure to choose the right partitioning column, monitor memory usage, and combine the results effectively. With these tips in mind, you’ll be well on your way to creating faster and more efficient Power Query queries.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us