'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Power Query Showing Duplicate or Phantom SharePoint Files in Excel
Question details
Excel Power Query identifies multiple rows for a single key because it detects a duplicate or phantom file in a SharePoint folder that isn't visible in the regular interface.
- Product
- Microsoft Excel, SharePoint
- Device & OS
- not provided
- Scenario
- Importing and transforming data from a SharePoint folder using Power Query in Excel.
- Observed behavior
- Power Query displays two entries with identical file names and metadata, causing key matching errors. Deleting the visible file in SharePoint does not remove the phantom duplicate from the query results.
Before escalating to support, try clearing your Excel Power Query cache and verify that the SharePoint site's recycle bins (both first and second stage) do not contain hidden versions of the file.
Contact Microsoft 365 Support for Metadata Inspection
Since phantom files are often caused by backend synchronization issues or stale SharePoint metadata, a Microsoft 365 administrator must open a service request.
When a file appears in Power Query but not in SharePoint or File Explorer, the issue usually lies deep within the SharePoint environment's metadata caching.
Microsoft Support engineers have the necessary backend tools to inspect and repair the corrupted metadata causing these duplicate entries.
Have your organization's Microsoft 365 administrator log in to the Microsoft 365 admin center using their administrator credentials.
On the left-hand navigation menu, click on 'Support' and then select 'Help & support' or 'New service request'.
Detail the issue, mentioning that Power Query is pulling phantom or duplicate files from a SharePoint folder due to suspected stale metadata, and submit the ticket for a support engineer to review.
Clear Power Query Cache and Refresh
Sometimes the duplicate file is temporarily stuck in your local Power Query cache rather than being a permanent SharePoint issue.
Switch to WPS Office for a Seamless Spreadsheet Experience
If you are tired of dealing with complex backend syncing issues and phantom file errors in Microsoft Office, consider trying WPS Office. It provides a lightweight, highly compatible alternative for managing your spreadsheets and data efficiently without the hassle.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded file and follow the quick on-screen instructions to install the suite.
- 3. Open Your Excel Files: Launch WPS Spreadsheets and open your existing .xlsx files with full formatting and formula support.
Frequently Asked Questions
Why does Power Query show duplicate files that aren't in SharePoint?
This is typically caused by stale SharePoint metadata, hidden items trapped in the site's recycle bin, or background synchronization errors that haven't fully cleared from the server.
Can deleting the visible file fix the phantom duplicate in Power Query?
Usually, no. If Power Query is reading a phantom file due to stale metadata, deleting the visible file will only remove the legitimate entry, leaving the duplicate or phantom entry intact in your query results.
How do I check if the duplicate is hiding in the SharePoint Recycle Bin?
Open your SharePoint site in a web browser, navigate to the 'Recycle bin' on the left menu, and look for deleted versions of the file. If you are an admin, check the 'Second-stage recycle bin' at the bottom of the page as well.
