Remove columns in source data Excel file

[Anonymous]

Hi everyone, I am new to Power BI desktop. 

Problem:

The source data is from an Excel spreadsheet. I made a dashboard then deleted one column named 'test column' in Excel and refresh the dashboard, it turns out an error

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

Ways I have tried:

1. Add the column back in Excel, the dashboard stops the error. But I don't need this column anymore and I don't want to include this column in the dashboard so I delete the column again, the error comes out again.

2. Clear Permission in Data source settings and Edit permission to Public or Private again. Refresh, and still get the same error.

Expectation:

How to make this error fixed? Why Power BI cannot be updated automatically according to the source file's change? It's so annoying feature.

Could you please help with this issue? 

Thanks! 

[Anonymous]

@Anonymous,

The engine detects that you specify to remove "test column" in your M code, but it could not found the specified column, hence the error. Rather than remove columns you dont want, use Remove Other Columns transformation instead. This will select the columns you want to keep in case additional columns might appear in your Excel file in the future. 

[Anonymous]

@Anonymous

Thanks for your reply. I get your meaning and I am sure it works.

But I prefer to resolve it in the direct way. In future you can not prevent updating from source data. what should I do if source data column is missing like this? I cann't choose a product without function to deal with this senario.

[AlexisOlson]

What do you mean "resolve it in the direct way"?

@Anonymous's suggestion is the simplest way to prevent extra columns appearing in the future by choosing what columns to keep rather than what columns to remove.

If you want Power BI desktop to just load in whatever is in the Excel file, then simply remove the last step that tries to delete column(s) (and any other steps that reference that column).

If neither of these work for you, then you need to explain more clearly how you want the load to behave. Do you always want every column from the Excel file or do you always want just a specific subset or some other logic that you haven't specified?

[Anonymous]

@AlexisOlson

Thanks for your idea.

Yes. I want Power BI desktop to just load in whatever is in the Excel file.

simply remove the last step that tries to delete column(s) (and any other steps that reference that column).

Do you mean delete this step marked with red box? please refer to the screenshot.

And delete fieds in the dashboard that reference that column.

[AlexisOlson · Accepted solution]

Yes, if you remove that last step that you have in the red box, then it won't try to delete a nonexistent column.

[Anonymous]

@AlexisOlson

Thank you.

It works out. It turns out so easy! Thank you very much!