SOLVED

Power Query - Best Practice to Reduce # of Steps?

Steel Contributor

Hi,

 

I'm fairly new to power query, and was curious if I should be condensing/waiting to apply steps instead of applying steps as needed (like how I would work in excel).

 

In this query I'm working on, I ended up moving columns into the correct order as I finished manipulating them, but it struck me that maybe I should just wait for the last transformation and reorder them all in one go. Same thing could be applied for changing types, renaming, and removing.

 

Duplicate steps:

DKoontz_0-1646759011969.png

Thank you!

 

2 Replies
best response confirmed by DKoontz (Steel Contributor)
Solution

Hi @DKoontz 

 

Reordering columns n times makes no sense from an efficiency perspective. You should re-order only once i.e. at the end of your query

Re. renaming columns it depends what your query does. Assume you have a table with [Column1],[Column2]. If you rename [Column1] and later delete/remove that column, for sure it makes no sense to rename it. On the other hand, if in one step you rename [Column1] and in another step rename [Column2] (as long as you're no going to delete any of those columns later) this shouldn't make a differerence from a performance perspective (vs. renaming the 2 columns in a single step)

Regarding type changes it depends on the scenario as some transformations/operations "loose" columns types and you might want/need to re-type some columns before moving forward
A common "mistake" is to add a custom column without typing it then to add a change type step: If you add a column theoritically you know its data type so type it when adding that column

 

Couple of other points:
- Other common "mistake": a step that changes the type of [Column1] followed by a delete/remove [Column1] when [Column1] isn't involved in other calc. in the meantime
- As a general rule: get rid of the columns and rows you don't need as soon as possible
- The number of steps of a query doesn't necessarily correlates with its efficiency
- There are non-obvious things that only MSFT people know. E.g look at Curt H. reply to Table.AddColumn + Table.RemoveColums vs Table.TransformColumns from performance point of view

Thank you for the in-depth response, I really appreciate it! That is a good tip on typing custom columns, I've already seen myself do that and have gone back through and fixed those errors.

Definitely need to start thinking about speed but I was also worried about having unnecessary steps being more complicated for anyone else to go back into and look at. Seems like so much of PQ is future proofing, and that takes a whole different mindset than I am used to.

Thanks again!

1 best response

Accepted Solutions
best response confirmed by DKoontz (Steel Contributor)
Solution

Hi @DKoontz 

 

Reordering columns n times makes no sense from an efficiency perspective. You should re-order only once i.e. at the end of your query

Re. renaming columns it depends what your query does. Assume you have a table with [Column1],[Column2]. If you rename [Column1] and later delete/remove that column, for sure it makes no sense to rename it. On the other hand, if in one step you rename [Column1] and in another step rename [Column2] (as long as you're no going to delete any of those columns later) this shouldn't make a differerence from a performance perspective (vs. renaming the 2 columns in a single step)

Regarding type changes it depends on the scenario as some transformations/operations "loose" columns types and you might want/need to re-type some columns before moving forward
A common "mistake" is to add a custom column without typing it then to add a change type step: If you add a column theoritically you know its data type so type it when adding that column

 

Couple of other points:
- Other common "mistake": a step that changes the type of [Column1] followed by a delete/remove [Column1] when [Column1] isn't involved in other calc. in the meantime
- As a general rule: get rid of the columns and rows you don't need as soon as possible
- The number of steps of a query doesn't necessarily correlates with its efficiency
- There are non-obvious things that only MSFT people know. E.g look at Curt H. reply to Table.AddColumn + Table.RemoveColums vs Table.TransformColumns from performance point of view

View solution in original post