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

[zenz]

Hi there, 

I want to import multiple csv files in a folder. In the below example, I have 3 csv files in the folder. 

How can I import only 2 of the 3 files based on the date in the file name as 2 separate tables ?

Here is what I want: 

- table 1:  the file with the Max date. So it will the be file, "data_20240619", and rename the table name as "data_current"

- table 2:  the date = 1 day prior to the Max date. So it will be the file, "data_20240618", and rename the table name as "data_prior_day"

- reason: my team will continue to save the most recent data file into the same folder, so as new file arrive, I need Power Bi to grab the latest data file and the file -1 day. 

Thank you so much for your help! 

[zenz]

Thanks, @MaxShema . 

Sorry, I am brand new to Power BI, so need a bit hand holding. Appreciate it! 

in the step #1 Create a new Query and name it as "data_current"

• this is what i did: righ click in the Queries pane (on the left) -> New Query -> More -> Folder (where csv files stored)
• then, on the right side, I have a new "Source" under the Query settings

                  

step #2 Add as a source for this query your folder with the csv files

• what do I need to do for this step

in step #3 Extract the dates from the names to the separate column

• I don't know how to insert a step and get a fomula bar that is only for the step
• I tried to right click in the Query settings, and "insert Step after", but no independent formula bar to allow me to enter codes. 

           

[MaxShema]

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”.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.