Value.Versions

D

Back to top

T

Back to top

#

Back to top

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

What is the Value.Versions Function?

The Value.Versions function is used to access all the values that a specific column in a table has had over time. For example, if you have a table with a column that contains the prices of a stock over a period of time, you can use the Value.Versions function to access all the previous prices of the stock.

The syntax for the Value.Versions function is as follows:

Value.Versions(table as table, column as text) as list

The table parameter is the table that you want to access, and the column parameter is the name of the column that you want to access the values from.

The M Code Behind Value.Versions

The M code behind the Value.Versions function is relatively simple. It starts by filtering the table to only include the rows where the value in the specified column is not null. It then groups the resulting table by the value in the specified column and creates a new column that contains a list of all the values in the specified column for each group.

Here is the M code behind the Value.Versions function:

(table as table, column as text) =>

let

Source = Table.SelectRows(table, each [column] <> null),

Grouped = Table.Group(Source, {column}, {{"Versions", each [column], type list}})

in

Grouped[Versions]

Let’s break down each step in the M code:

Step 1: Filter the Table

The first step in the M code is to filter the table to only include the rows where the value in the specified column is not null. This is done using the `Table.SelectRows` function.

Source = Table.SelectRows(table, each [column] <> null),

Step 2: Group the Table

The next step is to group the resulting table by the value in the specified column. This is done using the `Table.Group` function.

Grouped = Table.Group(Source, {column}, {{"Versions", each [column], type list}})

The `Table.Group` function takes three parameters:

– The table to group (`Source`)

– The columns to group by (`{column}`)

– The aggregations to apply to each group (`{{“Versions”, each [column], type list}}`)

The `{{“Versions”, each [column], type list}}` parameter creates a new column called “Versions” that contains a list of all the values in the specified column for each group.

Step 3: Return the List of Versions

The final step in the M code is to return the list of versions. This is done by referencing the “Versions” column in the `Grouped` table.

Grouped[Versions]

The Value.Versions function is a powerful tool in Power Query that allows users to access all the previous values in a specific column. Understanding the M code behind this function can help users to customize it to fit their specific needs. By breaking down each step in the M code, we can see how the function filters the table and groups it to create a list of all the previous values in the specified column.

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)