topic Re: Update existing column value based on custom column in DAX Commands and Tips

Update existing column value based on custom column

stribor45 — Wed, 22 Nov 2023 19:24:05 GMT

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

Re: Update existing column value based on custom column

tackytechtom — Fri, 09 Feb 2024 20:29:49 GMT

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/

Re: Update existing column value based on custom column

stribor45 — Wed, 22 Nov 2023 20:29:18 GMT

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

Re: Update existing column value based on custom column

FreemanZ — Fri, 24 Nov 2023 01:17:21 GMT

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"

)

Re: Update existing column value based on custom column

stribor45 — Fri, 24 Nov 2023 01:37:09 GMT

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

Re: Update existing column value based on custom column

FreemanZ — Fri, 24 Nov 2023 02:13:23 GMT

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:

Re: Update existing column value based on custom column

stribor45 — Fri, 24 Nov 2023 13:39:05 GMT

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

Re: Update existing column value based on custom column

stribor45 — Sat, 25 Nov 2023 02:13:06 GMT

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

Re: Update existing column value based on custom column

FreemanZ — Sat, 25 Nov 2023 03:18:03 GMT

hi stribor45 ,

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