Problem with Native Query queries - We're sorry, an error occurred during evaluation

[Jordi3Rs]

Hi,

I am using pqnet for a while now and it is an excellent tool to help me automate some ETL processes. However, sometimes I have experienced some issues with some queries with an error "We're sorry, an error occurred during evaluation" and I have not been able to figure out why as the same query was working in PBI or Excel. By changing my queries a bit I was able to resolve most of them with those issues but honestly didn't know why.

Recently I am optimizing some of my ETL queries that retrieve information from SQL Server and I realized that there is something in common for all the queries with the above mentioned problem.

The optimization process mainly aims to ensuring Native Queries instead of loading the data into the in-memory DB. In this context I could observe that any query that retrieves data in Native Query mode ends up in the above error.

For example, this query works perfectly in PBI and Excel but not in pqnet: let Source = Sql.Databases("(local)\DWH"), Test1 = Source{[Name="Test1"]}[Data], dbo_Test = Test1{[Schema="dbo",Item="Test"]}[Data] in dbo_Test

Just buffering the native query result makes the query to work also in pqnet let Source = Sql.Databases("(local)\DWH"), Test1 = Source{[Name="Test1"]}[Data], dbo_Test = Test1{[Schema="dbo",Item="Test"]}[Data] in Table.Buffer(dbo_Test)

Table.Buffer is just an example of how to fix it, but any step that causes data to be loaded into memory makes the query work.

With this observation in mind, I decided to search the internet about the bug and found that it was present in some versions of Excel and PBI a long time ago, but it was also resolved long ago. My suspicion is that SDK version 1.0.0.16 has this bug and I guess it was resolved in newer versions of the SDK.

Do you have any plans to update the SDK to the latest version? The latest version is 1.0.0.25, released on 8/18/2019, so it seems pretty stable since then.

Thank you, especially for the great tool you have developed. Jordi

[gsimardnet]

Hi @Jordi3Rs,

Lately, several people have raised cases that would be fixed by updating the SDK. For this reason, I will be planning an update soon.

I appreciate the time you took to figure out the cause of this problem. I will let you know when the update is done.

Thank you