Power Query Apply Parameter

Question

Good morning,
I am developing a solution in excel through the power query where I source the analysis service.
And I am trying to optimize the solution where I intend to apply the parameter.
For that I am using the following instruction:

{Cube.ApplyParameter, {"ColumnNameYear"}, {"2021"}}

But it is giving the following error:
"Expression.Error: It is not possible to convert a value of type List to type Function."

What is the correct way to apply a parameter?

Thanks in advance for the answer

Answer 1

The suggested form had already tried and it works but I intend to filter it immediately when reading the data so that it is faster to read.

This is what the term "folding" means, when Power Query executes the M code it "folds" the two M statements into a single MDX query so that only the filtered data is returned. It is not returning all the data, then filtering it in a second step. So this additional statement does in fact already filter it immediately.

Answer 2

{Cube.ApplyParameter, {"ColumnNameYear"}, {"2021"}}
"Expression.Error: It is not possible to convert a value of type List to type Function."

You set the values in curly brackets, which defines them as list, but an function (in round brackets) is expected; that's what the error messages says.
See Expressions, values, and let expression => Function value / List

Answer 3

sorry for the insistence but you don't have an example to send me how this function is called?

https://learn.microsoft.com/en-us/powerquery-m/cube-applyparameter

I tried to find examples but I am not finding it and I am not able to solve it.

{Cube.ApplyParameter, "DimensionColumnName", {"2021"}}

I intend to filter the data to only return the data for "2021"

Answer 4

I don't think ApplyParameters will do what you want anyway. If you just want to filter your data for the 2021 year then if you run that query in the designer and apply the filter to your year column the designer will add an extra Table.SelectRows call, but the power query engine will fold this filter into a single MDX query.

I just tested on a local cube and when I filtered on Marital Status = M it generated the following extra line in the M code, but this filter was folded into the one MDX query when I ran the import.

"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Employee.Marital Status]) = "[Employee].[Marital Status].&[M]" meta [DisplayName = "M"]))

Answer 5

Instead of writing parameter in Query Editor directly, have you tried using built-in fuction to build the parameterized query , showed like in this Excel using parameters