Row filtration while expanding table

[M0hamedFazil]

Hi Community,

I have a requirement where I have to pull files from the folder and combine all the files into Source. There are contents of previous file in each file. For instance, If I have data for dates 1-5 on file1, file2 will have contents of 2-6, and file3 will have 3-7 and so on.... the file name will be the timestamp. Since there is no unique column, I planned to sort the files by file name and use filtering only the lastest date of the file for other files except the first file wile combining. I struck on filtering the date as the dates will be dynamic as the number of files added will increase. Is there any idea for filtering based on date. Thanks in advance.

Regards

Fazil M

[Poojara_D12]

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 

[M0hamedFazil]

Hi @Poojara_D12 

Thanks for the reply. I have tried @wdx223_Daniel method and got the answer I expected. I'll try this and once worked I'll let you know.

Regards
Fazil M

[wdx223_Daniel · Accepted solution]

1, combine all the data without any filters and keep the filenames

2, add a new step with this code

 =Table.Combine(Table.Group(Source,"date",{"n",each List.Last(Table.Group(_,"filename",{"t",each _})[t])})[n])

[M0hamedFazil]

Hi @wdx223_Daniel 

I have Tried this and this works perfectly. Thanks 

Regards
Fazil M