Merging two columns into one

[Anonymous]

Hello! I have to clean messy data. I am struggling to find a solution for 2 columns of which one column contains no value while the other does contain some information, in some instances it contains the same value or additional information. Simply merging both columns will result in duplicates. How to merge it into one column taking this into account? Thanks so much! 

Here's an example of the two columns:

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZBNbsMgEIWvMvIqVZHCAI7Z2maQXJcYYZMu0tylp+nBepLiv9ZyKnXRrni8980MzPWa0UWoNmNZdmPjBUULgSxgzrFI9t4ZqZ5S4Cz25JFLNNEcufwlWcfM59ZJuFeCc1PqI+cHSD6o9mFh5ERIo+RK7Nsx1TLAwlu2loCeo0ks9MKKxBYVxMYwcF2p58oJepIBNX8EckM5NN15HNwFAkND2Tz3cCYyZODgQ2diPSS/r0PjR/Tj7X1qkUrqVaTF3XnOdk0dBfK0GbsBQelq+oF+zdL78sigis6naS+lpfDVB1Bw50YSUJ1O+S6A/0lQ6Gqj9U+6vhPqu+gv8vYJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"Product " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Product ", type text}}),



    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.SplitAny([#"Product "],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "result", each if [#"Product "]= "" then [Type] else
    if List.Contains([Custom],[Type]) then [#"Product "] else if [Type]="" then [#"Product "]&[Type] else [#"Product "]&","&[Type]),


    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Type", "Product ", "Custom"})
in
    #"Removed Columns"

Result:

By default in the above data I regard commas as separators, you can add what you want in the Text.SplitAny([#"Product "], ",*#$@!^") statement.

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @Anonymous ,

I would probably do a conditional replace on the [product] column to get precise control of the values.

A custom step something like this would do it:

// Add as custom step
= Table.ReplaceValue(
    previousStep,
    each [product],
    each if [product] = null then [type]
        else if Text.Contains([product], "MORE DETAILS NEEDED") then [type]
        else if not Text.Contains([product], [type]) then Text.Combine({[product], [type]}, ", ")
        else [product],
    Replacer.ReplaceValue,
    {"product"}
)

This may not work exactly how you need it to now, but you should be able to work with the principle to get what you want.

I get the following output on a small example set:

Pete

[Anonymous]

Thanks @Daryl-Lynch-Bzy ! I will try this tip. 

Best,

Georg

[Daryl-Lynch-Bzy]

Hi @Anonymous - I would suggesting merging using a "Add conditional column".  This will allow you to test the columns for If Type = Product then Type else Type & Product.  Does this help?

Many thanks

Daryl