Forum Discussion

stribor45's avatar
stribor45
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Update existing column value based on custom column

I have table A with the column City.  I have a custom column in this table which basically does search in table B city column and if it finds it put 'Found" in that column.  I would like to update th...
Need Help
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi stribor45 ,

     

    you can capitalize or proper both columns, before FindTex comparison. 

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
2 years ago

Hi stribor45 ,

 

Before:

 

 

After:

 

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lHycwQRkUDCLb80L0UpVidaydjIECHnA1aQX6KAJG9iamYOlw9DlY8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call ID" = _t, #"Call Status" = _t, City = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call ID", Int64.Type}, {"Call Status", type text}, {"City", type text}, {"Custom", type text}}),
    #"Replace Values" = Table.ReplaceValue(#"Changed Type",each [Call Status],each if [Custom] = "Found" then "Found!!!" else [Call Status],Replacer.ReplaceValue,{"Call Status"})
in
    #"Replace Values"


I took the way of doing this from here:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#replace-values-in-column

 

Let me know if this solves your query 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/