M (Power Query) in Power BI, passing params from a list to SQL statement

Question

I have an Oracle database with 1000s of projects in it and millions of detail records. I only need to query about 6 Projects at a time. I am trying to write an M expression to pass the 5 or 6 Projects I have in one table(called "Projects"), to my query as a Where clause. The query looks good, perfect SQL syntax, but errors. I am using a function...fnGetProjects see below.

() =>
let
    Source = "'" & Text.Combine(Projects,  "', '") & "'"
in
    Source

This is my query..

let
    WhereClause = fnGetProjects(),
    Source = Oracle.Database("gtmb", [HierarchicalNavigation=true, Query="SELECT  p.Proj_ID, p.Proj_Description, p.Proj_Name FROM Project_YTD p where p.Proj_ID IN ("& WhereClause &")"])
in
    Source

Here is my error message...

Formula.Firewall: Query 'MyProjects (2)' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I suspect it has something to do with precedence... but I thought I read using a function is a work around. I tried this using a table and parameter as well in various forms but no luck. Querying all projects makes my end users wait way too long.

Any help appreciated.

Thanks,

Mike

Have you tried just using "'" & Text.Combine(Projects, "', '") & "'" in the WhereClause step? i.e. WhereClause = "'" & Text.Combine(Projects, "', '") & "'" — Mistella

Tomas Michel Tomas Michel · Accepted Answer · 2020-08-05T18:49:20

Go into file, and in file in the "option and settings".

In there, go into "Data source settings"

Click on "Edit Permissions".

In the edit permissions, select "None" as Privacy level.

It should then work.

M (Power Query) in Power BI, passing params from a list to SQL statement

1 Answers