Google BigQuery
Power BI is a powerful business intelligence tool that provides insights into data through interactive visualizations and reports. Power Query is one of the most important components of Power BI that allows users to connect to various data sources and transform the data for analysis.
Google BigQuery is a cloud-based data warehouse that allows users to store, query, and analyze large datasets in real-time. In this article, we will explore how to connect to the Google BigQuery data source from inside Power BI using Power Query M Language code.
Prerequisites
Before we dive into the code, we need to make sure that we have the following prerequisites:
– A Google Cloud Platform (GCP) account
– Access to Google BigQuery
– Power BI Desktop installed on your computer
Step 1: Creating a Google Cloud Platform Project
To access Google BigQuery, we need to create a Google Cloud Platform project. Here are the steps to create a project:
1. Go to the Google Cloud Console (https://console.cloud.google.com/).
2. Click on the project dropdown menu at the top of the page and click on the “New Project” button.
3. Enter a unique name for your project and click on the “Create” button.
4. Once the project is created, select it from the project dropdown menu.
Step 2: Enabling the BigQuery API
After creating the project, we need to enable the BigQuery API. Here are the steps to enable the API:
1. Go to the GCP dashboard and select your project.
2. Click on the hamburger menu at the top left corner of the page and select “APIs & Services” > “Dashboard”.
3. Click on the “+ ENABLE APIS AND SERVICES” button.
4. Search for “BigQuery” and select the “BigQuery API” from the search results.
5. Click on the “ENABLE” button.
Step 3: Creating a Service Account
To access Google BigQuery from Power BI, we need to create a service account. Here are the steps to create a service account:
1. Go to the GCP dashboard and select your project.
2. Click on the hamburger menu at the top left corner of the page and select “IAM & Admin” > “Service Accounts”.
3. Click on the “+ CREATE SERVICE ACCOUNT” button.
4. Enter a unique name for the service account and click on the “CREATE” button.
5. Select “BigQuery User” as the role for the service account.
6. Click on the “CREATE KEY” button and select the JSON format.
7. Save the JSON file to your computer.
Step 4: Creating a BigQuery Data Source in Power BI
Now that we have created a service account, we can create a BigQuery data source in Power BI. Here are the steps to create a data source:
1. Open Power BI Desktop.
2. Click on the “Home” tab and select “Get Data” > “More”.
3. Search for “Google BigQuery” and select it from the search results.
4. Enter the Project ID of your GCP project.
5. Click on the “Sign in” button and enter your service account credentials.
6. Select the dataset that you want to connect to and click on the “Connect” button.
Step 5: Writing Power Query M Language Code
Now that we have created a data source, we can use Power Query M Language code to transform the data. Here is an example code that retrieves data from a BigQuery table:
let
Source = GoogleBigQuery.Database(“project-id”, “dataset-id”),
Table = Source{[Name=”table-name”]}[Data]
in
Table
In this code, we first create a source by calling the GoogleBigQuery.Database function with the project ID and dataset ID as parameters. We then select the table that we want to retrieve data from by calling the Source{[Name=”table-name”]}[Data] expression.
Conclusion
In this article, we have explored how to connect to the Google BigQuery data source from inside Power BI using Power Query M Language code. We have also seen how to create a Google Cloud Platform project, enable the BigQuery API, create a service account, and create a BigQuery data source in Power BI. By following these steps, you can easily retrieve and transform data from Google BigQuery for analysis in Power BI.
