How to Connect to a Google BigQuery Database in Power BI Desktop

Posted on by Summa Lai

In Power BI Desktop, you can connect to a Google BigQuery database and use the underlying data just like any other data source in Power BI Desktop.

Connect to Google BigQuery

To connect to a Google BigQuery database select Get Data from the Home ribbon in Power BI Desktop. Select Database from the categories on the left, and you see Google BigQuery.

Get Data dialog for Google BigQuery

In the Google BigQuery window that appears, sign in to your Google BigQuery account and select Connect.

Sign in to Google BigQuery

When you’re signed in, you see the following window indicated you’ve been authenticated.

Signed in to Google

Once you successfully connect, a Navigator window appears and displays the data available on the server, from which you can select one or multiple elements to import and use in Power BI Desktop.

Data from Google BigQuery

Considerations and Limitations

There are a few limits and considerations to keep in mind with the Google BigQuery connector:

  • The Google BigQuery connector is available in Power BI Desktop and in the Power BI service. In the Power BI service, the connector can be accessed using the Cloud-to-Cloud connection from Power BI to Google BigQuery.
  • You can use Power BI with the Google BigQuery Billing Project. By default, Power BI uses the first project from the list returned for the user.To customize the behavior of the Billing Project when you use it with Power BI, specify the following option in the underlying M in the Source step, which can be customized by using Power Query Editor in Power BI Desktop:CopySource = GoogleBigQuery.Database([BillingProject="Include-Billing-Project-Id-Here"]) Google BigQuery Storage API is enabled by default and is controlled by the optional boolean argument called “UseStorageApi”. Some customers might encounter issues with this feature if they use granular permissions. In this scenario, you might see the following error message:ERROR [HY000] [Microsoft][BigQuery] (131) Unable to authenticate with Google BigQuery Storage API. Check your account permissionsYou can resolve this issue by adjusting the user permissions for Storage API. Assign these Storage API permissions:
    • bigquery.readsessions.create – Creates a new read session via the BigQuery Storage API.
    • bigquery.readsessions.getData – Reads data from a read session via the BigQuery Storage API.
    • bigquery.readsessions.update – Updates a read session via the BigQuery Storage API.
    These permissions typically are provided in the BigQuery.User role. For more information, see Google BigQuery Predefined roles and permissions.Beginning with the April 2021 release of Power BI, if you do not have adequate permissions, you will see zero rows returned from queries. Make sure the specific permissions described previously are set properly on the account being used, to enabling refreshing a report or retrieving data from any tables.If the above steps do not resolve the problem or if you want to disable the support for Storage API, change your query to the following:CopySource = GoogleBigQuery.Database([UseStorageApi=false]) Or if you are already using a billing project, change the query to the following:CopySource = GoogleBigQuery.Database([BillingProject="Include-Billing-Project-Id-Here", UseStorageApi=false

Ref: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-bigquery