Log In
Register
Home
September 29, 2021
Offline
I saved my work last week, and now today when I attempt to continue to work on my project, one of the queries is giving an Expression Error about a missing column. The column is missing because for whatever reason, the Navigation step is not pulling in all the columns from the source data. I have rebuilt the query and it will load in the query editor without errors. But I can't load to a table or load any other queries that read the table even though I have reset everything. I can get it to work if I remove the #Removed Columns step, but I am concerned that the navigation step, which doesn't show up in the advanced editor, will reset again in the future and break it again.
Navigation step (as shown in editor step window)
= Source{[Item="Sales Order History",Kind="Sheet"]}[Data]
Advanced Editor code: (I replaced file name/address with xxxx for this post.)
let
Source = Excel.Workbook(Web.Contents("https://xxxx.xlsx"), null, true),
#"Sales Order History_Sheet" = Source{[Item="Sales Order History",Kind="Sheet"]}[Data],
#"Removed Columns" = Table.RemoveColumns(#"Sales Order History_Sheet",{"Column1"}),
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Tanya,
The navigation step is just selecting a sheet, it does nothing to the sheet content, so I guess it's something else.
If you can anonymize the data and upload sample files that replicates the missing column error, we can try to identify the source.
September 29, 2021
Offline
This is the second time it has happened, with different source and query files. 🙁
My workaround this time was to use 'Remove Other Columns' instead of removing the one column, so if it starts working again it won't break. This will probably become my 'best practice' as almost all our source data has the first column as blank, but if it isn't imported this glitch/gremlin will break the queries.
I did rebuild the query that day (more than once, and used older and new data files) and had the same problem. But now today it imported correctly. Next time I'll just be patient and/or do a few reboots! (and capture to share here to see if it is machine related).
Answers Post
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Maybe this topic will help, you can check if column 1 is empty and remove it:
September 29, 2021
Offline
Thank you! I have tried using list functions instead of merging queries (learned from a MOTH video!), and this additional use is helping me understand how to think of things in terms of lists more. I will continue to explore that line of thinking for this and future projects.
1 Guest(s)
