Power BI-Incremental Refresh Issue

[Perfecta]

Hi,

I’m facing an issue related to query folding and data refresh, and would appreciate your guidance.

I'm currently using Power BI Pro (not Premium). In Power BI Desktop, I’m getting a query folding warning. But when I publish the report to the Power BI Service, the refresh fails completely.

I'm suspecting the issue is related to query folding, but I’m not sure how to resolve it under the limitations of the Pro license. I'm happy to share the M code if needed.

Can  help guide me on how to fix this issue or work around it? Any support would be appreciated!

Thank you.

#Mcode:

let
    StartDate = DateTime.ToText(RangeStart, "yyyy-MM-dd"),
    EndDate = DateTime.ToText(RangeEnd, "yyyy-MM-dd"),
    APIKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    PageSize = 100,
    MaxPages = 2000,
    DelaySeconds = 1,
    GetPage = (PageNum as number) =>
        let
            Response = Json.Document(
                Web.Contents("xxxxxxxxxxxxxx", [
                    RelativePath = "xxxxxxxxxxxx",
                    Query = [
                        page = Text.From(PageNum),
                        limit = Text.From(PageSize),
                        created_at_min = StartDate,
                        created_at_max = EndDate
                    ],
                    Headers = [#"xxxxxxxxxxx" = APIKey],
                    ManualStatusHandling = {429}
                ])
            ),
            Status = Value.Metadata(Response)[Response.Status]?,
            Result = if Status = 429 then error "Too Many Requests" else Response,
            Orders = Result[orders],
            Delayed = Function.InvokeAfter(() => Orders, #duration(0, 0, 0, DelaySeconds))
        in
            Delayed,
    PageNumbers = List.Numbers(0, MaxPages),
    AllPages = List.Transform(PageNumbers, each try GetPage(_) otherwise null),
    FilteredPages = List.RemoveNulls(AllPages),
    Combined = List.Combine(FilteredPages),
    ToTable = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(ToTable, "Column1", Record.FieldNames(Combined{0})),
    ExpandedWithDate = Table.TransformColumnTypes(Expanded, {{"created_at", type datetimezone}}),
    AdjustedCreatedAt = Table.AddColumn(ExpandedWithDate, "created_at_adjusted", each DateTimeZone.RemoveZone([created_at]), type datetime),
    FilteredRows = Table.SelectRows(AdjustedCreatedAt, each [created_at_adjusted] >= RangeStart and [created_at_adjusted] < RangeEnd)
in
    FilteredRows

[v-lgarikapat]

Hi @Perfecta ,

Thanks for reaching out to the Microsoft fabric community forum.

@Akash_Varuna 

Thanks for your prompt response

In addition to @Akash_Varuna , I have added learning documents and previously solved threads here. They may help you resolve the issue.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

Solved: query folding error while doing incremental refres... - Microsoft Fabric Community

Solved: Incremental refresh with appended queries - Microsoft Fabric Community

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Solved: Query Folding - Microsoft Fabric Community

Solved: Using web source for incremental refresh - Microsoft Fabric Community

Solved: Incremental Refresh of data coming from Rest API o... - Microsoft Fabric Community

Thanks for reaching out to the Microsoft fabric community forum.

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @Perfecta ,

 If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @Perfecta ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @Perfecta ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the issue, please feel free to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If you found our response helpful, please mark it as Accepted Solution and consider giving a Kudos, so others with similar queries can find it easily.

Best Regards,

Lakshmi Narayana

[Akash_Varuna]

Hi @Perfecta I think it is because the M code uses non-foldable transformations, breaking query folding required for incremental refresh. Modify the code to pass RangeStart and RangeEnd directly to the API and remove unnecessary filtering like Table.SelectRows. Ensure foldable transformations by checking the query folding icon in Power Query.