Update Group if a cell is different from others

[jimboj]

Fairly new to power query but use this forum to learn, so thanks to all who post and hopefully can help me with this problem.

What I would to accomplish is within a group of numbers in ubfmemno column, if ubfmstat equals A, then change the cells in those records in column ubfmstat that equal the ubfmemno to A. As in the example below, ubfmemno has 375 in three records, with the ubfmstat showing "I" twice and "A" once. Since one ubfmemno has an "A" in that group, I need to change the other two records in the umbfstat column to "A".

Thank you for help.

[mussaenda]

Hi @jimboj 

Please check the attached pbix if that is what you need.

I attached a screenshot of the output.

hope this helps.

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlbSUfIEYjMjc6VYHQjfEYhNDQ3hfJC8hQlU3sAAKmJqgC5iYmAEETE3hakxt0QTMTdHEzAzQxYBWW1uZoyuyRhdxMjcQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ubfmemno = _t, ubfmstat = _t, ubfacct = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ubfmemno", Int64.Type}, {"ubfmstat", type text}, {"ubfacct", Int64.Type}}),
    ActiveRows = Table.SelectRows(#"Changed Type", each ([ubfmstat] = "A")),
    BuffList = ActiveRows[ubfmemno],
    Custom1 = Table.ReplaceValue(#"Changed Type",each [ubfmstat],each if List.Contains(BuffList,[ubfmemno]) then "A" else [ubfmstat],Replacer.ReplaceValue,{"ubfmstat"})
in
    Custom1

[jimboj]

Vijay and mussaenda,

Thank you for your assistance. 

mussaenda, that is exactly what is needed, but if you left the code in the attachment, I couldn't download.

Vijay, That worked perfectly. Thank you so much!!!