Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tas1
Advocate I
Advocate I

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
Tas1
Advocate I
Advocate I

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
Tas1
Advocate I
Advocate I

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 🙂 

 

@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.

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

omrdmr
Helper I
Helper I

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.

It appears on all applied steps!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.