cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tas1 Regular Visitor
Regular Visitor

Expression.Error: The column 'XXX' of the table wasn't found.

Hi all, I have updated PowerBI desktop last night. I have a script I'm reusing every month to read multiple excel files from a folder and apply some transformations on the data, sicne I have updated to the august version I keep on getting this error:

 

Expression.Error: The column 'Day' of the table wasn't found.
Details:
Day

 

Also when got back to read the script from the reports produced in a previous month I got the same error again! although it was working perfectly before the update!!!

 

I looked for solutions in the community but none applies to this!! 

 

Help please!!

 

 

Here's the full script:

 

let
ImportDaily =
let
Source = Folder.Files(<Folder Path>),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetExcelData", each Excel.Workbook([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded GetExcelData" = Table.ExpandTableColumn(#"Removed Columns", "GetExcelData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded GetExcelData",{"Hidden"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "RemovedRows", each Table.Skip([Data],5)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "PromotedHeaders", each Table.PromoteHeaders([RemovedRows])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "DateFill", each Table.FillDown([PromotedHeaders],{"Day"})),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "RemoveChannel", each Table.RemoveColumns([DateFill],{"Channel"})),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "RemoveFileName", each Table.RemoveColumns([RemoveChannel],1)),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom5",{"RemoveFileName"}),
#"Added Custom6" = Table.AddColumn(#"Removed Columns2", "UnpivotData", each Table.UnpivotOtherColumns([RemoveChannel], {"Start GP", "Day"}, "Attribute", "Value")),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom6",{"UnpivotData"}),
#"Expanded UnpivotData" = Table.ExpandTableColumn(#"Removed Other Columns1", "UnpivotData", {"Day", "Start GP", "Attribute", "Value"}, {"Day", "Start GP", "Attribute", "Value"})
in
#"Expanded UnpivotData",
#"Filtered Rows" = Table.SelectRows(ImportDaily, each ([Day] <> "Total")),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Day", type date}, {"Start GP", type time}, {"Attribute", type text}, {"Value", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","--","",Replacer.ReplaceText,{"Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Value", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Start GP", "5min"}, {"Attribute", "Channel"}, {"Value", "000s"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Day", "Channel", "5min", "000s"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Day", "Date"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each ([000s] <> null))
in
#"Filtered Rows1"

 

 

Thank you !

1 ACCEPTED SOLUTION

Accepted Solutions
Tas1 Regular Visitor
Regular Visitor

Re: Expression.Error: The column 'XXX' of the table wasn't found.

Hey!!! I found it !! 

So, I'm using the script to load 31 excel files from a folder (specified in the source path). After hours of investigation I've come to notice that one of the files has an extra empty row and its the 6th row (unlike all other 30 files). To be more clear, in one step I remove the top 5 rows, then in the step after I promote the first row ( after the top 5 have been removed ) to be the table header, whereas the affected file has an extra row promoting that empty row to header causes the error, as far as I can conclude! 

I hope this helps another poor soul 🙂 

 

View solution in original post

5 REPLIES 5
omrdmr Regular Visitor
Regular Visitor

Re: Expression.Error: The column 'XXX' of the table wasn't found.

Hi,

 

In which step do you get the error?

When you click on the steps from bottom to the up in the Applied steps, on which step this error disappears?

 

Maybe this will help you to find out where the issue is.

 

Regards.

Tas1 Regular Visitor
Regular Visitor

Re: Expression.Error: The column 'XXX' of the table wasn't found.

It appears on all applied steps!

Tas1 Regular Visitor
Regular Visitor

Re: Expression.Error: The column 'XXX' of the table wasn't found.

Hey!!! I found it !! 

So, I'm using the script to load 31 excel files from a folder (specified in the source path). After hours of investigation I've come to notice that one of the files has an extra empty row and its the 6th row (unlike all other 30 files). To be more clear, in one step I remove the top 5 rows, then in the step after I promote the first row ( after the top 5 have been removed ) to be the table header, whereas the affected file has an extra row promoting that empty row to header causes the error, as far as I can conclude! 

I hope this helps another poor soul 🙂 

 

View solution in original post

Moderator v-yuezhe-msft
Moderator

Re: Expression.Error: The column 'XXX' of the table wasn't found.

@Tas1,

Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
meera Occasional Visitor
Occasional Visitor

Re: Expression.Error: The column 'XXX' of the table wasn't found.

Hello, I changed the a cell in excel from Value to Forecast I now get the following error in Power BI (preventing me from refreshing my data).

 

Expression.Error: The column 'Value' of the table wasn't found.
Details:
    Value

 

I've made changes to the Renamed column aspect to read this...but It still doesn't work.

 

Can you help please?


    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Strategy", type text}, {"Cost Type", type text}, {"Cap/Rev", type text}, {"Supplier", type text}, {"Project Online", type text}, {"Theme", type text}, {"Category", type text}, {"Product Owner", type text}, {"Product", type text}, {"Item", type text}, {"Month", type date}, {"Value", type number}, {"Budget", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Budget", "Budget '17"},{"Item", "Allocation"},{"Value", "Forecast"}})

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 484 members 4,479 guests
Please welcome our newest community members: