Merge two columns into one, but with unique values

[Anonymous]

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 consists of a name, a semicolon, and a number, depending on the case it can have up to 3 names and 3 numbers in the same line (see last line of image), I am only interested in a list of all of the numbers, what i do in power query is:

1. Split column by delimeter (semicolon, each occurence of the delimeter)

2. From the last step I get 4 columns two with names and two with numbers, I erase the columns with the names since I'm only interested in the numbers, so I'm left with two columns

What I want is to merge these two columns into one but without just unifying the two lines (thats what happens if I use the merge columns tool), I'm drawing an example of what I want to do:

Is there anyway to do this? Thank you all in advance 🙌

[v-yueyunzh-msft]

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

[rsbin · Accepted solution]

@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,

[Anonymous]

Thank you! such a simple way to do it, so smart!!

[rsbin]

@Anonymous ,

Glad that it worked out for you and happy to help.