Power BI incremental refresh from Shopcada store using API

[Perfecta]

I'm trying to implement incremental refresh in Power BI for order data(around 2 lakh) from my store(shopcada). My goal is to load all historical data up to today and then refresh only the incremental data daily, keeping the historical data unchanged.

However, after publishing the data to Power BI Service, the refresh doesn't complete—it fails after running for about 5 hours. In the incremental refresh settings, I see an error stating:
"Unable to confirm if the M query can be folded."

Can someone help me understand why this is happening and how to fix it?

[v-ssriganesh]

Hello @Perfecta,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @Perfecta,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[PawelWrona]

Hello,

Please share more details regarding the setup. Can you please show screenshot from Power Query where you do the setup?

[m4ni]

Please note feedback from  @v-ssriganesh.

The data source must be able to support incremental refresh.  Usually this is a relational database (SQL, Oracle etc).

Other data sources such as API, Excel would not support incremental refresh as you source must be always online and running.

The way to tell in Power Query is shown in the screenshot below:

Right click the steps (each one if you have many) and look for "View Native Query".  As you can see for me this is greyed out.  This means that step is unable to be sent back to the data source (folded).

If "View Native Query" is avaiable then you can click to the see the underlying SQL code which gets sent back.

Summary - if the above option is greyed out, verify your source is an active relational database.

If  not greyed out then your incremental refresh should work.  Just go through all the steps because even with relational database, some transformations and steps cant be sent back to the source. HTH.

[Perfecta]

Sharing refresh history details too

[v-ssriganesh]

Hi @Perfecta,
Thank you for reaching out to the Microsoft fabric community forum.

The error message: "The column 'created_at' of the table wasn't found" indicates that the refresh is failing due to the absence of a required column during part of the process. This is especially important in the context of incremental refresh, where consistent column structure is critical.

Since you're connecting to an API source, it's important to note that such sources typically don’t support query folding, which incremental refresh depends on to optimize filtering logic. In these cases, schema reliability and data consistency across all refresh windows become even more important.

To improve refresh reliability and scalability, we recommend using a staging layer such as a Dataflow or a Lakehouse table to standardize your data before loading it into Power BI for incremental refresh. This approach also provides better control over schema consistency and helps isolate issues related to API behaviour.

Thank you, @m4ni for sharing your valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[m4ni]

Hi @Perfecta 

It may be worth double checking if any of your PowerQuery steps are breaking the fold. 

Move through the steps one at a time and verify the native query option is available.

Its tricky to comment without visibility but it could be a merge or transformation which is not liked by the source.

Please do clarify the data source is relational and can support incremental / folding operations.

Link to further details around this - Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn