Forum Discussion

stribor45's avatar
stribor45
Post 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 the 'Call Status" if the city is found in another table. 

 

so before search

Call IDCall StatusCityCustom
12345NANYFound
32145NALANot Found
34567NALVNot Found

 

 and after

 

Call IDCall StatusCityCustom
12345City Found!!!NYFound
32145NALANot Found
34567NALVNot Found

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi stribor45 ,

     

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

8 Replies

  • tackytechtom's avatar
    tackytechtom
    Most 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/

     

  • stribor45's avatar
    stribor45
    Post Prodigy
    2 years ago

    tackytechtom is there a way that this can be done in DAX since I am a little more familiar with it and would also like to add some other pieces to this small code adjustment

    • FreemanZ's avatar
      FreemanZ
      Super User
      2 years ago

      hi stribor45 ,

       

      Try to plot a table visual with Call ID column, City Column, Custom Column and a measure like:
       
      Status = 
      IF(
          MAX(TableA[Custom]) = "Found",
          "Found!!!", "NA"
      )

      • stribor45's avatar
        stribor45
        Post Prodigy
        2 years ago

        but custom columns have to be generated first. where does your formula look into Table B to find a matching value in a column of Table  formula has to go through each row of Table A and look if there is a city in the current row that matches the city column in table B and if found put the word "match" in the custom column