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

[Anno2019]

Hi Guru's

Need help please, I have search multiple posts with the same problem but my particular problem I could not find an answer to.

I have multiple data tables, each are an exact copy of the next when imported in terms of column placement, column heading names etc.  All of these are then appended to form 1 table.

My problem is that two of my tables are failing to refresh due to:

"Expression.Error";  The column 'XXX' of table wasn't found."

So here is where my problem lies, I found the step which creates the errors in both tables, it is the step for "Change Type", I could simply remove the offending column name from the Change Type code and then no more error BUT...the problem why the error exists in the first place is because it thinks the column does not exist anymore... which is not true.

I checked every single excel sheet source and there are 31 columns in the exact same order and nothing has changed, in the two with errors, they to have 31 columns BUT column number 31 is missing from the table in the first load step.

I do not know how to force powerbi to load the last column which IS in the data source.

Any ideas please???

[Anonymous]

Hi  - I face a similar issue. In my case, it is always the very last column which never gets loaded. The source files I use are all xlsx version & these files get overwritten everyday. The structure of the data tables remains the same.

In some other posts it was mentioned that "Changed Type" step could be causing issues. I have removed changed type steps in my report but still the last columns don't get loaded.

Now what I also found out is if I copy & paste the last column in the source files manually, then the Power BI report can load all the columns. But since the files are overwritten everyday, this isn't a viable solution.

Any other suggestions ?

Many thanks in advance!

[Anno2019]

@Anonymous 

I still don't have a solution or an answer, my problem is exactly what you are experiencing, it is the last column which is not recognised.

Even if I re-load the data source as a separate table, the last column still is not recognized.

[v-diye-msft]

Hi @Anno2019 

Could you plz kindly confirm if the issue has been resolved ? if yes, plz share your solutuon to helps others find it more quickly.

[AnkitBI]

Can you share your source files that is causing issues + 1-2 correct ones and PBIX file or Advance Editor Queries.

[Anno2019]

@AnkitBI , thank you for your response.

I cannot share the files, to sensitive but I can share the code in advance editor.

Essentially, in the two data sources which are failing, column number 31 which is "Account ID" is not loaded anymore causing the "Change Type" code to fail.  I checked both sources, and the column 31 has NOT been removed.

This is the code for the file where column 31 is not showing at all anymore

let

    Source = SharePoint.Files("https://mysharepointsiteaddresslinkishere/", [ApiVersion = 15]),

    #"Source Data file1 xlsx_https://mysharepointsiteaddresslinkishere/Shared Documents/Source Data Folder1/Email Messages/" = Source{[Name="Source Data file 1.xlsx",#"Folder Path"="https://mysharepointsiteaddresslinkishere/Shared Documents/ Source Data Folder1/Email Messages/"]}[Content],

    #"Imported Excel" = Excel.Workbook(#"Source Data File1 xlsx_https://mysharepointsiteaddresslinkishere/Shared Documents/ Source Data Folder1/Email Messages/"),

    #"DATA Sheet_Sheet" = #"Imported Excel"{[Item="DATA Sheet",Kind="Sheet"]}[Data],

    #"Removed Columns" = Table.RemoveColumns(#"DATA Sheet_Sheet",{"Column1"}),

    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),

    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dupl Search", type text}, {"Resp Name", type text}, {"Number", Int64.Type}, {"Number2", Int64.Type}, {"Current Month End Ex Rate (EUR)", type number}, {"Income (EUR) 000's", type number}, {"Income (EUR) fx adj 000's", type number}, {"Income (Loc) 000's", type number}, {"GP (EUR) 000's", type number}, {"GP (EUR)  fx adj 000's", type number}, {"GP (Loc) 000's", type number}, {"Product Name", type text}, {"UOM", type text}, {"Product", type text}, {"Year SAP", Int64.Type}, {"Month", type text}, {"Quarter", type text}, {"Month SAP", type date}, {"Acc Number", Int64.Type}, {"Acc Name", type text}, {"Sales Function", type text}, {"Client ID", Int64.Type}, {"Volume", Int64.Type}, {"TE", Int64.Type}, {"Volume (cb)", type number}, {"Chargeable Weight (kg)", type any}, {“Country Name", type text}, {"Branch", type text}, {"Country", type text}, {“Account ID", type text}}),

    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Dupl Search"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Duplicates",{{"Number", type text}, {"Number2", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Name", "Account Owner Name"}, {"Acc Name", "Customer Name"}, {"Sales Function", "Sales Channel"}, {"Country Name", "Sales Country"}})

in

    #"Renamed Columns"

This is the code for one of the source files where column 31 is showing correctly and there is no error.

let

    Source = SharePoint.Files("https://mysharepointsiteaddresslinkishere/", [ApiVersion = 15]),

    #" Source Data File2 xlsx_https://mysharepointsiteaddresslinkishere/Shared Documents/ Source Data Folder2/Email Messages/" = Source{[Name=" Source Data File2.xlsx",#"Folder Path"="https://mysharepointsiteaddresslinkishere/Shared Documents/ Source Data Folder2/Email Messages/"]}[Content],

    #"Imported Excel" = Excel.Workbook(#"Source Data File2 xlsx_https://mysharepointsiteaddresslinkishere/Shared Documents/ Source Data Folder2/Email Messages/"),

    #"DATA Sheet_Sheet" = #"Imported Excel"{[Item="DATA Sheet",Kind="Sheet"]}[Data],

    #"Removed Columns" = Table.RemoveColumns(#"DATA Sheet_Sheet",{"Column1"}),

    #"Removed Top Rows" = Table.Skip(#"Removed Columns",1),

    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Dupl Search", type text}, {"Resp Name", type text}, {"Number", Int64.Type}, {"Number2", Int64.Type}, {"Current Month End Ex Rate (EUR)", type number}, {"Income (EUR) 000's", type number}, {"Income (EUR) fx adj 000's", type number}, {"Income (Loc) 000's", type number}, {"GP (EUR) 000's", type number}, {"GP (EUR)  fx adj 000's", type number}, {"GP (Loc) 000's", type number}, {"Product Name", type text}, {"UOM", type text}, {"Product", type text}, {"Year SAP", Int64.Type}, {"Month", type text}, {"Quarter", type text}, {"Month SAP", type date}, {"Acc Number", Int64.Type}, {"Acc Name", type text}, {"Sales Function", type text}, {"Client ID", Int64.Type}, {"Volume", Int64.Type}, {"TE", Int64.Type}, {"Volume (cb)", type number}, {"Chargeable Weight (kg)", type any}, {“Country Name", type text}, {"Branch", type text}, {"Country", type text}, {“Account ID", type text}}),

    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Dupl Search"}),

    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Name", "Account Owner Name"}, {"Acc Name", "Customer Name"}, {"Sales Function", "Sales Channel"}, {"Country Name", "Sales Country"}})

in

    #"Renamed Columns"

[AnkitBI]

It will be difficult to analyse without files. Check if you can share after inserting some dummy data or masking any senitive values ?