Updating subset of data in file on a filtered value

[Anonymous]

Question:  I have a subset of data I'd like to update based on more than one condition.
What is a direct way to do this?

For example: Within a large set of data I have a field called Submitted By to indicate the creator of the record.
If I wish to  change the value in that field based on a date criteria and a second criteria from another field, what would be a way of accomplishing this?

My basic attempts at 'filtering' based on Submitted by has no way to 'undo' the filter once its applied to the data in the query builder.

(or does it?  Can I filter the data to reduce the sample size to what I'm interested in, make updates to the individual fields in question in the filtered data, and then  'undo' the filter to return to the larger data set (with my changes to the filtered data intact?)

[stretcharm]

Are you working the Query editor.

You should be able create a new conditional column based on the same conditions as the filter to apply your updates. Then use this column instead of the original one.

e.g.

[Anonymous]

Hello

Yes, I am.  I considered that method and it works if i can get all the various conditions into a single conditional column update. Trouble is there are multiple conditions that produce various updates, some using the same column as a criterion (ie. Submitted by = A and Submit date is JUNE, Submitted By = B and Submit Date is JUNE,,,,, so I think If I take this approach I may have to repeat it in a loop of creating new columns with partial values updates which I would then merge and delete.  Seemed like a lot more work than should be needed

[stretcharm · Accepted solution]

You can write more complex expression.

Use a custom column with or/and

if [Store Code] = 88509 or  [Store Code] = 88505 or [Store Code] = 88501 then 99999 else [Store Code]

You can also write a function to apply a really complex transformation.

Here is a post about complex conditions

https://www.powerquery.training/portfolio/replicate-power-pivots-switch-function/

If not of this works you could create a 2nd copy filter, update.

Exclude the filtered rows in the 1st query.

Finally append them back together again.