Multiple Excel data source by date in one file

[amirghaderi]

Hi,

I save a file every week in a folder. So the folder has files saved by week in that folder. I want Power Bi to read all files in the folder and combine them as one table and add a column which shows the data is from which file (week).

Example files in the folder:

File 1 07-Feb-2020    It has Column A/Column B/Column C

File 2 14-Feb-2020    It has Column A/Column B/Column C

File 3 21-Feb-2020    It has Column A/Column B/Column C

Table in Power Bi to be as below:

Column A/Column B/Column C/New column (Date of file):

Column A/Column B/Column C/07-Feb-2020

Column A/Column B/Column C/14-Feb-2020

Column A/Column B/Column C/21-Feb-2020

Thanks,

Amir

[amitchandak · Accepted solution]

Refer, if these can help

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

https://powerbi.tips/2016/06/loading-data-from-folder/

[edhans · Accepted solution]

You want to do a File Combine operation, but then you'll need to edit the sample transformation query. In Power Query:

1. Get Data From Folder
2. Select Transform Data
3. Filter it so it only shows the files you want (assuming it has other things in it. May be able to skip this step if this folder never has other stuff in it.)
4. In the Content column, select the double-down arrow and combine, then select the data in the first file want (Table, sheet, whatever)
5. You should now have all of your data, except the column you want with the file name.
6. Your query pane will look something like this:
   1. 
7. Here is where it gets tricky, as both Excel and Power BI have changed. You need to find the Removed Other Columns step either in the main query (my [OneDrive - eHansaltyics] query) or in the [Transform Sample File] query and make sure the "Source.Name" Column is checked. Newer versions of Power BI and Excel Power Query do this, but as recently as last month, they hid it.

[amirghaderi]

Thanks for the responses. They worked for me

[amitchandak · Accepted solution]

Refer, if these can help

https://insightsoftware.com/blog/power-bi-load-data-from-folder/

https://powerbi.tips/2016/06/loading-data-from-folder/