Fix Missing Item, Kind, and Hidden Columns in Excel Power Query
Question details
The user needs to troubleshoot and restore the Item, Kind, and Hidden metadata columns that have disappeared from Excel Power Query when reading multiple source workbooks.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Combining and reading data from multiple source workbooks using Power Query.
- Observed behavior
- The Item, Kind, and Hidden columns are missing from the query results, preventing proper data filtering and identification.
Before modifying your query settings, ensure that you have backed up your original Excel workbook and verify that your Office application is updated to the latest version.
Adjust Query Privacy and Data Source Settings
Restrictive privacy settings can prevent Power Query from reading metadata like Item, Kind, and Hidden columns when combining multiple sources.
Power Query uses privacy levels to isolate data sources from one another. If a source is restricted, metadata columns may be stripped during the merge process.
In Excel, go to the Data tab on the ribbon, click on Get Data, and select Query Options.
Under the Global or Current Workbook section, select Privacy.
Ensure that the privacy level is not restricted to 'This Source Only' or set it to 'Always ignore Privacy Level settings' for testing purposes.
Click OK and refresh your Power Query to check if the missing metadata columns are restored.

Create a Test Query to Verify Metadata
Rebuilding the navigation step with a new test query helps determine if the source files are still exposing the required columns.
Manually Add Equivalent Custom Columns
If the data provider or an update has permanently removed these columns, you can recreate them manually using custom column formulas.
Try WPS Office for Seamless Spreadsheet Management
If you frequently encounter complex errors in Excel, consider switching to WPS Office. It is a free, lightweight, and highly compatible alternative that offers a familiar user interface, making data analysis and spreadsheet management effortless.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package.
- 2. Install the Software: Run the installer and follow the on-screen instructions to set up WPS Office on your device.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files to continue working seamlessly.

Frequently Asked Questions
Why do Item, Kind, and Hidden columns disappear in Power Query?
These columns contain metadata about the workbook objects (like Sheets or Tables). They can disappear if privacy settings restrict data combination, if the source file structure changes, or after certain Excel updates.
How can I check my Power Query privacy settings?
Open Excel, navigate to the Data tab, select Get Data, and click Query Options. Under the Global or Current Workbook section, select Privacy to view and adjust your isolation levels.
Do I need to update Excel to fix Power Query bugs?
Yes, Microsoft frequently releases patches for Power Query. Ensuring your Office 365 or standalone Excel installation is up to date can resolve unexpected metadata missing errors.




