Power BI BigQuery – A Step-by-Step Connection Guide 2023
Power BI, a data visualization tool by Microsoft, provides a native connection to a number of databases including MySQL, Amazon Redshift, and BigQuery. The latter is quite popular nowadays due to being a powerful and easy-to-use tool. In this article, we’ll explain how you can integrate Microsoft Power BI and Google BigQuery natively or via a third-party solution.
Power BI connect to BigQuery
Note: the flow described below is only available in Power BI Desktop. Check out our blog post, if you are looking for a way to connect BigQuery to Excel.
- On the Home ribbon in Power BI Desktop, click Get Data and then More….
- In the open window, type “bigquery” into the search bar or select the Database category on the left, then find and select Google BigQuery. Click Connect.
- Now you need to connect BigQuery to Power BI. This can be done in two ways:
- Organizational account – click the Sign in button and follow the usual flow.
- Service Account Login – specify your Service Account email address and JSON key file contents.
Note: When using Service Account, you can create a separate account exactly for Power BI with the needed restricted permissions. In addition, you can always remove/revert keys for Service Account, so you are in more control of access with it. In the case of an Organizational account, you are granting Power BI the same rights you have. This can be excessive if, for example, you are the owner of the entire project.
For the purpose of this setup, we’ve chosen the Organizational account way – clicked the Sign in button and allowed Power BI Desktop to access our Google Account. Once signed in, you’ll see a respective message on the screen.
Click Connect to proceed.
- A Navigator window will appear in which you need to choose a BigQuery Project, a dataset, and a table to load data from.
- The last step of the Power BI BigQuery connector setup is to click Load and choose an importing option:
- Import – to bring a copy of the selected dataset into Power BI
- DirectQuery – to set up a live connection to this dataset
Check out each option’s details to choose the best one for your needs.
Load data from BigQuery to Power BI – Import mode
If you choose Import, the data from BigQuery will be copied to Power BI. You will be able to manipulate it as you wish – change the format, add/remove columns, etc. Here is how it may look in Power BI:
The main drawback of this import mode is that Power BI won’t be synchronized with BigQuery. So, changes in BigQuery won’t be reflected in Power BI.
Load data from BigQuery to Power BI – DirectQuery mode
If you choose DirectQuery, the data from BigQuery will be synchronized with Power BI, but the preview won’t be available.
Nevertheless, now any change in BigQuery records will be updated in Power BI.
Whichever importing option you choose, after you click OK your BigQuery data will be available for visualization in Power BI.
Power BI – BigQuery connection considerations
Can I connect BigQuery to Power BI service?
You cannot connect Power BI service to BigQuery directly. But there is a workaround consisting of the following steps:
- Connect Power BI desktop to BigQuery (the flow above)
- Publish the Power BI dataset
- Get data to Power BI service from the published dataset
Note: Make sure that you’ve signed in to the same Microsoft account both in Power BI desktop and Power BI service.
Publish the Power BI dataset
Once you’ve connected Power BI and BigQuery in the desktop app, you need to click Publish on the Home ribbon.
Then you will be asked to save changes as a .pbix file on your device. After that, select a workspace to publish to Power BI and click Select.
Your dataset will be published to Power BI.
Learn more about how to use Power BI.
Get data to Power BI service from the published dataset
Now you can proceed to Power BI service. Go to Get data => Published datasets.
Select a published dataset and click Create.
And there you go! Your BigQuery dataset is in your workspace in Power BI service.
How to load another dataset from BigQuery to Power BI
If you want to load another table or dataset or project from BigQuery, you need to repeat the main flow:
- On the Home ribbon in Power BI Desktop, click Get Data and then More….
- Select Google BigQuery and click Connect.
- Choose the required project, dataset, and table from BigQuery in the Navigator window and click Load.
- Choose the importing mode.
That’s it – a new dataset will be added to Power BI.
How to reconnect Power BI to another BigQuery account
To switch to another BigQuery account, take the following actions:
- Go to the File menu => Options and settings => Data source settings
- Select “GoogleBigQuery” and click Edit Permissions…
- Click Edit in the open window.
- Click Sign in as a different user in the open window.
Note: If you use Service Account Login, then change the Service Account Email and JSON key file contents, respectively.
After that, sign in to BigQuery using the necessary account and click Save. To load data from Bigquery using the newly connected account, you’ll need to repeat the flow above.
Note: The datasets previously loaded from BigQuery in import mode will be available, whereas the DirectQuery datasets will not be.
Can I transform data before loading from BigQuery to Power BI
Remember the step when you needed to choose a BigQuery project, dataset, and table to load data from. Near the Load button in the Navigator window, you can see the Transform data button as well.
This button allows you to preformat your BigQuery dataset before loading it to Power BI. Click on it, choose the importing mode, and a Power Query Editor window will open where you can add/remove columns, sort values, split columns by values, and many more.
Note: not all formatting options are available in DirectQuery mode.
Once you’re ready with formatting, click either the Save icon in the top left corner or click the Close icon in the top right corner. A window asking you to apply changes will appear. Click Yes (if you clicked the Close icon) or Apply (if you clicked the Save icon).
Then your data will load from BigQuery to Power BI.
Tip: Alternative way to transform data before loading from BigQuery to Power BI
Transform the data directly in BigQuery and save it as a separate data view. You can create a BigQuery dataset called “dashboards” and save all the related views there. This method can speed up the process if you are importing processed data from BigQuery, as it has fewer columns and some rows are filtered. In addition, it’s way more convenient 🙂
The native Power BI BigQuery connector – why you should use it
DirectQuery is the main benefit of the Power BI BigQuery connector. You can keep your data synchronized between the source (BigQuery) and the destination (Power BI). Besides, the connection works smoothly and allows you to move around data pretty fast. With the BigQuery SQL, you can test your initial queries and then load those to Power BI.
Another bonus is that you can import data from multiple sources to BigQuery on a schedule and then load it to Power BI. For example, you can export Google Analytics to BigQuery, and then load it to Power BI.
This is possible due to the Coupler.io tool, which connects BigQuery to Airtable, Xero, Pipedrive, Google Sheets, and many more sources. With this tool, you can also automate BigQuery data exports on a schedule or even connect Google Sheets to Power BI.
The setup flow is pretty simple:
- Set up source (configure where to get data from)
- Set up destination (choose a BigQuery project, dataset, and table to export data to)
- Set up a schedule (automate your data refresh to synchronize your data)
Check out the available BigQuery integrations.
Connect Power BI to BigQuery using third party tools
The native Power BI to BigQuery connector has the following mechanics:
Data is imported from BigQuery and can be transformed in Power Query Editor
Some users would like to customize their queries first, using BigQuery SQL to get the data they need. For such cases, an ODBC connector can be a good option to consider. Here is what this looks like.
- First, you’ll need to download and install an ODBC driver for your source. Mind that ODBC drivers are not free.
- Then you’ll need to configure the driver’s authentication in ODBC Administrator before you can use it for data loading.
- Eventually, you can get back to Power BI, click Get Data and then More… on the Home ribbon. Then, instead of Google BigQuery, select ODBC and click Connect.
In the Data source name drop-down, select your ODBC driver and click Advanced options to expand the SQL statement field. Here you can insert your SQL query.
Click OK to get a preview of the data queried from BigQuery.
Now you can proceed to load the query to Power BI.
To wrap up: native Power BI BigQuery connector or ODBC?
In our experience, using the native connector is more efficient since you don’t need to spend time configuring it compared to the ODBC driver. So, the only reason why you could go with ODBC is the need for loading queries from BigQuery to Power BI. However, you can query your data in BigQuery in separate tables and then load those datasets to Power BI, right? Anyway, choose the option that fits your needs best.
Meanwhile, you can also consider choosing another data visualization tool for your purposes and connect, let’s say, BigQuery to Data Studio. Good luck with your data!
