Import multiple csv files from folder into separate tables based on date conditions

Hi

You can try to do the next way.

1. Create a new Query and name it as "data_current"
2. Add as a source for this query your folder with the csv files
3. Extract the dates from the names to the separate column

Use the next script in M:

= Table.AddColumn(Source, "Date", each

        let

            // Extract the date part of the string

            DatePart = Text.Middle([Name], 5, 8),

            YearPart = Text.Start(DatePart, 4),

            MonthPart = Text.Middle(DatePart, 4, 2),

            DayPart = Text.End(DatePart, 2),

            DateText = MonthPart & "/" & DayPart & "/" & YearPart

        in

            DateText)

4. Change type of column Date to date 🙂

5. Create a list that will contain the maximum value from the Date column.

6. And the last – Filter that row that has value in the Date column equal to MaxDate

Then add separate query “data_prior_day” and do the same but add another one step after calculation a maximum date – calculation the date before maximum.

 

As result you will get two queries with the one table in each: the table with max date in the name in the query "data_current" and the table with date one day before the max date in “data_prior_day”.