Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |