Forum Discussion
Deleting columns in table view vs Power Query Editor
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.
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.
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!) 😄
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.