SQL Select Query in Advanced Editor for a DirectQuery

[ronap]

Hello

I am trying to set up a DirectQuery connection to a DB2 database in Power Query, I am using the IBM DB2 connector

I have a SQL query that performs multiple joins in the database to save me querying multiple tables and merging within Power Query, this works very well on the ODBC connector with an incremental refresh

However, I'm looking to see if I can run this query as a DirectQuery, I was unable to load as DQ with the ODBC connector, hence using the IBM DB2 connector. The issue I'm now having is that I have been unable to add in the SQL query in the same manner as for the ODBC connection - I can imagine this is something I will need to add into the M Language in Advanced Editor.

Does anyone have any example codes as a guide of how to do this? Or is there a better way to do this? As I'd like to avoid querying all necessary tables seperately to then merge into Power Query, as I have the SQL select query just as I need it - I tried adding the select query into the advanced editor as below..

let
   Source = DB2.Database("HOST", "DBNAME", [HierarchicalNavigation=true,           Implementation="Microsoft", Query="SELECT QUERY HERE"]),
   DB2S = Source{[Schema="DBNAME"]}[Data],
   REPAYMENT1 = DBNAME{[Name="REPAYMENT"]}[Data]
in
   REPAYMENT1

However I receive the following error:

Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Schema=DBNAME
Table=[Table]

It looks as though the query has been examined in the first half of the advanced editor but then maybe not assigned properly to the 'Table' as a variable? Any tips or help would be very appreciated!!

Thank you!

[BA_Pete · Accepted solution]

Hi @ronap ,

There's a couple of issues here:

1) As @v-cgao-msft 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

[v-cgao-msft]

Hi @ronap ,

According to the connector documentation, SQL statements cannot be used in DirectQuery data connectivity mode.
Power Query IBM Db2 database connector - Connect to an IBM Db2 database from Power Query Desktop

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group