Forum Discussion
How do I get PowerBI to refresh to updated Excel document in a file?
Hi Anonymous
It's best not to link Power BI to just a single Excel file. Instead, connect it to a folder containing all versions of your Excel files for greater flexibility and efficiency.Here’s how you can do this in Power BI Desktop:
Select "Folder" as Your Data Source
Go to Home > Get Data > Folder in Power BI Desktop.
Pick the folder where your weekly or monthly Excel files are stored.
Filter for the Latest File
Power BI will show metadata for all files in the folder.
In Power Query, sort by “Date Modified” or extract the date from filenames.
Use Keep Top Rows = 1 to select only the newest file.
Combine and Load Data
Expand and extract the sheet or table from the latest file.
Load this data into your Power BI model.
Seamless Refreshes
Just add the new Excel file to the folder for each update.
Power BI will always use the latest file when you refresh.check out these Microsoft Docs:
https://learn.microsoft.com/en-us/power-query/combine-files-overview
Power Query Folder connector - Power Query | Microsoft Learn
Regards,
Sreeteja.
Super UserAnonymous
Yes, it is possible to set up Power BI Desktop to automatically refresh and link to the most recent dated Excel file. Here are the steps
Folder Connection: Instead of linking directly to a single Excel file, you can link Power BI to a folder where all the Excel files are stored. Power BI can then be set up to read the latest file from that folder.
Combine Files: Use Power BI's "Combine Files" feature to combine data from all Excel files in the folder. You can then filter or sort the data to ensure you are using the most recent file.
Scheduled Refresh: Set up a scheduled refresh in Power BI Service to ensure that the data is updated regularly.
Hi, how do I do these three steps?
Thank you!
- bhanu_gautam
Anonymous
Step 1: Connect to a Folder
Open Power BI Desktop.
Go to Home > Get Data > Folder.
Browse to the folder where your Excel files are stored and click OK.
Click Combine > Combine & Load.
Step 2: Combine Files
In the Combine Files dialog, select the sheet or table you want to combine from each file.
Power BI will create a query that combines all the files in the folder.
In the Power Query Editor, you can add a custom column to extract the date from the file name if your files are named with dates (e.g., 2023-01-01.xlsx).
Step 3: Filter for the Most Recent File
In the Power Query Editor, sort the files by the date column in descending order.
Keep only the top row to ensure you are using the most recent file.
Close and apply the changes.
Step 4: Scheduled Refresh
Publish your Power BI report to the Power BI Service.
In the Power BI Service, go to the dataset settings.
Configure the scheduled refresh settings to update the data at your desired frequency.