Answered

Dataflow in Dataverse with BigQuery - missing SQL statement option

(0) Share

Report

Posted on by IanJB

Hi everyone,

I'm hoping someone can point me in the right direction for using a native SQL query when connecting to Google BigQuery from Dataverse Dataflow. When I set up a connection on Dataverse, I don't have an option to provide a SQL query, only select existing tables/views that are then available individually in Power Query.

Contrast this to a standard SQL server connection, which offers a query input under the Advanced options:

Similarly, if I use BigQuery as my datasource in PowerBI Desktop, I can put in a query (as described here Use advanced power query editor to use SQL stateme... - Microsoft Fabric Community😞

Does anyone know if there is a way to add a native SQL query when using BigQuery via a Dataflow on Dataverse?
Documentation suggests it should be possible (Import data from a database using native database query - Power Query | Microsoft Learn) but I'm not seeing a means to do so when creating a Dataflow.

The BigQuery doc reference suggests this should be available on the web as well (Google BigQuery connector - Power Query | Microsoft Learn)

Am I simply missing something, or am I out of luck on this one?

Thanks!

Categories:

Power Query

I have the same question (0)

All responses (2)

Answers (1)

Sort by

IanJB 93 on at

Like (0)

Report
Copy link

Link copied!

Need to read a little further in the BigQuery connector doc to see that the SQL statement is not supported online:
Bummer 😞

Was this reply helpful? Yes No
Verified answer

IanJB 93 on at

Like (0)

Report
Copy link

Link copied!

Found a workaround. Even though the SQL statement input is not available as part of the connection setup on Web, you can drop in a native query once the connection is made in Power Query. Just create a new blank query and use the following format to get the query to run just as if you had the query input available on Desktop:

Value.NativeQuery(GoogleBigQuery.Database(){[Name="PROJECT NAME HERE"]}[Data], "SELECT STATEMENT HERE", null, [EnableFolding=true])

Was this reply helpful? Yes No