Forum Discussion
Override/Edit a Single Cell
I have a data set that pulls in a long text ID that translates to a project name. I normally would replace values on the ID to the Project name and that worked in most cases. However I have some one offs in the data where the unique ID needs to be split into different project names. I can't replace text because it will replace all of the cells. I need to be able to edit these one offs manually. Is this possible?
The column I need to edit is a text field and I am pulling the data using visual code/ODATA.
I struggled through this and couldn't get it to work. I however was able to fix it using a conditional column. The value I needed was in another column so using the custom conditional column I was able to say if it matches this unique ID then grab from that other column.
4 Replies
You can modify Table.ReplaceValue to edit rows only under certain conditions.
For example, if you replace "A" with "aaa", in [Column1] the code looks something like this:
= Table.ReplaceValue(#"Changed Type", "A", "aaa", Replacer.ReplaceText, {"Column1"})Suppose you have an index column and you only want this replacement to be valid for the row where index = 6. Then you could write
= Table.ReplaceValue(#"Changed Type", "A", each if [Index] = 6 then "aaa" else "A", Replacer.ReplaceText, {"Column1"})Sorry I am having a hard time following this one. Would I be doing this in the Query itself?
In this example my column is named: Project and I am trying to edit row 25.
Thank you,
Yes, you'd add a Replace Values transformation step to your query and then modify as needed similar to the example I gave.
I struggled through this and couldn't get it to work. I however was able to fix it using a conditional column. The value I needed was in another column so using the custom conditional column I was able to say if it matches this unique ID then grab from that other column.
