Error filtering data with SQL expression

[Faberz]

Hi, 

Not sure if this is really PQ since there is a lot of SQL as well.  

I need data to use in a WHERE clause (SQL) and to get this data I have created an "helper table" that then gets turned into a list via steps in PQ.  The format of the list is: 'a-b-c', 'd-e-f', etc.).  I tried it with only 5 elements to make things fast (the full dataset is > than 1M) and it work without problems. 

When I use the full dataset I get the following error: 

DataSource.Error: Microsoft SQL: Parse error at line: 273, column: 7191034: Incorrect syntax near 'x'.
Details:
DataSourceKind=SQL
DataSourcePath=sql-biprod.database.windows.net;sqldb-biprod-synapse
Message=Parse error at line: 273, column: 7191034: Incorrect syntax near 'x'.
ErrorCode=-2146232060
Number=103010
Class=16
State=1

That looks like an error in the SQL code BUT as explained above the exact same code works when the filtering list has only 5 element. 

I tried to look for the error but I could not find it. 

Any help/suggestion is appreciated.

[Faberz]

Hi Ibendlin, 

There was an issue with a double '' in the list that was breaking the code. I removed it and made some optimization in the SQL code (much less M involved) and it works now. 

Thank you for your reply and the time you spent helping.

[lbendlin]

Check the query logs on the SQL side and post a sanitized version of the failing query.