Forum Discussion

zenz's avatar
zenz
Frequent Visitor
2 years ago

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

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! 

Need Help
Tips and Tricks

2 Replies

  • MaxShema's avatar
    MaxShema
    Regular Visitor
    2 years ago

    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.

  • zenz's avatar
    zenz
    Frequent Visitor
    2 years ago

    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.