Forum Discussion
Multiple Excel data source by date in one file
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
You want to do a File Combine operation, but then you'll need to edit the sample transformation query. In Power Query:
- Get Data From Folder
- Select Transform Data
- 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.)
- In the Content column, select the double-down arrow and combine, then select the data in the first file want (Table, sheet, whatever)
- You should now have all of your data, except the column you want with the file name.
- Your query pane will look something like this:
- 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.
3 Replies
You want to do a File Combine operation, but then you'll need to edit the sample transformation query. In Power Query:
- Get Data From Folder
- Select Transform Data
- 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.)
- In the Content column, select the double-down arrow and combine, then select the data in the first file want (Table, sheet, whatever)
- You should now have all of your data, except the column you want with the file name.
- Your query pane will look something like this:
- 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.
Thanks for the responses. They worked for me
