Loading multiple files from a folder

   Hey there! 

In this article I will show you how you can load multiple excel files from a folder. This is not incremental load as such but this approach picks up data as and when a new file is uploaded.

Exampe use case: 
A team generally reviews orders of the week in an excel, add comments and uploads in a sharepoint location. 
Files are named as "Book Week X" (Book Week 1, Book week 2)

Power bi requirement: To visualize the values in all these files and pick up new files that are being uploaded every week. 

Solution:

Here I have filtered the files that start with the word "Book". 

If you are not sure how to use advanced editor to do this:

1. Click the filter drop down for column named "Name".

2. Select one of the file names

3. You will have a Filtered Rows step generated at the right pane under "Applied steps"

4. Click on it and look at the formula bar.  Say it has : [Name]="Book1.xlsx". Edit it to Text.Contains formula shown below.

Click on combine and select combine files from the drop down. 

Now select one sample file, load the right sheet. The same sheet will be loaded from all files existing in the folder (that were the result of the previous filter step). As the dataset is refreshed, the new files that match the filter query (that is the files in the folder with the word "Book"), the sheet 1 of these files will be loaded. 

A few steps will get auto added. Click on the unique identifier column and remove duplicates to avoid duplication due to uploading same file twice by mistake in the folder.

The final output will have a column Source.Name auto added with the file from which the row is added. 

Let me know in comments if this article was useful.