If you are saving the Excel file to your desktop locally, then you must close the Excel file before using Power BI template.
If you are saving the Excel file to SharePoint, you can continue to have both Excel file and Power BI template open
When you open the Power BI template, you should edit the parameters as shown below.
There are 3 parameters. Start Year, End Year and File Location.
If the earliest employee started in 2018, enter Start year as 2018. If current year is 2022, enter End Year as 2022.
If the Excel file is located locally on your desktop, the file location parameter should look like this.
If the Excel file is located on SharePoint or OneDrive, it will look like this
Note: If the path ends with web=1, please make sure to remove "?web=1
After successfully entering parameters, click on Apply changes.
If you receive any errors in Power BI, please try again to apply changes. It sometimes takes a couple of attempts by Power BI to establish connection.
Verify that your data is being pulled into Power BI successfully. With that, the one-time connection is done between the Power BI template and the Excel data file.
Whenever there is any change to the Excel file data, just open Power BI and hit Refresh to pull the latest data from Excel to Power BI.