Forum Discussion

amirghaderi's avatar
amirghaderi
Helper IV
6 years ago
Solved

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 s...
Show and Tell
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    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:
    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.

edhans's avatar
edhans
Community Champion
6 years ago

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:
  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's avatar
amirghaderi
Helper IV
6 years ago

Thanks for the responses. They worked for me