Value.Optimize

D

T

The M Code Behind the Power Query M function Value.Optimize

Understanding Value.Optimize

Before we dive into the M code behind Value.Optimize, let’s first understand what this function does. Value.Optimize is used to optimize the data types of columns in a table. When you load data into Power Query, it tries to automatically detect the data types for each column. However, this detection is not always accurate, and sometimes results in slower query performance.

This is where Value.Optimize comes in. It allows you to optimize the data types of columns in a table to improve query performance. For example, if you have a column with values that are all integers, but Power Query detects it as a decimal column, you can use Value.Optimize to change the data type to integer and improve query performance.

The M Code Behind Value.Optimize

Now that we understand what Value.Optimize does, let’s take a look at the M code behind this function. Here is the M code for Value.Optimize:


let

Source = #table( type table [ Column1 = text, Column2 = text ], {} ),

Optimize = (table as table) as table =>

let

types = Table.TransformColumnTypes(table, List.Transform(Table.ColumnNames(table), each {_, type any})),

optimizedTypes = Table.TransformColumns(types, List.Transform(Table.ColumnNames(types), each {_, Value.Type(Text.Combine(List.Sort(List.Distinct(Table.Column(table, _)))))}))

in

Table.TransformColumnTypes(table, List.Transform(Table.ColumnNames(optimizedTypes), each {_, optimizedTypes{_}[Column1]}))

in

Optimize


As you can see, the M code for Value.Optimize is quite complex. However, let’s break it down step-by-step to help you understand what it does.

First, the M code creates a table with two columns, Column1 and Column2. This table is used as a dummy table and will be replaced with the actual table you want to optimize.


let

Source = #table( type table [ Column1 = text, Column2 = text ], {} ),


Next, the M code defines the Optimize function. This function takes a table as an argument and returns an optimized table.


Optimize = (table as table) as table =>


Inside the Optimize function, the M code transforms the data types of the columns in the table. It does this by first creating a new table that has the same column names as the input table, but with all columns set to type any.


types = Table.TransformColumnTypes(table, List.Transform(Table.ColumnNames(table), each {_, type any})),


Next, the M code optimizes the data types of each column in the table. It does this by sorting and removing duplicate values in each column, and then using the Text.Combine and Value.Type functions to determine the optimal data type for the column.


optimizedTypes = Table.TransformColumns(types, List.Transform(Table.ColumnNames(types), each {_, Value.Type(Text.Combine(List.Sort(List.Distinct(Table.Column(table, _)))))}))


Finally, the M code transforms the input table once again, using the optimized data types from the previous step.


Table.TransformColumnTypes(table, List.Transform(Table.ColumnNames(optimizedTypes), each {_, optimizedTypes{_}[Column1]}))


Using Value.Optimize in Your Queries

Now that you understand the M code behind Value.Optimize, let’s see how you can use this function to optimize your queries.

To use Value.Optimize, you first need to load your data into Power Query. Once your data is loaded, select the table you want to optimize and click on the Transform Data button. This will open the Power Query Editor.

In the Power Query Editor, click on the Advanced Editor button to open the M code editor. Here, you can replace the existing M code with the Value.Optimize code we discussed earlier.


let

Source = ,

Optimize = Value.Optimize(Source)

in

Optimize


Replace “” with the M code for your data source. This could be a SQL query, a CSV file, or any other data source that Power Query supports.

Once you have replaced the M code, click on the Done button to close the editor. Power Query will now optimize the data types of your table, which should result in improved query performance.

In this article, we took an in-depth look at the M code behind the Power Query M function Value.Optimize. We also learned how to use this function to optimize the data types of columns in a table, which can help improve query performance. By understanding the M code behind Power Query functions, you can gain a deeper understanding of how Power Query works and use this knowledge to optimize your queries and improve your data analysis workflows.

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

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)