Excel - "expression.error: the column 'Date' of the table was not found" even though it exists in the table

0
votes

Refreshing a query in excel, I have an error that is a popup, and doesn't allow me to go to where the error is occurring. In the power query editor, the error does not come up. When I exit the power query editor and try to load the query to the table, I get the expression error. Below is the screenshot of the power query editor showing the date:

Date column displays

But when I try to filter table by date, I get:

expression.error: the column 'Date' of the table was not found

The spot where the error is occurring in the code is here:

let
Source = Csv.Document(Parameter3,[Delimiter=",", Columns=13, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
#"Removed Top Rows" = Table.Skip(Source,2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Domain", type text}, {"Content", type text}, {"Sender Profile Image Url", type text}, {"Created Time", type datetime}, {"Permalink", type text}, {"snTypeColumn", type text}, {"Associated Cases", type text}, {"Product Brand Name", type text}, {"Product", type text}, {"Sentiment", type text}, {"Star Rating", Int64.Type}, {"Experience Score", Int64.Type}})

in #"Changed Type"

Here's the table I was querying from:

{"Date", type date}, {"Domain", type text}, {"Content", type text}, {"Sender Profile Image Url", type text}, {"Created Time", type datetime}, {"Permalink", type text}, {"snTypeColumn", type text}, {"Associated Cases", type text}, {"Product Brand Name", type text}, {"Product", type text}, {"Sentiment", type text}, {"Star Rating", Int64.Type}, {"Experience Score", Int64.Type}}
1
excelexcel-formulapowerbipowerquery
please post your full code, and a view of the starting data for that table – horseyride
alternatively, if you can share a sample file then someone may be able to find out where went wrong. – Terry W
@TerryW: That's not how SO works. If the information can't be made available in the question itself, then the question isn't appropriate for this site. You can't post files externally and then ask people to go get that file and figure things out. – Ken White

1 Answers

0
votes

Here's the parts I debugged for this issue: Looked at the files in the directory that was being used as the source. Noticed that some files had the header column in the top row, and other files (most) that had two document information rows. So the previous owner of this query assumed a two rows of data that is not necessary for the query, and so added #"Removed Top Rows" = Table.Skip(Source,2) to skip past those two top information rows.

  1. I went through queried files, iterating over each one and adding two new rows if they were edited to take out the top two rows
  2. I then tried, and ran into additional errors, so I checked the file extensions. Some files were xlsx when this query calls for: Csv.Document(Parameter3,[Delimiter=",", Columns=13, Encoding=65001, QuoteStyle=QuoteStyle.Csv])
  3. I changed/modified/deleted any xlsx files that were causing additional issues
  4. Query completed successfully after working through the logic

Takeaway: sometimes manual work to check every file can overcome query issues.