There’s a nice new feature in Power Query Online (the version of Power Query used in Dataflows): parameters of type List. You can see this as a new option in the Type dropdown in the “Manage parameters” dialog:
Why is this interesting? In the past, Power Query parameters were always single values like a date or a string; now a parameter can contain mutliple values.
There’s one other new feature in Power Query Online that goes along with this: In and Not In filters, which can use these new List parameters.
I’m sure there are other cool things you can do with this but I’ll leave them to future blog posts.
Interesting..my first question (as always) is “but does it fold” and I’ll have to wait until back at work to find out. Thanks for the heads up!
The answer is yes, it folds (at least in Postgres and SQL Server connectors)
That’s awesome. Having lists sounds amazing. However, does that work on Service? I think I have tried using parameters in DataFlows but I couldn’t handle them like the datasets 🙁
It works, but the unfortunate thing about parameters in dataflows is they cannot be updated in dataflow settings like dataset parameters can, and they cannot be updated via the REST API either. Not really sure why, but it’s a shame because it hamstrings attempts to automate deployments of dataflows – parameters must be hardcoded in the .json file
How to make it work for Direct Query to SQL Server ?
Dear Chris
Many thanks for your posts, again and again very helpful. I’m struggling with a simple “filter by parameter” in Excel PQ as well as Power BI Desktop.
It’s so simple to pass a list to a parameter via query, however, when I apply the filter to the column, only the preset value is filtered and not the list of values defined in parameters.
Is there something wrong with my version? Can you please help as this caused almost a sleepless night to me.
many thanks
Peter
Can you post the M code you’re using?
Hi Chris
Glad to hear from you. I reduced my problem to an easy Excel sheet. Here are the code pieces:
This is where I get the list of names for the parameter:
let
Quelle = stg_Name,
Name = Quelle[Name]
in
Name
——————————————-
This is the code for the parameter:
“Sandra” meta [IsParameterQuery=true, ExpressionIdentifier=trf_Name, Type=”Text”, IsParameterQueryRequired=false]
//trf_Name is above query where I define the list of names for the parameter
———————————————
This is where the filter is applied:
let
Quelle = trf_Frauen,
#”Gefilterte Zeilen” = Table.SelectRows(Quelle, each [Name] = Hot)
in
#”Gefilterte Zeilen”
The problem is where the filter is applied. If you look at the code shown in the last screenshot in the post, you’ll see that instead of using Table.SelectRows(Quelle, each [Name] = Hot) you should have an expression that does something like this: Table.SelectRows(Quelle, each List.Contains(Hot, [Name])
That I have also tried after having found your post. However, it results in this error:
Expression.Error: Der Wert “”Sandra”” kann nicht in den Typ “List” konvertiert werden.
Details:
Value=Sandra
Type=[Type]
I don’t have it in english. I’m sure you understand it: Value Sandra cannot be converted into typ “list”
I have also tried to empty the preset value in the parameter window; then the error says “cannot convert “null” into type “list”
That suggests you have not created a list parameter
Well, a list which comes from a query. I admit it’s not exactly the same as you did above. But how could I then make a dynamic filter?
Other way would be a right outer join, however, I experienced performance problmens doing this. That’s why I’m trying this kind of filter.
Ah, now it’s working, when I directly use the list as the filter!!!
I first passed the list to the parameter but this is not necessary I see.
Many thanks!