Deleting columns in table view vs Power Query Editor

[tbakrf]

I'm bringing a SQL table into Power BI Desktop as an import, and there are several unnecessary columns (they are not part of any relationship). 

The question is whether I should click on the Table View (left navigation), right click the column(s) and delete, or if I should stick with transforming the data and clearing out columns through PQE.

Can someone please clarify the difference between what happens when you use the table view / delete from model versus transform?  If the data is refreshed several times a day am I saving any resources deleting the column in the model?

Apologies- I keep trying to web search the differences but every result I find on deleting columns seems to be based only on  the transforming data method.  Thanks!

[AmiraBedh · Accepted solution]

Power Query Editor is used to transform data before it is loaded into the model.

When you remove a column in PQE, the column is excluded during the data load. This means it won’t be part of the dataset in Power BI, and it won't consume memory or affect the model size.

IMHO, I think this approach is generally more efficient. Since the column is removed before loading, less data is transferred, stored, and processed. If your data refreshes several times a day, you save resources each time, as the unnecessary data is never loaded.

Also don't forget that PQE transformations are more transparent and reusable so if you need to revisit why certain columns were removed, it's easy to track in the applied steps in PQE.

In the other hand, when you delete a column in the table view here it means you are removing it from the model after the data has already been loaded from the source.

The column is still fetched from the source and loaded into the dataset, but it is simply not visible in the report view.

Although it may clean up your report visually, it doesn’t save memory or processing power, as the data still exists in the underlying model. If your data refreshes frequently, those columns will still consume resources each time, even though they’re hidden from your report.

Deleting in the model isn't as transparent or easy to track compared to PQE. Future users (or even yourself) may not realize that those columns are still loaded, just not visible.

[tbakrf]

Thank you!  I was thinking about this in the wrong order; I am so glad you explained this!

Last question - if you did need to go back later and a column that had been deleted from a table via the data model, is there a clever way to get that column back into the model in Power BI Desktop?  Or .. just go back to recent data sources, pull a second copy of the table, copy the PQE applied steps... etc.. ?  (This is really just to understand in case it comes up down the road - I'm guessing it might!) 😄

[AmiraBedh]

Unfortunately, once a column is deleted in the data model, it is not just "hidden"; it is actually removed from the view, and there is no simple "undo" for this action.

You simply need to reimport the table, you go to PQ and refresh the metadata.