Row filtration while expanding table

Hi M0hamedFazil 

To achieve this, where each file has overlapping data, and you need to filter based on the latest date, you can follow this approach:

Steps:

1. Combine All Files:

   • Use Power Query to combine all files from the folder. This will give you a single table with data from all files, including the repeated dates.

2. Add a Date Column:

   • Ensure each file has a date column (even if it's just inferred from the file name or other metadata).

3. Sort Files by Timestamp:

   • Sort the combined data by file name (timestamp) to ensure the files are ordered by the date of creation.

4. Filtering Logic:

   • For each file (except the first one), you need to filter out rows where the date appears in the previous file.
   • You can achieve this by using Power Query steps to compare the current file's dates against the previous file's dates.

5. Dynamic Date Filtering:

   • After combining the files, you can create a column that marks the latest date for each file.
   • Example approach:
     • Sort the data by file timestamp.
     • Create a custom column to check if the current row's date is the latest date within the context of the file.
     • Use Table.Distinct and Table.SelectRows to remove duplicates based on the date logic, keeping only the latest date for each file.

Example in Power Query:

let
    // Load files from folder
    Source = Folder.Files("C:\\YourFolderPath"),
    // Combine files
    CombinedData = Table.Combine(Source[Content]),
    // Add timestamp column or use file name if needed
    AddTimestamp = Table.AddColumn(CombinedData, "FileTimestamp", each DateTime.FromText(Text.Middle([Name], 0, 19))),
    // Sort by timestamp
    SortedData = Table.Sort(AddTimestamp,{{"FileTimestamp", Order.Ascending}}),
    // Remove duplicates, keeping the latest date from each file
    RemoveDuplicates = Table.Distinct(SortedData, {"DateColumn", "FileTimestamp"})
in
    RemoveDuplicates

Key Points:

• Combine Files: Use Power Query to load and combine all files.
• Date Filtering: Sort by file name (timestamp) and use logic to keep the latest date for each file.
• Dynamic Date Handling: Use Table.Distinct or similar filtering to handle the dynamic nature of the data.

This approach ensures that even as new files are added, only the latest date from each file is included in the final dataset.

Would you like more details on implementing this solution or troubleshooting any step?

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS