Cube.Parameters

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Cube.Parameters

What is Cube.Parameters?

Cube.Parameters is a Power Query M function that allows users to create dynamic queries based on user input. This function is particularly useful when dealing with large datasets where it is not feasible to load all the data at once. By using Cube.Parameters, users can filter the data based on user input, which allows them to work with smaller datasets that are easier to manage.

How Does Cube.Parameters Work?

Cube.Parameters works by creating a parameter that can be used to filter data. This parameter can be any valid M expression, such as a text string or a numerical value. When the user runs the query, they are prompted to enter a value for the parameter. This value is then used to filter the data based on the specified criteria.

The M Code Behind Cube.Parameters

To understand how Cube.Parameters works, it is important to understand the M code that powers it. The following code demonstrates how to create a dynamic query using Cube.Parameters:

let

Parameter1 = Cube.Parameters(“Parameter1”, type text),

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

FilteredRows = Table.SelectRows(Source, each [Column1] = Parameter1)

in

FilteredRows

This code creates a parameter called "Parameter1" of type text. The source data is then loaded into the query, and the Table.SelectRows function is used to filter the data based on the value of Parameter1. When the query is run, the user is prompted to enter a value for Parameter1. This value is then used to filter the data based on the specified criteria.

Using Cube.Parameters in Practice

To demonstrate how Cube.Parameters can be used in practice, let's consider an example where we have a large dataset containing sales data for a company. We want to create a dynamic query that allows us to filter the data based on a specific product category.

To create this query, we would start by loading the data into Power Query and then adding a step to create a parameter using the Cube.Parameters function. We would then use the Table.SelectRows function to filter the data based on the value of this parameter.

let

Parameter1 = Cube.Parameters(“Product Category”, type text),

Source = Excel.CurrentWorkbook(){[Name=”SalesData”]}[Content],

FilteredRows = Table.SelectRows(Source, each [Product Category] = Parameter1)

in

FilteredRows

When we run this query, we are prompted to enter a value for "Product Category". We can then enter a specific product category, such as "Electronics", and the query will filter the data to only include sales data for that category.

In conclusion, Cube.Parameters is a powerful function in Power Query M that allows users to create dynamic queries based on user input. By understanding the M code behind this function, users can take full advantage of its capabilities and enhance their 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)