'%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 Not Refreshing Shared OneDrive Data in Excel for Mac
Question details
The user is unable to refresh Power Query data from a shared OneDrive workbook using Excel for Mac.
- Product
- Excel for Mac
- Device & OS
- Mac
- Scenario
- Attempting to refresh a Power Query connected to a shared Excel file hosted on OneDrive while using Excel for Mac.
- Observed behavior
- The query fails to refresh, new rows added to the source workbook do not appear, and users may encounter a 'DataFormat.Error' regarding an invalid absolute path.
Verify that you have an active Microsoft 365 for Mac subscription, as standalone perpetual versions have limited Power Query features, and ensure no other users are currently editing the shared source workbook.
Connect Using the 'From Web' Option
Bypass local path limitations on macOS by connecting to the OneDrive for Business file via its web URL.
Excel for Mac has different data-source capabilities compared to Windows. Shared OneDrive local synced paths are often not treated as valid refreshable absolute paths, triggering a DataFormat.Error. Using a direct web link can sometimes resolve this.
Open OneDrive for Business in your web browser, locate your source Excel file, and copy the direct link (URL) to the file.
Launch Excel for Mac, navigate to the 'Data' tab on the ribbon, and select 'Get Data' followed by 'From Web'.
Paste the copied OneDrive link into the URL field and authenticate with your Microsoft 365 account to establish the data connection.
Refresh the Query on Excel for Windows
Use Excel for Windows to refresh the data since it fully supports Power Query connections to shared OneDrive files.
Migrate Source Data to a Supported Location
Store the source data in a location fully supported by Power Query for Mac to ensure seamless refreshing.
Try WPS Office for Seamless Cross-Platform Data Management
If you are tired of compatibility issues and platform limitations in Excel for Mac, consider switching to WPS Office. It provides a free, lightweight, and highly compatible alternative for managing your spreadsheets, analyzing data, and collaborating across platforms without expensive subscriptions.
- 1. Download the software: Visit the official WPS website and download the free WPS Office suite for your Mac.
- 2. Open your workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx workbooks without losing formatting.
- 3. Manage your data: Utilize the built-in 'Data' tab tools to filter, sort, and analyze your shared information effortlessly.
Frequently Asked Questions
Why do I get a 'DataFormat.Error' when refreshing Power Query on Mac?
This error occurs because Excel for Mac does not recognize local synced OneDrive paths in the same way Excel for Windows does. It requires a valid absolute path or a supported web URL to complete the refresh.
Does the standalone version of Office for Mac support Power Query?
The standalone perpetual versions of Office for Mac (such as Office 2019 or 2021) have a much smaller set of Power Query features compared to the Microsoft 365 subscription version. An active Microsoft 365 subscription is recommended for full data-source support.
Can I refresh a Power Query while someone else is editing the source file?
Refreshing data while the source workbook is actively being edited by another user can cause data locks or result in incomplete updates. It is highly recommended to perform the refresh when the source file is closed.
