How to Connect BigQuery to Power BI

Galaxy Glossary

How do I connect Google BigQuery to Power BI Desktop and Service?

Connect Google BigQuery as a data source in Power BI Desktop or Service for interactive reports.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why link BigQuery with Power BI?

Combine BigQuery’s scalable storage with Power BI’s visual layer to explore large datasets without exporting CSV files.

What are the prerequisites?

Create a Google Cloud project, enable BigQuery API, and ensure a Power BI Pro or Desktop installation. An email with BigQuery data-access permission is required.

How do I connect in Power BI Desktop?

1. Open Home ► Get Data ► More ► Google BigQuery.
2. Sign in with your Google account.
3. Choose the Billing Project, then browse datasets.
4.Select tables, choose Import or DirectQuery, and click Load.

Which connection mode should I pick?

Import caches data inside the .pbix file—best for small tables (<1 GB). DirectQuery leaves data in BigQuery and sends SQL on every interaction—ideal for live, large datasets.

How do I filter data before loading?

Click Transform Data to open Power Query. Use Advanced Editor to add SQL like SELECT * FROM Orders WHERE order_date > current_date - 30.This folds to a BigQuery job, reducing data transfer.

Can I use custom SQL?

Yes. In Navigator, expand a dataset, pick New Source ► Blank Query, then write:
let Source = GoogleBigQuery.Database(null), Orders = Source{[Name="my_project"]}[Data]{[Name="my_dataset"]}[Data], Query = Value.NativeQuery(Orders, "SELECT customer_id, SUM(total_amount) AS revenue FROM Orders GROUP BY customer_id")in Query

How do I refresh data in the Power BI Service?

Publish the .pbix, then add a Google BigQuery data gateway or use the OAuth cloud connection.Schedule refresh up to 8 times/day (Pro) or 48 times/day (Premium).

Best practices for performance

• Prefer DirectQuery for tables >1GB.
• Limit visuals per page to reduce simultaneous queries.
• Aggregate in BigQuery using views or materialized views.
• Partition and cluster BigQuery tables on frequently-filtered columns.

Security considerations

Grant BigQuery’s BigQuery Data Viewer role to the Power BI service account. Revoke unused permissions and enable row-level security through authorized views if needed.

.

Why How to Connect BigQuery to Power BI is important

How to Connect BigQuery to Power BI Example Usage


-- BigQuery view for Power BI DirectQuery
CREATE OR REPLACE VIEW `sales.latest_orders` AS
SELECT o.id,
       o.order_date,
       c.name AS customer_name,
       o.total_amount,
       ARRAY_AGG(STRUCT(p.name, p.price, oi.quantity)) AS items
FROM `Orders` o
JOIN `Customers` c ON c.id = o.customer_id
JOIN `OrderItems` oi ON oi.order_id = o.id
JOIN `Products` p ON p.id = oi.product_id
WHERE o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY o.id, o.order_date, c.name, o.total_amount;

How to Connect BigQuery to Power BI Syntax


Power BI Desktop ► Home ► Get Data ► Google BigQuery [Import|DirectQuery]

Connection string parameters (advanced):
  • BillingProject="billing_project_id"
  • Dataset="my_dataset"
  • SqlStatement="SELECT * FROM Orders WHERE total_amount > 100"

Example M code:
let
  Source = GoogleBigQuery.Database(BillingProject="acme_billing"),
  Orders = Source{[Name="acme_billing"]}[Data]{[Name="sales"]}[Data],
  Filtered = Value.NativeQuery(Orders,
    "SELECT o.id, o.order_date, c.name, o.total_amount FROM Orders o JOIN Customers c ON o.customer_id = c.id WHERE o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)")
in
  Filtered

Common Mistakes

Frequently Asked Questions (FAQs)

Does DirectQuery cost more in BigQuery?

Yes. Each visual run triggers SQL and charges on processed bytes. Cache visuals and use aggregated views to cut cost.

Can I parameterize SQL in Power Query?

Create a text parameter in Power Query, then reference it inside Value.NativeQuery(). Publish to Power BI and set a default in the Service.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.