Hi there I'm wondering if its possible to load a bunch of Excel files from a folder and filter them by the date created (or the date thats part of the file name). All the Excel files are the same, but they contain multiple sheets with different columns. Not one sheet is the same in each file. So I would be like to add the different sheets to the report and use the date from the file names to filter the data. Is this doable? Thanks alot in advance,Klaus

klausvm When you use Get Data > Folder option to import multiple files, you get the following screen which allows you to filter the files on the dates you require.

Thanks Downy. I know that much. I want to be able to filter the different sheets in the report by the date of the files they come from. There is no date columns in the files, so there is no relationship between the different sheets and files. Do you know what I mean?

Anybody who can help me with a solution? Thanks in advance

klausvm If you need to identify each sheet and its data with the file Created Date which you want to use as filters for your data in the report, you can add a custom column when you import from a Folder like I showed. Then, you can Once you have done that, expand the Custom column only with Name and Data, The Name contains the Sheet Name. You will have data and related created date from file it came from =Excel.Workbook([Content])

Fowmy Thank you - I already did that. But I then have a list of all the sheets (which now is called table) with each file. I want the data within these tables - I can then create a new query for each, but the filter does not apply to these queries when added to the report. It only shows the query that i expaned. Lets say it's these 3 files. I have multiple sheets in the files with different data. I want to expand that data and have it show in Power BI, but beeing able to filter it by the date of the file. 25-04-2021 - File 1Sheet 1 (4 Columns with data)Sheet 2 (10 Columns with data)Sheet 3 (8 Columns with data) 26-04-2021 - File 2Sheet 1 (4 Columns with data)Sheet 2 (10 Columns with data)Sheet 3 (8 Columns with data) 27-04-2021 - File 3Sheet 1 (4 Columns with data)Sheet 2 (10 Columns with data)Sheet 3 (8 Columns with data)

Filter multiple sheets with different data by the filename

11 Replies

Fowmy
Super User
5 years ago
klausvm

When you use Get Data > Folder option to import multiple files, you get the following screen which allows you to filter the files on the dates you require.
- klausvm
  Frequent Visitor
  5 years ago
  Anybody who can help me with a solution?
  
  Thanks in advance
  - Fowmy
    Super User
    5 years ago
    klausvm
    
    If you need to identify each sheet and its data with the file Created Date which you want to use as filters for your data in the report, you can add a custom column when you import from a Folder like I showed. Then, you can
    
    Once you have done that, expand the Custom column only with Name and Data, The Name contains the Sheet Name. You will have data and related created date from file it came from
    
    =Excel.Workbook([Content])
klausvm
Frequent Visitor
5 years ago
Thanks Downy. I know that much.

I want to be able to filter the different sheets in the report by the date of the files they come from. There is no date columns in the files, so there is no relationship between the different sheets and files.

Do you know what I mean?

Forum Discussion

Filter multiple sheets with different data by the filename

11 Replies

Related content

Dynamic Filename

Difference between quantities from different filename

Date difference with multiple criteria

Data Pipeline copy data filename wilcards

How to avoid people creating multiple times the same table in different workspaces