Multi value in one dynamic M query parameter

Question

Hi all,&nbsp;I need to pass multiple values to single parameter which is added as 'where' clause to query. There is no static list of these values. It's dynamically changing. I'm receiving values to parameters from RLS Customdata() function, which has impact on column of values for parameter. I would like to pass these values to single parameter in 'where' clause.&nbsp;Can you advise me how to achieve?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;lukaszbrdon&nbsp; - have you consider using the List.Contains function to filter the table.&nbsp; This should fold to the source.&nbsp; Here is example to consder:Filter Column by List Items (Exact Match) in Power Query - YouTube

syndicate_admin · Answer

Hi all,&nbsp;let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARAMETER_LIST&nbsp; =&nbsp;if&nbsp;&nbsp;&nbsp; //check to see if the parameter is a list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type.Is(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value.Type(PARAMETER),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List.Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //if it is a list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //add single quotes around each value in the list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AddSingleQuotes = List.Transform(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARAMETER,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; each "'" &amp; _ &amp; "'"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //then turn it into a comma-delimited list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DelimitedList = Text.Combine(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AddSingleQuotes,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ","&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DelimitedList&nbsp;&nbsp;&nbsp; else&nbsp;&nbsp;&nbsp;&nbsp; "'"&amp; PARAMETER_LIST&nbsp; &amp;"'",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Source = Oracle.Database("XXX [HierarchicalNavigation=true, Query="Select * from xxx join xxx on bbb=aaa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where column ='"&amp; PARAMETER_LIST &amp;"'"])in&nbsp;&nbsp;&nbsp; Sourcebut I'm receiving the error like below:DataSource.Error: Oracle: ORA-00933: SQL command not properly endedDetails:DataSourceKind=OracleDataSourcePath=xxxMessage=ORA-00933: SQL command not properly endedErrorCode=-2147467259&nbsp;Without "where" clause Idon't get error.&nbsp;Can anybody advice?&nbsp;

syndicate_admin · Answer

Hi all,I have sth like that for now.let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARAMETER_LIST&nbsp; =&nbsp;if&nbsp;&nbsp;&nbsp; //check to see if the parameter is a list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type.Is(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Value.Type(PARAMETER),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List.Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //if it is a list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //add single quotes around each value in the list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AddSingleQuotes = List.Transform(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARAMETER,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; each "'" &amp; _ &amp; "'"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //then turn it into a comma-delimited list&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DelimitedList = Text.Combine(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AddSingleQuotes,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ","&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; in&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DelimitedList&nbsp;&nbsp;&nbsp; else&nbsp;&nbsp;&nbsp;&nbsp; "'"&amp; PARAMETER_LIST&nbsp; &amp;"'",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Source = Oracle.Database("XXX [HierarchicalNavigation=true, Query="Select * from xxx join xxx on bbb=aaa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where column ='"&amp; PARAMETER_LIST &amp;"'"])in&nbsp;&nbsp;&nbsp; Sourcebut I'm receiving the error like below:DataSource.Error: Oracle: ORA-00933: SQL command not properly endedDetails:DataSourceKind=OracleDataSourcePath=xxxMessage=ORA-00933: SQL command not properly endedErrorCode=-2147467259&nbsp;Without "where" clause Idon't get error.&nbsp;Can anybody advice?

douglasabailey · Answer

This might be too late for you but it could help someone else.

I think in the Where clause you should use IN not '='. And add brackets around the list:

Use: Where column IN ("& PARAMETER_LIST &")

So it would give you: Where column IN ('a','b')

Instead of giving you: Where colum = 'a','b'

Forum Discussion

Multi value in one dynamic M query parameter

4 Replies

Related content

TDS Endpoint multi value parameter PBRS

Dynamic subcription parameter not saving

Sum dynamic parameter fields multiple selected parameter

Dynamic query parameters multi-value

dynamic parameter