Forum Discussion
SQL Select Query in Advanced Editor for a DirectQuery
Hi Anonymous ,
There's a couple of issues here:
1) As Anonymous has noted, SQL statements can't ordinarily be used for Direct Query models as they are not 'foldable'. Due to how DQ works (i.e. it creates SQL queries for each visual in the report) it must be based on Power Query data that can be entirely converted (internally) to an SQL query to begin with.
More information on this here: https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding
You *may* be able to resolve this by using the Value.NativeQuery function in PQ and using the [EnableFolding=true] optional parameter, but I'm honestly not sure if this will work as expected, especially on DB2 which can be rather more finicky that MS SQL Server, for example.
More information on this here: https://learn.microsoft.com/en-us/powerquery-m/value-nativequery
2) Your REPAYMENT1 step isn't referring to a previous step. I would suggest that the DBNAME part here should probably read DB2S, referring to the previous step. This isn't going to fix your folding issue though.
Pete
Hi Anonymous ,
There's a couple of issues here:
1) As Anonymous has noted, SQL statements can't ordinarily be used for Direct Query models as they are not 'foldable'. Due to how DQ works (i.e. it creates SQL queries for each visual in the report) it must be based on Power Query data that can be entirely converted (internally) to an SQL query to begin with.
More information on this here: https://learn.microsoft.com/en-us/power-bi/guidance/power-query-folding
You *may* be able to resolve this by using the Value.NativeQuery function in PQ and using the [EnableFolding=true] optional parameter, but I'm honestly not sure if this will work as expected, especially on DB2 which can be rather more finicky that MS SQL Server, for example.
More information on this here: https://learn.microsoft.com/en-us/powerquery-m/value-nativequery
2) Your REPAYMENT1 step isn't referring to a previous step. I would suggest that the DBNAME part here should probably read DB2S, referring to the previous step. This isn't going to fix your folding issue though.
Pete