Advanced Concatenate/ Merge columns with Power Query

Concatenating columns in Power Query is not hard to do, but depending on the technique you use, you might get unexpected or wrong results.

There is a bullet proof way to do it and I will explain it here.
Chapters:
00:00 Intro & Problem description
01:19 How power query concatenates columns with Table.CombineColumns
02:40 Issues with nulls when concatenating using a custom column
04:00 Explain Text.Combine function in power query
04:17 The final pattern/ solution
05:00 Avoid errors with nulls in the new pattern
Done!

Link to the null, blank video mentioned in this video:


SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

▼▼▼▼▼▼▼▼▼▼

If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:


Many thanks in advance!

▲▲▲▲▲▲▲▲▲▲


************





************


QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[novak]

Hi Ruth, did you check the effect on query folding? In my experience “Merge columns” broke it, whereas Excel-style concatenation was passed on to an SQL server.

[ferdinandsantos]

Thanks for sharing Ruth! Always learning new things from you.👍🏼 Just want to point out a difference between the two methods. The second method omits the leading comma in row 5 (Bike, 8). This will matter when the merged column is split back into columns as Bike will end up in the first column. So the choice between the two methods depends on what the merged column will be used for subsequently.

[guillermochavez]

Nunca dejes de compartir tus videos de trucos con PQ, los amamos. Gracias.

[sebastiencrepel]

Hello Ruth. Your channel is a real gold mine ! Thank you for all this valuable work.

[jaythevitaminjay]

Thank you for sharing this I already concatenated the columns regardless if there’s a null values.

[shaddwatson]

Hello Ruth, your videos have helped me professionally numerous times and for that I would like to thank for all you do! Keep up the great work.

[mgvivi]

Hi, i not speak english, but i like your video, is amazing. Thanks a lot. Me ayuda mucho con mi trabajo. Saludos de Perú

[thomasivarsson]

Null is a nonexisting value so it cannot have a data type. Concat Null yeilds Null is the default in databases. I did not know the trick of merge on 3 different columns. Great!

[philipkimani]

Thank you so much for sharing this knowledge. It worked with my data.

[ThGrum]

Exactly what I was looking for. Thank you so much 👋

[jensnystrom]

Hej Ruth! If I have multiple columns like the "Custom" in your video here but I want to split them out? I have column 1-5 that have a kg &value merged that represents scales but now want to split column 1-5 out to 1 - KG & 1 Value, 2 - KG & 2 - Value - any idea of how to do this? Split columns can be used obvs but generates multiple steps - is there a way to do it in one?

[mehdihammadi]

Thanks for the reminder Ruth, as always simple and efficient

[akshitmehta]

Hi Ruth, I need to do the same but with numbers instead. I have two columns with numbers and I need to combine and add numbers from both columns to the new added column :( can't figure it out

[adityasinha]

Thanks for the video! it solved the problem :). Do you have a way to add images into tabular from Excel directly or images stored locally

[abhishekstatus_]

Thanks for sharing this Ruth !! Really amazing concept !!

[norbertschmidt]

Dear Curbal or should I say Ruth,
please tell me.
Thanks for the video. GREAT. I didn't know before.

Hope to see more of your videos.

[googlegoogle-ggdp]

I have 1 m record on which I want to apply group by to get max value of each category and then same value should reflect against each category in separate custom tried it but it will take almost 2 hours and eventually system hanged....any suggestions pls

[apurvamaheshwari]

Hi Ruth

Please help me urgently, I created a calculated column in one table but the same isn't appearing when I'm trying to merge it as query to another table. How to resolve this?

[jayasreecarey]

Thanks a lot ! was looking for the exact requirement

[Jugulator]

This solved my problem - thanks Curbal! Your channel is amazing ;)