Replace Value if any field is a certain value

[Anonymous]

I am attempting to find a good way to accomplish this find and replace.  Using the example data below, I would like to replace the "No" value in the intake column if any of the Ref IDs have "Yes" in the Intake Column.  

Ref ID	Code	Intake
15375	PR111751	Yes
15375	PR111772	No

[ImkeF]

Hi @Anonymous ,
yes, each _ basically returns the whole (partitioned) table.

Great to see it worked for you!

[ImkeF · Accepted solution]

Hi @Anonymous ,
you can:
1) Group on RefID and choose "All rows" for aggregation

2) Add a column that checks if "Yes" is included anywhere for the RefID

3) Expand all columns and add a column that checks for "Yes" and replaces if matching:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NjdV0lEKCDI0NDQ3NQQyI1OLlWJ10KXMjYBMv3yl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref ID" = _t, Code = _t, Intake = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref ID", Int64.Type}, {"Code", type text}, {"Intake", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ref ID"}, {{"All", each _, type table [Ref ID=nullable number, Code=nullable text, Intake=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ContainsYes", each List.Contains([All][Intake], "Yes")),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"Code", "Intake"}, {"Code", "Intake"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each if [ContainsYes] then "Yes" else [Intake]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Intake", "ContainsYes"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Intake"}})
in
    #"Renamed Columns"

[Anonymous]

Thanks @ImkeF !  I was hoping to avoid Grouping Rows since I have 20 other Columns of Data that I did not add to the example and I have had issues with losing columns in the past.  Is the each_ in the Grouped Rows function the reason I was able to expand and see all the columns again?  This did work great and I only received an error on the Added Custom1 step.  I used this instead:

#"Added Custom1" = Table.AddColumn(#"Expanded All", "Custom", each if [All.Intake] = "No" and [ContainsYes] = "FALSE" then "No" else "Yes"),

 Thanks again, Jared