Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Update a text column from other row data

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?

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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!

5 Replies

  • Manoj_Nair's avatar
    Manoj_Nair
    Solution Supplier
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        v-jingzhang
        Community Support
        3 years ago

        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!