Expression.Error: The column '<ColumnTitle>' of the table wasn't found

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
Hi I am new to Power Query and I have a question. I upload quarterly data from a website in CSV format to a dedicated folder that holds all the previous files. The file structures have not changed for these source files but for some reason the newest upload is failing. When I hit refresh on the query, it runs through the routine of refreshing all the previous files and upon opening the newest file it is failing on the last step of the query. Note I only want the new file but the query was written to refresh everything which I am fine with for now.

The error generated upon querying the last and newest file provides the following message:

An error occurred in the ‘Transform File’ query. Expression.Error: The column 'MARKET_DAY' of the table wasn't found.
Details: MARKET_DAY

When I click on that last step in the query, the command I see in the query editor is trying sort a column as follows:
= Table.Sort(#"Filtered Rows1",{{"MARKET_DAY", Order.Ascending}})

From the Advanced Editor (see below) I am working backwards from this last step to see if in fact this Market_Day header/column is missing. But it appears to be there in the second last step called #Filtered Rows 1, and it appears to be there also in the third last step called #"Changed Type"

I cannot figure out why this is happening AND why it only appears to happen on the newest and last file in the refresh. I can see the file being opened and uploaded in the status bar during the query but it fails on the sort and never finishes.

ADVANCED EDITOR

let
Source = Folder.Files(<FilePath>),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> <SourceFileName>" and [Name] <> <ThisFileName>)),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"MARKET_DAY", type date}, {"NODE", type text}, {"TYPE", type text}, {"VALUE", type text}, {"HE1", type number}, {"HE2", type number}, {"HE3", type number}, {"HE4", type number}, {"HE5", type number}, {"HE6", type number}, {"HE7", type number}, {"HE8", type number}, {"HE9", type number}, {"HE10", type number}, {"HE11", type number}, {"HE12", type number}, {"HE13", type number}, {"HE14", type number}, {"HE15", type number}, {"HE16", type number}, {"HE17", type number}, {"HE18", type number}, {"HE19", type number}, {"HE20", type number}, {"HE21", type number}, {"HE22", type number}, {"HE23", type number}, {"HE24", type number}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([VALUE] = "LMP") and ([TYPE] = "Loadzone") and ([NODE] = "OTP.OTP")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"MARKET_DAY", Order.Ascending}})
in
#"Sorted Rows"
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Are you sure it's that step that causes the error? If you select the penultimate step, are there results visible?
 
Upvote 0

Forum statistics

Threads
1,223,355
Messages
6,171,615
Members
452,411
Latest member
sprichwort

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top