Slow data loading time on PBI with Bigquery GCP

If you set the year to a datetime, say the first day of the year, then you can use incremental refresh. Then only need to load all once, and just this year for every other refresh 

Hi Rinn,
Thank you for reaching out to the Microsoft fabric community forum. Thank you rohit1991, Deku, for your inputs on this issue.

Thank you for providing the details. Working with large datasets like yours (30M rows × 40 columns) in Power BI Import mode with Big Query can indeed present performance issues.

Since the refresh is taking over 5 hours on both Desktop and in the Power BI Service, the problem is likely due to the volume of data being retrieved and the way Big Query handles full table scans during each refresh.

Use Views in Big Query: Create a SQL view that filters or pre-aggregates your data to reduce the volume imported into Power BI.

Implement Incremental Refresh: This is especially effective for large datasets. It allows Power BI to only refresh new or changed data rather than pulling the entire dataset each time. You'll need a date/timestamp column and to configure RangeStart/RangeEnd parameters.
Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn
Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

Big Query Table Optimization:

• Use partitioned tables (on date fields).
• Use clustered tables to speed up query filtering.
• Avoid SELECT * – only import necessary columns.

Review Network and Gateway Performance (if applicable): If you're using an on-premises gateway, ensure it’s up to date and hosted on a high-performance machine.

Kindly refer to the below link for better understanding:
Google BigQuery connector - Power Query | Microsoft Learn
Data refresh in Power BI - Power BI | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.