Setting Dynamic Path and Appending multiple excel/csv files in Power BI

Hello! There are many users who have to keep on appending a new excel/csv file every month on month based on data availability. Every time a user has to source the new excel file and upload it in the Power BI desktop and later append in the final table in which they are collating the data. Or even few users would be doing it manually outside Power BI and creating a collated file every month and would have been uploading the file with latest month data

The solution here I am discussing would reduce the manual scrambling of the data. The only prerequisite is that the structure of the data file must be same (and that's how the appending can be successful) i.e. number of columns should be same and column headers must be same. Also the file name must have either same prefix or suffix! This is an important rule though

So prerequisite:

1. Data structure must be same i.e. same column count/ column name

2. File name must have same prefix/suffix (example in below image)

In the below example, as you can see we have a folder which consists of our files. currently we are in Feb month and once the data for March comes in, we could see another file with the name Mar_sales_2022. One thing that you must observe is that monthly sales file would be different however the suffix and file pattern has a common identity i.e. _sales_2022. Hence all prerequisite meeting up

Folder with multiple files

Next Step

In the Power BI desktop, we need to create a parameter first where we will define file path. At this location, we are going to keep all our future files. It can be your one drive or shared folder as well

Create Parameter

Once the parameter is set, then we have to write M query language in the power query editor window (refer the below image)

Writing M language in Query Editor

In the query editor, please use below code to create the data linkage between files and Power BI

Uploading Multiple Files At Once

The next step is to expand the column which says Binary. Click ok without doing any changes here

Append Multiple files with same structure

One can see here a source.name column is created which is showing Jan as well as Feb data appended in one single query. That's it! You close and apply and then see the magic in next screen

Appending Multiple Files in One Go

There are two files in my location set by parameter hence two files got loaded and appended in one single shot. As soon as the new March file comes and sits in the folder, upon PBIX refresh, the new data will start reflecting without my intervention to load or append the new file! Isn't that exciting and helpful?

I tested my logic here by simply replicating the Feb file (it also has sales_2022 in its name) and hence on refreshing the data, new file get added automatically

Hope this will help in your project! Thanks