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.
The easiest way to connect Power BI to BigQuery
We’ll start with the easiest way to connect the two apps. This method is user-friendly, so you don’t need to spend much time on settings, and it’s also suitable for users with no tech knowledge. We will achieve all this with the help of a partner Power BI connector by Coupler.io.
With Coupler.io, you can load data from BigQuery to Power BI automatically on a schedule. Let’s see how to do this in three steps.
1. Extract your data from BigQuery
To get started, click Proceed in the form below to sign up to Coupler.io for free.
Next, connect your BigQuery account. Specify what data you want to extract by providing an SQL query.
2. Preview and transform data
In the next step, you can preview the dataset that is about to be exported. You can also make edits and perform transformations. For example, you can:
- Hide unnecessary columns and add new ones.
- Rename and rearrange columns.
- Merge or split columns.
- Perform calculations using formulas.
- Sort and filter data.
- Blend data from different accounts or apps into one dataset.
If your data looks correct, click Proceed. Once this is done, follow the instructions to connect the importer to your Power BI account.
3. Schedule updates and manage your data
Toggle on Automatic data refresh and specify your preferences for scheduled updates. You can set the update interval from once per month to every 15 minutes, making your report live.
Finally, click Run Importer. That’s it! You’ve connected BigQuery to Power BI, and you can start working with your data in this data viz tool.
Now, let’s see what other options you have for connecting Power BI and BigQuery.
Connect Power BI to BigQuery with an ODBC connector
As we already mentioned, the native Power BI to BigQuery connector has the following mechanics:
Data is imported from BigQuery and then can be transformed in Power Query Editor
Apart from using Coupler.io (an option we’ve described in the section above), you can also use an ODBC connector. 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.
After this, you can proceed to load the query to Power BI.
And now, let’s see how to connect Power BI and BigQuery with the native connector.
Power BI – connect to BigQuery natively
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.
If you are using Coupler.io for transferring data from BigQuery to Power BI, you can easily transform data in the Preview and transform module. We’ve already described how to do this in the section The easiest way to connect Power BI to BigQuery.
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 – when you should or should not use it
DirectQuery is the main benefit of the native 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 data around pretty fast. With the BigQuery SQL, you can test your initial queries and then load those to Power BI.
At the same time, if you need to import data from other sources to Power BI, you’d better opt for a more versatile solution. Coupler.io allows you to connect 50+ sources to Power BI. With this tool, you can also automate BigQuery data exports on a schedule or even connect Google Sheets to Power BI.
Coupler.io also simplifies channeling data from BigQuery to PowerBI. Data preview, SQL queries, data transformations, and scheduled synchronizations are all available here as well – all wrapped into an intuitive user-friendly interface.
To wrap up: native Power BI BigQuery connector, Coupler.io, or ODBC?
In this guide, we explored three ways to get your data from BigQuery to Power BI. Doing this with Coupler.io is the simplest and most user-friendly method. It allows you to establish an automated connection within minutes, query your data before loading, perform calculations, sort and filter your data – so you get a clean analysis-ready dataset transferred to Power BI.
But, if you prefer that, you can also try other ways of connecting the two apps.
In our experience, using the native connector is more efficient compared to using the ODBC driver since you don’t need to spend time configuring it. 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? And you can also add SQL queries to your importer in Coupler.io, as well as perform data transformations before loading your dataset to Power BI.
Meanwhile, you can also consider choosing another data visualization tool for your purposes and connect, let’s say, BigQuery to Data Studio. Anyway, choose the option that fits your needs best, and good luck with your data!
Automate data export with Coupler.io
Get started for free
