How do I get PowerBI to refresh to updated Excel document in a file?

[achintya_patel]

Hi there,

I am looking for help regarding refreshing in powerBI. Currently I have an excel document linked to the powerBI Desktop. This excel document gets updated weekly but onto a separate excel document placed onto a file. 

Is it possible for my powerBI desktop to be linked to this file, so that it updates to the most recent dated excel file weekly/monthly?

Thanks so much,

[v-sshirivolu · Accepted solution]

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

[achintya_patel]

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 @v-sshirivolu ? 

[v-sshirivolu]

Hi @achintya_patel ,

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.

[bhanu_gautam]

@achintya_patel 

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.

[achintya_patel]

Hi, how do I do these three steps? 

Thank you!

[bhanu_gautam]

@achintya_patel 

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.