Power BI errorss out when there is a deleted column in the source...

[Anonymous]

Hi All,

Power BI Desktop errors out during refresh when a column was removed in the source even when that column is not used in the report.

There must be a way to have power bi recognize and update its model.

This would a very common use case during development so Power BI Desktop should handle it gracefully not stop.

So if we have a source table with columns A, B, C and we remove C since it is not in use it should not cause Power BI to error and stop a refresh.

Thanks for the help on this.

[edhans · Accepted solution]

This is why generally you don't remove columns. Rather, you select the columns you want and Remove Other Columns. That actually specifically selects the columns you want with Table.SelectColumns() and other columns can appear and disappear and Power Query won't care.

[Anonymous]

Sounds very confusing and very non intuitive. So is there a resync to the source that Power BI provides? There are multiple ways to remove columns in the tool and only one specific way to tell Power BI to remove it safely???

Hopefully Power BI has a better way to address this.

[edhans]

This is how it works. There are two key ways to remove a column. Table.RemoveColumns() and this is when you specifically want a column gone. So when I create temporary columns, I often just remove them. I created them and I know they are there, so I remove them.

But most often my data comes from an outside source, and if I cannot trust it to always be consistent, if it has columns A, B, C, and D, and I don't want C, I select A, B, and D and "Remove Other Columns." Then if column E shows up, I don't get A, B, D, and E in my data. Likewise, if column C gets deleted, I don't care, because I ignored C, and that is Table.SelectColumns().

Now, if A, B, or D disappears, it will break, but if I was using those, my formulas in DAX will too.

THere are not multiple ways to remove and only one to remove safely. There are multiple ways to remove columns and you need to pick the one that is best for your scenario. 

If you always want new columns added to come into the data source, because you are Unpivoting them later (via Unpivot Other Columns) like new date columns, then you do want to use Remove Columns, because Remove Other Columns won't let you select columns that don't exist yet.

I know it sounds confusing at first, but there are 100% legit reasons for each way to remove columns. And if you delve into the M language, you can remove and select columns dynamically based on their index number, characters in the column name, and other ways. I have a project I'm working on where I select the first two columns (index 0 and 1), then every column that ends in "2020" and "2021" as they are entered, then I effectively use Remove Other Columns (Table.SelectColumns() based on my dynamic list) on the project, because all kinds of other garbage columns (for me)comes in and out that I never want to see.

Super SUPER powerful product, but like any product with a lot of power, it can seem like a dizzying array of options at first.

[Anonymous]

I understand what you are saying but I deleted columns and I noticed that an entry was added in a file where advanced editor can get to it and I can remove the column form a line labeled removed columns and it comes back into the table it was "deleted" from.

So great to have so many delete/remove/remove those not selected options but I really need a way to fix the problem considering the situation. Seems to me Power BI is a little weak in this area since syncing to source properly is not provided like I hoped.

[Anonymous]

Ok I did a workaround. Power Query opened up what like an entry into what Advanced Editor for a specific table and I removed the entry for the offending column and refresh started working again.

I still would like to know why all the other columns listed as removed worked but not this one column.

Really odd.

[edhans]

I'm not sure what this means. "Power Query opened up what like an entry into what Advanced Editor for a specific table"

You'll need to post specifics, including code, screenshots, data, etc.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

Thanks it is solved by what I did as I mentioned as indicated by the error message.

Many Thanks!

[edhans]

I'd have to see what you are talking about with screenshots or something. Tables and columns do not come back. If you are in the Advanced Editor you really need to know what you are doing. You can reference the wrong steps and while to you it seems it came back, to Power Query, you told it to come back.

Also, Power Query is read only, so deleting columns will not cause entries to be added anywhere.

I know you are a bit frustrated right now, but I think the cautious approach is to assume you don't fully understand the product (I've been using it for 5-6 years and I don't claim to fully understand it by a long shot!) and it is that understanding how it does it and why it does what it does should be the goal, not to blame the product for being "weak."