How to parameterize an odbc.query with a parameter restricted to a column from the same sql source ?

[Anonymous]

Hello,

I have defined a Power Query parameter called "MyParameter" whose values are restricted to a list "ListOfPossiblesValues" obtained by quering a table from a PostgreSQL database : 

Odbc.Query("dsn=MY-PostgreSQL-DB", "SELECT DISTINCT my_column FROM my_schema.my_table")[my_column]

I then want to execute a parameterize query to the same PostgreSQL data source to obtain the records that match "MyParameter" :

Odbc.Query("dsn=MY-PostgreSQL-DB", "SELECT * FROM my_shema.my_table WHERE my_column = '"&MyParameter&"'")

But whenever I try it I get the following error : "Formula.Firewall: Query "MyQuery" (step "Source") references other queries or steps and therefore cannot access a data source directly. Reconstruct this data combination."

I assume this is due to the fact that queries are executed in parallel so my second query can not use "MyParameter" since it is not defined when Power BI loads all the data.

I also tried to make a reference to "MyParameter" :

- "MyParameter" value beacome "Source"

- then I use a similar query with "Source" instead of "MyParameter" : Odbc.Query("dsn=MY-PostgreSQL-DB", "SELECT * FROM my_shema.my_table WHERE my_column = '"&Source&"'")

But I have the same error.

Does anyone have some insights for my use case ? 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Dynamic M query parameters feature is in preview. Please refer to offical blog for more details about Considerations and limitations.

• A single parameter cannot be bound to multiple fields nor vice-versa.

• Aggregations are not supported with the feature

• Row-level security (RLS) is not supported with the feature

• ...

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.