Update a text column from other row data

[Anonymous]

My case is the following:
I have a dataset with many rows and columns. One column is the description which I want to update its data with a condition.

Example data:

In the 'New Description' Column, I am trying to have the value 'the description I want to update with' for the three rows with code AAA000 and the rows with code AAA111 to have as New Description 'Other Description' etc.

Any ideas?

[Manoj_Nair]

Desired output 👆

@Anonymous - try this calculated column measure 

New Description = IF('Table (2)'[Code] = "AAA000", "the description I want to update with", "Other the Description").

Let me know if this resolve your query the MARK AS ACCEPTED SOLUTION. Many Thanks

[Anonymous]

Thank you for your answer.
However, I can't hardcode the description. In my real data, there are thousands of data that I need to update. I need an expression like this: for code 'AAA000', replace the description that starts with 'old' with the description that does not start with 'old'.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can add a conditional column first. 

Then group by Code column and add two new columns as below. 

Finally expand "All Data" column and select only old Description column. 

Full code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NDAwUNJRKslIVUhJLU4uyiwoyczPU/BUKE/MK1EoyVeK1UFSlp+TAlaGX9TQ0BAkCjSzCNlQNGk0TUZGRiia0MSxq4aLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Description", each if Text.StartsWith([Description],"old") then null else [Description]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Code"}, {{"New Description", each List.Max([New Description]), type nullable text}, {"All Data", each _, type table [Code=nullable text, Description=nullable text, New Description=nullable text]}}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Description"}, {"Description"})
in
    #"Expanded All Data"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[Anonymous]

I think this is what I am looking for. Thank you all, folks!

[Manoj_Nair]

@Anonymous - not very clear about your requirement, but try this or tinker the DAX if required.