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


 

 

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

     

3 Replies

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