desktop Power Query

[HFT]

I have a report that retrieves data through an API using a dynamic token that expires every hour. The dataset is quite large (over 500 MB), and during the data retrieval process, the token sometimes expires midway, resulting in the error Couldn’t connect with the provided credentials.

I’d like to know if Power Query can automatically request a new token once the old one expires during the data load. Also, does it normally take as long as 3–4 hours to fetch around 500 MB of data in Power Query?

[v-pnaroju-msft · Accepted solution]

Hi HFT,

Thank you for the follow up.

Based on my understanding, the slow refresh issue is primarily caused by storing and processing large volumes of consultant data through API embedding without an optimized storage layer. Power Query or PPU handles large datasets less efficiently without dedicated Fabric capacity or a high performance data source such as ADLS Gen2 or Blob Storage.

As SQL is not an option, please consider the following supported non‑SQL storage alternatives for improved performance:

1. Azure Data Lake Storage Gen2 which supports large datasets, high-performance Parquet or Delta formats, and seamless integration with Power BI and Fabric.
2. Azure Blob Storage which is a suitable alternative if ADLS Gen2 is not available.Use Parquet format for faster reads.
3. Dataverse which is appropriate for managed relational data storage when operating within your organisation’s Power Platform environment.
4. Dataflow Gen2 which requires Fabric capacity (F‑SKU). Power BI Pro Per User (PPU) alone does not include Fabric capacity.
5. Classic Dataflows (Gen1) which remain usable under PPU, but performance and features such as Direct Lake and advanced refresh are limited.

If possible, request Fabric capacity or use a trial to leverage Dataflow Gen2 with ADLS Gen2 storage for faster and more reliable refreshes. If Fabric capacity is not available, use Azure Blob Storage or ADLS Gen2 with Classic Dataflows or a direct file connection.

Please refer to the links below for further information:
Azure Data Lake Storage Gen2 - Power Query | Microsoft Learn
Azure Blob Storage - Power Query | Microsoft Learn
Create a Power BI report using the Microsoft Dataverse connector - Power Apps | Microsoft Learn
Understand Microsoft Fabric Licenses - Microsoft Fabric | Microsoft Learn
Configuring dataflow storage to use Azure Data Lake Gen 2 - Power BI | Microsoft Learn
Power Query Parquet connector - Power Query | Microsoft Learn
Dataflows best practices - Power BI | Microsoft Learn

We hope this information helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi HFT,

We would like to follow up and check whether the details we shared have helped to resolve your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi HFT,

Thank you for the follow up.

Based on my understanding, the slow refresh issue is primarily caused by storing and processing large volumes of consultant data through API embedding without an optimized storage layer. Power Query or PPU handles large datasets less efficiently without dedicated Fabric capacity or a high performance data source such as ADLS Gen2 or Blob Storage.

As SQL is not an option, please consider the following supported non‑SQL storage alternatives for improved performance:

1. Azure Data Lake Storage Gen2 which supports large datasets, high-performance Parquet or Delta formats, and seamless integration with Power BI and Fabric.
2. Azure Blob Storage which is a suitable alternative if ADLS Gen2 is not available.Use Parquet format for faster reads.
3. Dataverse which is appropriate for managed relational data storage when operating within your organisation’s Power Platform environment.
4. Dataflow Gen2 which requires Fabric capacity (F‑SKU). Power BI Pro Per User (PPU) alone does not include Fabric capacity.
5. Classic Dataflows (Gen1) which remain usable under PPU, but performance and features such as Direct Lake and advanced refresh are limited.

If possible, request Fabric capacity or use a trial to leverage Dataflow Gen2 with ADLS Gen2 storage for faster and more reliable refreshes. If Fabric capacity is not available, use Azure Blob Storage or ADLS Gen2 with Classic Dataflows or a direct file connection.

Please refer to the links below for further information:
Azure Data Lake Storage Gen2 - Power Query | Microsoft Learn
Azure Blob Storage - Power Query | Microsoft Learn
Create a Power BI report using the Microsoft Dataverse connector - Power Apps | Microsoft Learn
Understand Microsoft Fabric Licenses - Microsoft Fabric | Microsoft Learn
Configuring dataflow storage to use Azure Data Lake Gen 2 - Power BI | Microsoft Learn
Power Query Parquet connector - Power Query | Microsoft Learn
Dataflows best practices - Power BI | Microsoft Learn

We hope this information helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[HFT]

Thanks. My current report uses Embedding for a large volume of consultant data, causing slow refreshes. Given company constraints prevent SQL use, what non-SQL data source options are available for storing this data to enable faster, more efficient report refreshes? I am using PPU Embed license and when i check for datafows it is asking for fabric capacity, should I have that or without that also datafloes work.

[v-pnaroju-msft]

Thankyou, @andrewsommer, for your response.

Hi HFT,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

In addition to the response provided by @andrewsommer , please follow the steps below, they may help resolve the issue.

1. Power Query currently does not support mid refresh token renewal. You must obtain a new token before each API call or use a method that handles token refresh outside Power Query. Implement token retrieval via a custom Power Query connector.
2. Optimise data loads by using incremental refresh, paging, and query folding, and consider using dataflows to stage large datasets.

For further information, please refer to the links below:
Handling authentication for Power Query connectors - Power Query | Microsoft Learn
Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...
Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn
Dataflows best practices - Power BI | Microsoft Learn

We hope the information provided helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[andrewsommer]

Power Query (in both Power BI Desktop and the Service) does not automatically refresh OAuth or API tokens mid-load once the query execution begins. It treats the token (or credentials) provided at the start of the query as static throughout that refresh session.  There is probably a way to work around this and hopefully another member of this board can help with that.

As far as the refersh time, no, that’s unusually slow.  A variety of things could be slowing it down:

• API throttling
• Complex transformations
• Using shared capacity instead of premium

You can try to batch your api calls, incremental refresh or split up your load and transformations (brinze level flow to btring the data into the service and then sliver layer that transforms it).