Incremental refresh

[ImSh7m]

Im trying  to implement incremental refresh in Power BI , particularly when dealing with csv files in a local folder .

I am currently working with a dataset where each Excel file is named to encode specific dates. The naming convention is as follows:

• File Name Example: Position-S0007_x+2.csv. Position-S0007_x4.csv.    Position-S0007_x+5.csv.            Position-S0007_x+6.csv ..........Position-S0007_x+11.csv

Interpretation:

• 0007 indicates the 7th month (July).

• x represents the last day of that month.

• x+2 signifies 2 business days after the last day of the month.

Given this structure, I aim to implement an incremental refresh in Power BI that accurately reflects the dates encoded in these filenames. I have already loaded these files into Power BI and combined them into a single table and have applied many steps to my queries to further enhance the table. Also i do not have the view native query option when i right clickon my steps. I'm not sure if this means i can not have query folding or if its possible to fix it. It would be great if you could help me. 

[Anonymous · Accepted solution]

Hi,miguel ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@ImSh7m .I am glad to help you.
In fact, after reading the article provided by miguel, I ran the following test and determined that the CSV file can do what the article says, which is to refresh only the data that is in the refresh interval (incremental refreshing)
This is my test.

(StartDate as datetime, EndDate as datetime) =>
let
  
    // Convert StartDate and EndDate to numbers and calculate the range of years
    start = Number.From(Date.From(StartDate)),
    end = Number.From(Date.From(EndDate)) - 1,
    Years = List.Distinct(List.Transform({start..end}, each Text.From(Date.Year(Date.From(_))))),
// Get the list of files from the SharePoint site
    Source = SharePoint.Files("https://xxx123456789.sharepoint.com/sites/123SiteName", [ApiVersion = 15]),
// Filter the file list to keep only files with folder paths containing the years
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains(List.Transform(Years, (x) => Text.Contains([Folder Path], x)), true)),
// Add a custom column to load the file content as CSV
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content], [Delimiter="	", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])),
// Expand the custom column to display all columns
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}),
// Combine all columns into one column using Tab as the delimiter
    #"Combined Columns" = Table.AddColumn(#"Expanded Custom", "Combined", each Text.Combine({Text.From([Column1]), Text.From([Column2])}, "#(tab)")),
// Select and rename the required columns
    #"Selected Columns" = Table.SelectColumns(#"Combined Columns", {"Combined"}),
// Define an empty table schema
    Schema = #table(type table [Combined = text], {})
in
    // Try to return the combined data, if it fails return the empty table schema
    try #"Selected Columns" otherwise Schema

After executing the function, process the imported csv data

Original data processing completed (here you need to process the original data according to your actual situation)

This incremental refresh connects csv files in multiple folders in sharepoint

The test data:

Setting Incremental Refresh Query Parameters

In my testing, the system warns:
Cannot confirm whether M queries can be collapsed or not, and suggests not to use non-collapsible queries for incremental refresh.
Whether the data source supports query collapsing or not affects the efficiency of the whole refresh execution (the time required for the refresh), and has no impact on the final result of the incremental refresh execution (if the refresh is successful, only part of the data is refreshed)
This is also covered in the issue I share below.

Issues Link:

Solved: Cannot setup incremental refresh parameters - Microsoft Fabric Community
Fixing Query Folding with SQL Server in Power BI for Incremental Refresh – bits2BI

After successfully uploading to the service, configure the data credentials & gateway connection, then refresh the semantic model, this csv file data, will be in the historical interval, but not in the refresh interval of the data for modification can not see whether to perform an incremental refresh, I modified the 2024 data

Replacement of csv file in Sharepoint online (to simulate a change in the data source data)

before refresh:

The refresh result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[miguel]

Trying to create a function that would first interpret the files into dates and times would break the lazy evaluation as you're effectively requesting to read the list of files which is something that should be avoided. It can be an expensive query and not really something that I'd suggest.

The goal would be to pass a filter that fetches only a subset of the files before they are combined. You can do that by either converting the RangeStart and the RangedEnd to a filter that can be directly interpreted by your list of files or follow a simpler approach where you organize the data into folders and subfolders and implement a solution like the one showcased in the link below:
https://www.thepoweruser.com/2020/01/19/incremental-refresh-for-files-in-a-folder-or-sharepoint-powe...

I'm not entirely I sure I follow the naming convention that was described or the granularity in which you wish to create the partitions, but I'd recommend simplifying whereas possible. 

[shm]

thanks for the reply, does this approach work with csv files? i think i dont have query folding, because when i do a Diagnostic , in the data source query collumn i can only see null values , and also i dont have native query option when i right clickon my steps.