Power Query Apply Parameter

Rui José Silva Carvalho 1 Reputation point
2021-05-18T10:51:10.097+00:00

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

97379-errorlist.jpg

SQL Server Analysis Services
SQL Server Analysis Services

A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.

Community Center | Not monitored
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Darren Gosbell 1,471 Reputation points
    2021-05-19T07:51:59.453+00:00

    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.

    Was this answer helpful?

    1 person found this answer helpful.
  2. Lukas Yu -MSFT 5,826 Reputation points
    2021-05-21T08:23:31.213+00:00

    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

    Was this answer helpful?

    0 comments
  3. Darren Gosbell 1,471 Reputation points
    2021-05-18T23:01:03.1+00:00

    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"]))

    Was this answer helpful?

  4. Rui José Silva Carvalho 1 Reputation point
    2021-05-18T16:55:57.493+00:00

    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"

    Was this answer helpful?

  5. Olaf Helper 47,631 Reputation points
    2021-05-18T11:17:32.923+00:00

    {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

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.