automation of files from a folder

[Jaycena2024]

I want to build a logic to import two csv files (to do analysis on current year vs last year performance)
1. Current Year latest date file i.e, 19 Mar 2024
2. Last Year date file i.e., 19 Mar 2023 or which ever is closer (assuming cases like 19 Mar 2023 is a holiday etc)

I have imported the root folder (contains excel files from 2019 to 2024 with name same as the date they are created) in step 1 (image below)

In step 2, I have filtered out the .ini files so that rows contains only excel files

How should I proceed now to select only two csv from the list, one being the latest one i.e., current date 19 Mar 2024 and one it's counterpart from last year i.e., 19 Mar 2023 so that I can analyze these two files and similarly whenever needed I can select file from current date and it's similar file from last year

[Anonymous · Accepted solution]

Hi @Jaycena2024 ,

Please try below steps:

1. Convert File Names to Dates: Since your files are named after the dates they were created, you'll first need to convert these file names into a date format that Power BI can recognize. This will allow you to perform date-based operations on them.

2. Filter for Specific Dates: Once you have the dates in a recognizable format, you can use Power BI's query editor to filter for the two specific dates you're interested in. You can use custom M code in the Advanced Editor to dynamically calculate the dates for "today" and the same day in the previous year. Here's a simplified example of how you might start to approach this:

let
    Today = DateTime.LocalNow(),
    LastYear = Date.AddYears(Today, -1),
    FilteredRows = Table.SelectRows(YourTableName, each [YourDateColumn] = Today or [YourDateColumn] = LastYear)
in
    FilteredRows

Automate Date Calculation: To ensure that your Power BI report always selects the current date and its counterpart from the previous year without manual intervention, you'll want to incorporate dynamic date calculations. The M code snippet above demonstrates a basic approach to this by using DateTime.LocalNow() to always use the current date as part of the filter criteria.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.