Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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:

Amanda_Mendez_0-1666203769772.png

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:

Amanda_Mendez_1-1666204431506.png

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

 

1 ACCEPTED SOLUTION
rsbin
Super User
Super User

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

View solution in original post

4 REPLIES 4
v-yueyunzh-msft
Community Support
Community Support

Hi , @Anonymous 

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

(1)This is my test data :

vyueyunzhmsft_0-1666231613056.png

(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:

vyueyunzhmsft_1-1666231667022.png

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
Super User
Super User

@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
Not applicable

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

@Anonymous ,

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.