cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

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:

 

tt_analyst_0-1625496352953.jpeg

 

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)

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

@tt_analyst 

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

Syndicate_Admin
Administrator
Administrator

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.

Syndicate_Admin
Administrator
Administrator

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.

Syndicate_Admin
Administrator
Administrator

@tt_analyst 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.

Syndicate_Admin
Administrator
Administrator

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 🙂

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,157)