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

[lluser]

I am querying a Teams Sharepoint folder for Excel files to include the content in the analysis. Here are the steps I follow:

1. Select the folder and combine the files in the folder

2. Here is the result of the Query

3. The following steps are applied to transform the sample file:

a. The first two rows are removed

b. The first rows is used as the column header

4. Query displays the following error:Expression.Error: The column 'Incident Report for My Groups -A' of the table wasn't found.
Details:
Incident Report for My Groups -A

I am new to Power BI and I understand why I am getting the error. However, the only solution I found was to modify the original spreadsheet outside of Power BI, before loading it. I was wondering if a better solution exists. Your help is greatly appreciated.

[lluser]

Thank you for your reply.

Here is the statement in Advanced Editor:

let
Source = Excel.Workbook(Parameter1, null, true),
#"Incident Report for My Groups -_Sheet" = Source{[Item="Incident Report for My Groups -",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Incident Report for My Groups -_Sheet", [PromoteAllScalars=true]),
#"Removed Top Rows" = Table.Skip(#"Promoted Headers",2),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Assigned Group", type text}, {"Assignee", type text}, {"Incident Number Hyperlink", type text}, {"Priority", type text}, {"Status", type text}, {"Status_Reason", type text}, {"Description", type text}, {"Submit Date", type datetime}, {"Last Resolved Date", type datetime}, {"Resolution Category", type text}, {"Customer Name", type text}, {"Product Name", type text}})
in
#"Changed Type"

The column was not renamed, but the first row was promoted as the column header, therefore there is no mention of the old column name in the statement.

I tried to rename the column manually in the Query but I still got the error.

[Smalfly]

Can you share an example file please?

[lluser · Accepted solution]

Problem solved by replacing all the column names in Query statement with the ones from Sample File. Still, it looks like a workaround to an unhandled exception in Power BI.

Thank you for your help.

[Smalfly]

Hi @lluser ,

If I understand it correctly, the column 'Incident Report for My Groups -A' has been renamed to 'Assigned group'.

If that is the case, you can update the query and replace every occurence of 'Incident Report for My Groups -A' with 'Assigned group'. This can be done in the applied steps of your query (on the right panel if you select the query) or in the advanced editor if you like:

I hope that helps.