Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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])
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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)
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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I assume it's not possbile then
@klausvm
The files you have shared cannot be downloaded, the link expired.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.