Multi value in one dynamic M query parameter

[lukaszbrdon]

Hi all,

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.

Can you advise me how to achieve? 

[Syndicate_Admin]

Hi all,

let

    PARAMETER_LIST  =

if

    //check to see if the parameter is a list

      Type.Is(

        Value.Type(PARAMETER),

        List.Type

      ) then

        //if it is a list

        let

          //add single quotes around each value in the list

          AddSingleQuotes = List.Transform(

              PARAMETER,

              each "'" & _ & "'"

            ),

          //then turn it into a comma-delimited list

          DelimitedList = Text.Combine(

              AddSingleQuotes,

              ","

            )

        in

          DelimitedList

    else

    "'"& PARAMETER_LIST  &"'",

    Source = Oracle.Database("XXX [HierarchicalNavigation=true, Query="
Select * from xxx join xxx on bbb=aaa

       where column ='"& PARAMETER_LIST &"'"])

in

    Source

but I'm receiving the error like below:

DataSource.Error: Oracle: ORA-00933: SQL command not properly ended
Details:
DataSourceKind=Oracle
DataSourcePath=xxx
Message=ORA-00933: SQL command not properly ended
ErrorCode=-2147467259

Without "where" clause Idon't get error.

Can anybody advice?

[Syndicate_Admin]

Hi all,

I have sth like that for now.

let

    PARAMETER_LIST  =

if

    //check to see if the parameter is a list

      Type.Is(

        Value.Type(PARAMETER),

        List.Type

      ) then

        //if it is a list

        let

          //add single quotes around each value in the list

          AddSingleQuotes = List.Transform(

              PARAMETER,

              each "'" & _ & "'"

            ),

          //then turn it into a comma-delimited list

          DelimitedList = Text.Combine(

              AddSingleQuotes,

              ","

            )

        in

          DelimitedList

    else

    "'"& PARAMETER_LIST  &"'",

    Source = Oracle.Database("XXX [HierarchicalNavigation=true, Query="
Select * from xxx join xxx on bbb=aaa

       where column ='"& PARAMETER_LIST &"'"])

in

    Source

but I'm receiving the error like below:

DataSource.Error: Oracle: ORA-00933: SQL command not properly ended
Details:
DataSourceKind=Oracle
DataSourcePath=xxx
Message=ORA-00933: SQL command not properly ended
ErrorCode=-2147467259

Without "where" clause Idon't get error.

Can anybody advice?

[DouglasABailey]

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'

[Daryl-Lynch-Bzy]

Hi @lukaszbrdon  - have you consider using the List.Contains function to filter the table.  This should fold to the source.  Here is example to consder:

Filter Column by List Items (Exact Match) in Power Query - YouTube