'%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 Importing Unexpected SharePoint List Columns in Excel
Question details
Users need to resolve an issue where Power Query fails to import the correct set or sequence of columns from a SharePoint list.
- Product
- Microsoft Excel and Power BI
- Device & OS
- not provided
- Scenario
- Importing data from a frequently updated SharePoint list into Excel or Power BI using Power Query.
- Observed behavior
- Power Query imports only a subset of columns, and the selected columns do not match the original SharePoint list's column order or numbering.
Verify that you have admin access or can contact your Microsoft 365 administrator, as resolving persistent SharePoint connection bugs may require generating support tickets and HAR logs.
Open a Support Ticket via Microsoft 365 Admin Center
Use this official channel when the issue persists across both Excel and Power BI and cannot be fixed by refreshing local data, as it requires backend investigation.
When Power Query consistently fails to retrieve the correct SharePoint list schema and the issue is not reproducible locally, it often points to a deeper synchronization bug between SharePoint and Power Query services.
Microsoft's support team will need to investigate the specific SharePoint connection and collect HAR (HTTP Archive) logs to identify where the column mapping fails.
Log in to the Microsoft 365 Admin Center using an account with administrator privileges.
Click on 'Show all' in the left-hand menu, then select 'Support' followed by 'Help & support'.
Briefly describe the issue ('Power Query importing unexpected SharePoint list columns') and select the option to contact support.
Mention in your ticket that the issue affects both Excel and Power BI, and state that you are ready to provide HAR logs for the SharePoint connection.
Manually Expand Hidden Record Columns in Power Query Editor
Use this workaround to force Power Query to display missing SharePoint columns that might be hidden inside structured records.
Clear Data Source Permissions and Refresh
Clear cached credentials to ensure Power Query isn't using outdated schema definitions or permissions when connecting to SharePoint.
Experience Hassle-Free Data Management with WPS Office
If complex integrations and synchronization bugs in Microsoft Excel are slowing down your daily workflow, consider switching to WPS Office. It provides a streamlined, highly compatible environment for editing spreadsheets, analyzing data, and managing your standard tasks without the heavy resource overhead.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button.
- 2. Install the Suite: Run the setup file and follow the quick on-screen instructions to install WPS Office on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel files. Your formatting, data, and formulas will be completely preserved.
Frequently Asked Questions
Why are some SharePoint columns missing when I import them into Excel via Power Query?
This usually happens because Power Query caches an older version of the list schema, or the missing columns are complex data types (like Lookups or Metadata) that are grouped inside a 'Record' column. You often need to expand these records manually in the Power Query Editor.
How do I fix the column order imported from SharePoint?
Power Query does not always respect the default 'View' order configured in SharePoint. To fix this, open the Power Query Editor, drag and drop the column headers into your preferred order, and then click 'Close & Load' to apply the changes to your Excel sheet.
What are HAR logs and why does Microsoft Support need them?
HAR (HTTP Archive) logs record the background network requests between your browser and Microsoft's servers. Support engineers use them to pinpoint exactly where the data transfer for the SharePoint list columns is failing or being truncated.
Can I force Power Query to refresh the SharePoint list structure?
Yes. Inside the Power Query Editor, go to the 'Home' tab and click the drop-down arrow under 'Refresh Preview', then select 'Refresh All'. This forces the query to fetch the latest columns and data structure directly from the source.
