Forum Discussion

jimboj's avatar
jimboj
Regular Visitor
4 years ago
Solved

Update Group if a cell is different from others

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 colu...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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's avatar
jimboj
Regular Visitor
4 years ago

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!!!