Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Merge two columns into one, but with unique values

Hello everyone, I was wondering if there is a way to convert two columns into one but without merging the two data, let me explain further: I have this data and as you can see this column cons...
Need Help
Tips and Tricks
Tutorial Requests
  • rsbin's avatar
    rsbin
    3 years ago

    Anonymous ,

    I would approach it this way:

    1) Copy your first query to create a second one.

    2) In the first query, delete column 2.  Rename Column 1 to New Column

    3) In your second query delete Column 1. Rename column 2 to New Column

    4) Use the Append Queries function to append these columns together.  I would choose "Append Queries as New"

    5) Then you can delete the first two queries.

     

    Hope this or some alternative to this will help you get on your way.  There may be a cleaner solution, but I thought that I'd at least throw this idea ou to you.

    Regards,

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi , Anonymous 

We can realize it in Power Query Editor,Here are the steps you can refer to :

(1)This is my test data :

(2)We can put this M language in "Advanced Editor":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykvMTTW0NjQyBiGlWJ1opVygiJG1kaGxoaF1SkZpYgZM1ggsDdJQDpa3MDQyt05JLM1IKYUqAfKAsDgRxIWaBlIONgxiQCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [test = _t]),
    test = Table.TransformColumnTypes(Source,{{"test", type text}}),
    Custom1 = Table.SplitColumn(test,"test", (x)=>Text.Split(x,";"),1,null,0),
    Custom2 = Table.TransformColumns(Custom1,{"test.1",(x)=>List.Alternate(x,1,1)   }),
    #"Expanded test.1" = Table.ExpandListColumn(Custom2, "test.1")
in
    #"Expanded test.1"

(3)Then we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

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