Update existing column value based on custom column

[stribor45]

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 ID	Call Status	City	Custom
12345	NA	NY	Found
32145	NA	LA	Not Found
34567	NA	LV	Not Found

 and after

Call ID	Call Status	City	Custom
12345	City Found!!!	NY	Found
32145	NA	LA	Not Found
34567	NA	LV	Not Found

[stribor45]

@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]

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]

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

[FreemanZ]

hi @stribor45 ,

not sure if i fully get you, try to plot a table visual with call id colum, city column and a measure like:

Status = 
IF(
    MAX(TableA[City]) IN VALUES(TableB[City]),
    "Found!!!", "NA"
)

it worked like:

[stribor45]

Is there a way to modify this so it ignores the case when comparing? It works for me when I compare "Atlanta" to "Atlanta" but it doesn't when it is "Atlanta" to "AtlanTA".

Table2WithSubstring = Table.AddColumn(#"Table A", "CitySubstringMatch", each not List.IsEmpty(List.FindText(#"Table B"[City], [City Name])))

[FreemanZ · Accepted solution]

hi @stribor45 ,

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

[stribor45]

Ahh. I see what you mean now. Thank you. This works fine but when I was posting this I posted a very simplified example so it is easier to communicate. What I am trying to do is a little bit more complex as I have to match other values. Table A and B also have State and ZIP codes as well so those need to match as well. Sorry, it was my error since I simplified the example for better understanding. My apologies.

As an example for each row in Table A I have to find if there is a matching row in Table B that has the same column values

[tackytechtom]

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/