Dynamic SQL where the whole where clause is given by one parameter

[LaMa]

Hi there,

I am trying to build a direct query that uses input from a parameter. This parameter essentially dictates the entire where clause for the dynamic SQL. The query runs without any issues in the query editor, however, when I try to apply the query in Power BI Desktop i get the following error. Please keep in mind that the where clause could filter a lot of columns, e.g. Company = XYZ and Date = 2023-01-31 and ProductClass = Bikes and SalesArea = US..

Failed to save modifications to the server. Error returned: 'DirectQuery partition 'XYZ-1cd1bc8f-4f60-4d50-b7b5-09fc804bf4a1' has '2' datasource reference(s) in its expression which is not allowed. '.

My query looks like this, where ClauseIDParameter is the parameter sourced from the table where_clauses, which ideally should filter the data coming from Data_Table.

let
Source = Sql.Database("<Server>", "<DB>"),
where_clauses = Source{[Schema="dbo",Item="Where_Clauses"]}[Data],
#"Filtered Rows" = Table.SelectRows(where_clauses, each [Clause_id] = ClauseIDParameter),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Clause_String"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Clause_String", type text}}),
#"WhereFilter" = #"Changed Type"{0}[Clause_String],
SQLQuery = "SELECT * FROM Data_Table where 1=1 " & #"WhereFilter",
SQLSource = Sql.Database("<Server>", "<DB>,
[Query =
SQLQuery
])

in
SQLSource

Thank you,

[amitchandak]

@LaMa , as there is no space

SQLQuery = "SELECT * FROM Data_Table where 1=1 " & WhereFilter ,

Also, check Dynamic M parameters

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[LaMa]

Hi amitchandak, thank you for the reply.

In the soltution you're proposing I don't see the difference compared to my query above?

I have already looked at this forum and in the Microsoft documentation for answers. Unfortunately, without any success.

[amitchandak]

@LaMa , can you share what is there in where_clauses  I need to check out

[LaMa]

Hi again amitchandak,

Below is an example of what the table with where clauses might look like. In our actual case there might be more than 20 conditions in a where clause.

Clause_id	Clause_String
1	and [CompanyName] = 'Microsoft' and [Date] = '2023-01-01'
2	and [CompanyName] in ('Microsoft', 'IBM') and [Date] = '2023-02-01'
3	and [Industry] = 'Logistic' and [Date] between '2023-01-01' and '2023-04-01'

As such, based on the where clause_id which users would choose as a parameter, we would want the 'main' SQLquery to be filter by this selection.