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.
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.
In Power Query, sort by “Date Modified” or extract the date from filenames.
Use Keep Top Rows = 1 to select only the newest file. sorry how do you do this step Anonymous ?
Hi Anonymous ,
After connecting to the folder, Power BI will load a table listing all the files in that folder.
In Power Query Editor, follow these steps:
Locate the "Date modified" column.
Click the dropdown on that column and choose Sort Descending – this will bring the latest file to the top.
Go to the Home tab - click "Keep Rows" - Keep Top Rows.
Enter 1 to keep only the latest file.
Finally, click "Combine Files" (if not done already) to extract data from that latest file.
Hope this helps you.