Forum Discussion
Help refreshing excel source files via parameters & filters/slicer
Hello,
Looking for some ideas on how to refresh historical data in a Powerbi Dashboard. Currently the dashboard is refreshed each month, but I am looking to build out a solution to display historical data for previous months data.
The historical data files are in excel sheets, spread across multple folders. Each file is a different month. I am able to add the main folder and filter the list to just the files containing the historical data using power query.
I looked at using custom parameters to update the file name which would allow the data to be refreshed without going through transform. But I need to find a solution that allows the end user to filter between months when the report is published.
I don't want to merge the data as each file(25mb) contains rows of data that has many lines which are duplicated over each month with thousands of additional new lines added.
But I am not sure how to create a relationship between the parameter and a filter/slicer to enable my users to cycle between reports, if that is at all possible.
Thanks in advance
2 Replies
Hi Data_guy_Ty ,
Currently, users cannot switch data source tables directly. You can submit this idea here.
Welcome to Microsoft Fabric Ideas
It might make sense to consolidate your historical data into a single table. Since your data is now consolidated into one table, you can easily build relationships and use Power BI's native slicers to filter data.
If you want to avoid a full refresh every month, you can also consider incremental refreshes via custom functions:
Incremental refresh for files in a Folder or SharePoint
For Dashboard, tiles may come from multiple reports/data sources and you may also need to consider limitations on filtering data:
Solved: How to filter tiles on a dashboard in power BI ser...
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
You can apply your filter over your desired file in the first step after loading files from the folder.
