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"
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"
