How to Make Power Query File Paths Dynamic for Multiple Windows Users
Question details
The user needs to configure a Power Query file path to work dynamically across different Windows user accounts when sharing a workbook.
- Product
- Excel
- Device & OS
- Windows
- Scenario
- Sharing a Power Query workbook that connects to files stored in local synced folders, such as Box or OneDrive, where the Windows username changes per user.
- Observed behavior
- The query fails to refresh for other users because the file path is hardcoded to the original creator's specific Windows User ID.
Before modifying your Power Query M code, ensure that all users have the Box desktop app (or your specific cloud sync tool) installed and that the folder structure after the Windows user profile folder is identical for everyone.
Use a Named Cell for Dynamic Windows User IDs in Power Query
Create a named cell in your Excel workbook to hold the current user's ID, and reference it dynamically within the Power Query Advanced Editor.
By reading a value directly from an Excel worksheet, Power Query can dynamically build the file path. Each user simply updates the cell with their Windows username to refresh the query successfully.
In your Excel workbook, select an empty cell (e.g., A1) and type your Windows User ID. Click the Name Box located to the left of the formula bar, type 'UserID', and press Enter to name the cell.
Navigate to the 'Data' tab on the ribbon, click 'Get Data', and select 'Launch Power Query Editor'. Once the editor opens, select your query and click 'Advanced Editor' on the Home tab.
Modify your M code to retrieve the value from the named cell and concatenate it into your file path. Add these lines at the beginning of your 'let' statement: let Source = Excel.CurrentWorkbook(){[Name="UserID"]}[Content][Column1]{0}, FilePath = "C:\Users\" & Source & "\Box\Your_Folder_Path"
Replace the hardcoded file path in your original connection step with the new 'FilePath' variable. Click 'Done' to save the code.
Click 'Close & Load' in the Power Query Editor. The query will now use the username entered in the 'UserID' cell to locate the file.
Connect Directly Using a Cloud Web Link
Bypass local folder paths entirely by connecting Power Query directly to the file hosted on the cloud using a shared web link.
Need a Faster, Lightweight Spreadsheet Tool?
While Power Query is a specific feature of Microsoft Excel, if you are looking for a highly compatible, free, and lightweight Office suite for everyday data analysis and team collaboration, WPS Office is an excellent choice.
Frequently Asked Questions
Why does my Power Query fail to refresh for other users?
When you connect to a local synced folder (like Box, OneDrive, or Dropbox), Power Query records your specific Windows profile path (e.g., C:\Users\YourName\...). When other users open the shared file, their computer tries to find your user folder on their hard drive, resulting in a 'DataFormat.Error' or 'File not found' error.
Can Power Query automatically detect the current Windows username?
For security and privacy reasons, Power Query does not have a native, built-in function to read the Windows environment username directly. The most reliable workaround is to pass the username from an Excel named cell into the Power Query M code.
Does this dynamic path method work for OneDrive and SharePoint as well?
Yes, you can use the exact same named cell logic to replace the username variable in OneDrive or SharePoint local sync paths. However, for SharePoint, it is generally recommended to use the built-in 'From SharePoint Folder' connector to avoid relying on local sync paths altogether.
