Forum Discussion

WaelTalaat79's avatar
WaelTalaat79
Icon for Helper I rankHelper I
3 years ago
Solved

Data Enable folding for huge 5 years data

Dears,

I prepared a 5 years data and i need to enable the incremental refresh

but i got an message as below

"Unable to confirm if the M query can be folded. it is not recommended to use incremental refresh with non-foldable queries"

 

here below the M steps so how can i modify it to Enable folding

 #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Extension], "txt")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "Purchase performance")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type text}, {"Month", type text}, {"Supplier Code", type text}, {"Supplier Name", type text}, {"Supplier Type", type text}, {"Store Code Name", type text}, {"Dept", type text}, {"Section Code", type text}, {"Section Name", type text}, {"Family Code Name", type text}, {"Subfamily Code Name", type text}, {"Brand Code", type text}, {"Brand Name", type text}, {"PG Code", type text}, {"PG Name", type text}, {"Item Code", type text}, {"Item Name", type text}, {"Dc Supplier Flag", type text}, {"Item Barcode", type text}, {"VAT Code", type text}, {"VAT Rate", Percentage.Type}, {"Invoice No", type text}, {"LPO NO", type text}, {"LPO Creation Date", type text}, {"LPO Delivery Date", type text}, {"Invoice Amt Without Tax", Int64.Type}, {"Invoice Amt With Tax", Int64.Type}, {"Invoice Qty", Int64.Type}}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type", {{"LPO Creation Date", type date}}, "en-GB"),
#"Changed Type with Locale2" = Table.TransformColumnTypes(#"Changed Type with Locale1", {{"LPO Delivery Date", type date}}, "en-GB"),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type with Locale2",{"Source.Name"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each ([Section Code] <> "" and [Section Code] <> "Section Code")),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Filtered Rows2", {{"Date", type datetime}}, "en-GB"),
#"Filtered Rows3" = Table.SelectRows(#"Changed Type with Locale", each [Date] >= RangeStart and [Date] < RangeEnd)
in
#"Filtered Rows3"

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi WaelTalaat79 ,

    What is your data source? Query folding is generally available on structured data sources.

     

    Because support for query folding is different for different types of data sources, verification should be performed to ensure the filter logic is included in the queries being executed against the data source. In most cases, Power BI Desktop attempts to perform this verification for you when defining the incremental refresh policy. For SQL based data sources such as SQL Database, Azure Synapse, Oracle, and Teradata, this verification is reliable. However, other data sources may be unable to verify without tracing the queries. If Power BI Desktop is unable to confirm, a warning is shown in the Incremental refresh policy configuration dialog.

    If you see this warning and want to verify the necessary query folding is occurring, use the Power Query Diagnostics feature or trace queries by using a tool supported by the data source, like SQL Profiler. If query folding is not occurring, verify the filter logic is included in the query being passed to the data source. If not, it's likely the query includes a transformation that prevents folding.

    Before configuring your incremental refresh solution, be sure to thoroughly read and understand Query folding guidance in Power BI Desktop and Power Query query folding. These articles can help you determine if your data source and queries support query folding.

    Please refer to:
    Incremental refresh for datasets and real-time data in Power BI

    Best Regards,
    Gao

    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • WaelTalaat79's avatar
      WaelTalaat79
      Icon for Helper I rankHelper I

      Dear,
      thanks for your reply, 
      the data sourcing is an CSV + Excel file reading from sharepoint & onedrive
      and when i checked the sequence data report steps the enable folding is desapled from "Source" step

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi WaelTalaat79 ,

        //the data sourcing is an CSV + Excel file reading from sharepoint & onedrive.
        The reason is here. They are both flat data sources and they do not support query folding. Consider importing this data into a data source that supports query folding before reconfiguring incremental refresh.

        Best Regards,
        Gao

        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly -- How to provide sample data