Snowflake custom SQL fails to load when merged in Power Query with another PowerBI Service dataflow

[Anonymous]

Hello fellow experts!

We're having problems loading data from Snowflake into PowerBI desktop when using custom SQL. I appreciate this is relatively new feature having only just been released in June ( https://powerbi.microsoft.com/en-us/blog/power-bi-june-2021-feature-summary/#snowflake_update )

The problem only seems to occur when we merge the result of the query with another dataflow from the PowerBI Service.

Details/observations so far:

• Loading the results of the custom query we pass to Snowflake directly to the data model works without any issue.
• Additional basic transformations in Power Query (changing data types, splitting columns, multiplication etc.) work correctly and the result can be successfully loaded to the data model.
• Merging the query with a local table created from the "Enter Data" functionality works correctly and the result can be successfully loaded to the data model.
• Merging the query with another dataflow from the PowerBI service results in the following error message:

Additional info. that might be of help:

• I'm starting from a new PowerBI workbook (no existing queries/tables/relationships)
• The Snowflake query is limited to 100 records (for testing) and there're no issues with the preview data.
• Currently using PowerBI desktop version: 2.94.921.0 64-bit (juin 2021)
• The SQL query takes <2 seconds to compute

As the custom SQL functionality is relatively new I'm struggling to find any other examples of potential solutions.

Any insight/help would be much appreciated 🙂

(If any additional information would be useful please let me know)

[Anonymous]

Hi All,

Just to follow up on this topic. It has been raised/escalated to Microsoft on our side, but I would encourage everyone to do so as well if you experience the same issue.

I will lose access to this particular forum account as I leave my current company today - this does not mean I've lost interest in the outcome of the problem, only that I won't know how it progresses internally.

Good luck to everyone 🙂

[devpraha]

@Anonymous have you tried to query the Snowflake database by using an ODBC connector within Dataflows?

As the Dataflow version of the Snowflake connector does NOT support Custom SQL Statements yet (as opposed to the PBI Desktop version which DOES support Custom SQL for Snowflake, see the latest posts here) using ODBC within Dataflows may actually resolve your issue. Note that ODBC connectors also allow you to add Custom SQL Statements.

[Anonymous]

We're talking about mixing existing dataflows and merging them with a custom Snowflake SQL query

[Anonymous]

Hi there, exact same problem here. The fatal combination seems to be Power BI dataflow merged with Snowflake custom query. I haven't found any other thread about it, so glad you reported it. I think submitting a support ticket is the first step to go for.

[Anonymous]

Good point, I hadn't thought of that.

I've just tested this and there're no problems loading the result to the data model. Incidentally, we use dataflows extensively and have never had an issue like this before. I've also tested performing the same action using SQL Server (instead of Snowflake), merging the result, and loading it to the data model - no issues at all.

We've also independently verified the problem with my colleagues in another country using different dataflows etc. and we both have the same problem.

I'm in the process of excalating this wtih Microsoft - fingers crossed.

[V-pazhen-msft]

@Anonymous 

Try merge a local table("enter data" ) with the power bi dataflow, see if the problem comes from a datasource within the dataflow.

Paul Zheng _ Community Support Team