Forum Discussion
How to batch multiple Renamed Column steps into one step
I've just watched Chandeeps latest video where he talks about 'Batching' and removing unecessary steps for renaming columns.
Unfortynately, he doesn't explain how to do this in the video: Cut Your Power Query Steps in Half (With These Tricks âš¡)
I have 8 column rename steps that I would like to consolidate if possible, can someone show me how to combine these into one step please?
Here are are the first 3 in my Query:
Renamed Columns
= Table.RenameColumns(#"Filtered Rows1",{{"ticketnumber", "Case Number"}, {"createdon", "Created On"}})
Renamed Columns1
= Table.RenameColumns(#"Expanded tpo_PrimaryTopic",{{"pre_resolutiondate", "Resolution Date"}})
Renamed Columns2
=Table.RenameColumns(#"Duplicated Column",{{"Created On - Copy", "Created On - Hierarchy"}})
Thanks
ArchStanton
Hi again
In answer to your question "My M code knowledge is very limited, can you provide an example of how this code will look"You can learn yourself.
Simply create some test data
Edit the query and overtype the columns.
The Power Query will automatically generate the the M rename code in the top window
If you click Advanced Editor then you can view the whole M script
This is how most developers learn M code,
by first using the wizard to crearte code
and then editing it.Learn more about Power Query for free here
https://www.youtube.com/watch?v=i66yyAl5_JcPlease click thumbs up and [accept solution] buttons.
5 Replies
ArchStanton
Hi again
In answer to your question "My M code knowledge is very limited, can you provide an example of how this code will look"You can learn yourself.
Simply create some test data
Edit the query and overtype the columns.
The Power Query will automatically generate the the M rename code in the top window
If you click Advanced Editor then you can view the whole M script
This is how most developers learn M code,
by first using the wizard to crearte code
and then editing it.Learn more about Power Query for free here
https://www.youtube.com/watch?v=i66yyAl5_JcPlease click thumbs up and [accept solution] buttons.
Hi ArchStanton
It is best pratice to group Renames into a single step rather than have the steps spread over a Power QueryFor example instaed of:-
- Add column 1
- Rename column 1
- Add column 2
- Rename column 2
- Add column 3
- Rename column 3
You could have :-
- Add column 1
- Add column 2
- Add column 3
- Rename column 1, 2 and 3
In your example, I suggest fisrt you back-up your work before making any changes.
Then delete the Renamed Columns, Renamed Columns1 and Renamed Columns2 steps
and add a new step that renames all three columns.
Unfortunately, if you have any references to the new name before new step then they will fail with name not found error, but you can easily edit these to the oldname. Then it work ok.
Please click thumbs up because I have tried to help.
Then click [accept solution] if it works.
Thank you
Hi, thanks for the reply.
My M code knowledge is very limited, can you provide an example of how this code will look using the 3 examples I've provided please?
Thanks
Thanks, unfortunately I'm stuck with these multiple steps as the query fails when I remove and try to edit column name changes, this is because in the middle of all of these transformations - I have merged queries and done a whole host of other steps that I have unsuccessfully tried to pick apart and edit but failed.
Thanks for you help anywayHi again ArchStanton
In repy to your comment "Thanks, unfortunately I'm stuck with these multiple steps as the query fails when I remove and try to edit column name changes, this is because in the middle of all of these transformations - I have merged queries and done a whole host of other steps that I have unsuccessfully tried to pick apart and edit but failed."Please can you kindly click [accept solutoon] because the solution works
I have provided you wth the solution, examples and a training video link but it sounds like you have given-up trying to implement it.
We all started as novices and had to go through the same excerise of unpicking M code to learn.
😀
