Import multiple files from multiple folders, and "refresh" load new file

[dnguyen67]

Is it possible to import multiple files from multiple folders?

Currently, I have all the files ( 365 files in one year) in a folder on One Drive, so it easily loads current files and update new files (when schedule/manually click "refesh data") from that folder on One Drive. Now, I want to load multiple years from different folders as mentioned below.

Here is the example:

• Case: If I want to load the files from year 2021 and year 2022, first I have to access to table_1_folder, then go to folder day_folder, then go to subfolders year_2021_folder and year_2022_folder to load all the files in these two sub folders. Directory structure as below:

                Table_1_folder

                                Day_folder

                                                Year_2019_folder
                                                Year_2020_folder

                                                Year_2021_folder

                                                Year_2022_folder

• Question: Is there a function in the Power Query to access the main folder day_folder, then select one/multiple sub-folders, let’s say, year_2021_folder and year_2022_folder, then load all files from these two sub-folders to a table in Power BI? Then if I schedule “refresh data” daily, power bi will only go to the most recent year folder (year_2022_folder) to get daily new file and update the table?

This is only one table, we have more tables with same directory structure.

I just want to know if it is feasible, or I must combine all folders into one big folder in One Drive? I want to bring this database into Azure cloud database, any recommendations/suggessions are very welcome

Thank you in advance,

[v-jingzhang · Accepted solution]

Hi @dnguyen67 

If the files are on the local drive, you can use Folder connector to connect to the Parent folder. If the files are from an online OneDrive folder or an online SharePoint folder, you can use SharePoint Folder connector to connect to the site. 

After you connecting to it successfully, you will see all files under that path in the preview window like below. Click on "Transform Data" button instead of "Combine Data" button. We need to filter the files in Power Query Editor before combining. 

There is a Folder Path column there. You can click the dropdown icon to expand the menu and only select the subfolder paths that you want to import data from. This step will filter the files listed in the table and only remain files that are needed. Next time when you refresh data, it will only import data from these selected subfolders. 

After that, click the combine icon on Content column header to combine data in remained files. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ManuMMI]

I have this solution 

let
Source = Folder.Files("Your Folder"),
#"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[Date created]) in each [Date created] = latest),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transformar archivo", each #"Transformar archivo"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transformar archivo"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transformar archivo", Table.ColumnNames(#"Transformar archivo"(#"Archivo de ejemplo")))
in
#"Expanded Table Column1"

[dnguyen67]

Based on the Source = Folder.Files("Your Folder"), the table links to 1 table only, so do you know how a table can loads data from 2 folders?

[ManuMMI]

folder.file extract  all file from different folders

[watkinnc]

Yes, you can use Folder.Files.

--Nate

[dnguyen67]

Can you please more specific?  Folder.Files "Returns a table containing a row for each file found in the folder path and all its subfolders", however, I only want to return some of subfolders in the folder? ex. I want to load data in the subfolders 2021_subfolder and 2022_subfolder, not 2019_subfolder and 2020_subfolder in the table_1_folder?