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.

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

Example H2

Example H3

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

Choosing Import mode for multi-gigabyte tables. This inflates .pbix files and slows refresh. Fix by switching to DirectQuery or aggregating data first.
Using user OAuth tokens for scheduled refresh. Tokens expire, causing failures. Instead create a service account or use the Power BI managed OAuth cloud connection.