Forum Discussion
Where is the best place to remove columns? In power query or sql tables
Hello
I needed to clear the report of unnecessary columns.
I had a disagreement with a colleague about where to do this.
In power query or in the sql source itself.
Is there a difference?
Hi, pavel118
Here are some considerations:
- Removing columns in the SQL source can improve performance because it reduces the amount of data transferred from the database to Power Query. This can be especially important if you are working with large datasets.
- If the unnecessary columns are not needed by anyone else, removing them at the SQL source can help maintain cleaner and more efficient databases. However, if other reports or processes rely on those columns, it might be better to handle the removal in Power Query to avoid disrupting other users.
- Power Query offers a user-friendly interface for transforming data, including removing columns. This can be more convenient for users who are not as comfortable writing SQL queries. Additionally, changes made in Power Query are easier to modify and revert if needed.
- If you have multiple reports or queries that use the same data, removing columns at the SQL source ensures consistency across all reports. On the other hand, if only a specific report needs the columns removed, handling it in Power Query might be more appropriate.
In summary, if performance and data governance are your primary concerns, removing columns in the SQL source might be the better option. If flexibility and ease of use are more important, Power Query could be the way to go. It ultimately depends on your specific needs and the context of your data usage.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- audreygerred
Super User
When you are adding a column you always want it done as far upstream as possible, but it's OK to select which columns you want right in Power Query, and do it as a very early step so that it query folds. As long as it is query folding that step will actually happen at your source and not in Power BI engine. Leaving the columns in source and just removing in Power Query will make it easy for if you or anyone else every does need the field. All of this assumes your sql source follows star schema, but even if it doesn't you can still pick which fields you want from PQ.
Hi, pavel118
Here are some considerations:
- Removing columns in the SQL source can improve performance because it reduces the amount of data transferred from the database to Power Query. This can be especially important if you are working with large datasets.
- If the unnecessary columns are not needed by anyone else, removing them at the SQL source can help maintain cleaner and more efficient databases. However, if other reports or processes rely on those columns, it might be better to handle the removal in Power Query to avoid disrupting other users.
- Power Query offers a user-friendly interface for transforming data, including removing columns. This can be more convenient for users who are not as comfortable writing SQL queries. Additionally, changes made in Power Query are easier to modify and revert if needed.
- If you have multiple reports or queries that use the same data, removing columns at the SQL source ensures consistency across all reports. On the other hand, if only a specific report needs the columns removed, handling it in Power Query might be more appropriate.
In summary, if performance and data governance are your primary concerns, removing columns in the SQL source might be the better option. If flexibility and ease of use are more important, Power Query could be the way to go. It ultimately depends on your specific needs and the context of your data usage.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
