cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klausvm
Frequent Visitor

Filter multiple sheets with different data by the filename

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

11 REPLIES 11
klausvm
Frequent Visitor

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?

Fowmy
Super User IV
Super User IV

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

Fowmy_0-1619266675511.png

 



 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

klausvm
Frequent Visitor

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 

Fowmy_0-1619438310924.png

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

Fowmy_1-1619438393625.png

 



=Excel.Workbook([Content])

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

klausvm
Frequent Visitor

 

@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 1
Sheet 1 (4 Columns with data)
Sheet 2 (10 Columns with data)
Sheet 3 (8 Columns with data)

 

26-04-2021 - File 2
Sheet 1 (4 Columns with data)
Sheet 2 (10 Columns with data)
Sheet 3 (8 Columns with data)

 

27-04-2021 - File 3
Sheet 1 (4 Columns with data)
Sheet 2 (10 Columns with data)
Sheet 3 (8 Columns with data)

 

@klausvm 

 

Would it be possible for you to create 2 excel files with simple sample data and show the expected results on a different file?

 

you can share the files link here after saving them in a cloud space like OneDrive. 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

klausvm
Frequent Visitor

I assume it's not possbile then

klausvm
Frequent Visitor

@Fowmy 

Did you get a chance to take a look?

 

Thank you in advance

@klausvm 

The files you have shared cannot be downloaded, the link expired.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

klausvm
Frequent Visitor

@Fowmy Did you get a chance to look at the files?

 

I would love to be able to filter on the filename, because all of the sheets in the files are different formatting.

klausvm
Frequent Visitor

@Fowmy 

I'v made a new upload here: https://we.tl/t-oS89U9ceDi

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors