Forum Discussion
Helper IAutomatically rename any new values with a certain prefix?
Hello.
I have a dataset with many entries like the following examples.
| JCOS Sign 8411 |
| JCOS Sign 8411 |
| JCOS Sign 8409 |
| JCOS Sign 8409 |
| JCOS Sign 8409 |
| JCOS Sign 315 |
| JCOS Sign 8724 |
I would like all these entries renamed to just “Signs” so that they are all combined in the report. This data source is always growing, so I’d like something that would automatically replace any entry that starts with “JCOS Sign” with just “Signs”
This would be easy enough to do in Excel with find and replace using wildcard “JCOS Sign *” but I’m not finding any easy way to do this in PowerQuery.
Any suggestions?
Thanks!
I read this as you wanting to change "JCOS Sign 1234" to just "Signs". If so, then the easiest thing to do would be to add a custom column. If your current final step name is LastStep, and your JCOS column is named "IDs", then you can click on the Custom Column button, and in the formula dialogue, type:
= each if Text.StartsWith([IDs], "JCOS Sign") then "Signs" else [IDs]
Then you can delete the original "IDs" column.
--Nate
3 Replies
I read this as you wanting to change "JCOS Sign 1234" to just "Signs". If so, then the easiest thing to do would be to add a custom column. If your current final step name is LastStep, and your JCOS column is named "IDs", then you can click on the Custom Column button, and in the formula dialogue, type:
= each if Text.StartsWith([IDs], "JCOS Sign") then "Signs" else [IDs]
Then you can delete the original "IDs" column.
--Nate
Sean2Thanks Nate! That was the piece I was missing. I used else [IDs] to get all the other non JCOS Signs entries into that column and it's working perfectly.
Thanks you!
- mahoneypat
Microsoft Employee
You can just highlight that column in the query editor, then choose Replace Values in the ribbon, put JCOS Sign in the top box and Signs in the bottom.
Pat
