Advanced Concatenate/ Merge columns with Power Query

[@novak2033]

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.

[@CurbalEN]

Aha, that I havent tested. The M function used is not the same as the Right click 》 merge, so fingers crossed it doesn't break? Let us know if you test it!

[@ferdinandsantos6680]

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.

[@CurbalEN]

You can add that manually if needed and replicate the first method exactly 😊

[@ThGrum]

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

[@thomasivarsson2468]

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!

[@CurbalEN]

Thanks Thomas, that explains it :)

[@jayraldtajale8121]

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

[@guillermochavez7275]

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

[@CurbalEN]

Si, no te preocupes. Siempre que aprenda un truco nuevo lo mostrare aqui :)

[@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ú

[@CurbalEN]

Me alegra oirlo :)

[@sebastiencrepel5032]

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

[@CurbalEN]

Thanks 🥳

[@gabrielpugas]

Perfect presentation! Thank you

[@shaddwatson1833]

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.

[@CurbalEN]

And thanks for the feedback!!

[@elisafolletti5060]

Thanks Ruth. It seems that the result of merge and that of combine are not completely the same; in the first case ",Bike,8" in the second "Bike,8"

[@CurbalEN]

You can fix that adding the , manually :)

[@AlvaroPerez-ox7ul]

Best channel on this so far.

[@CurbalEN]

Thanks!

[@philipkimani1262]

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

[@fabienperon3065]

hi thanks. How to merge columns when we ignore numbers ok column ?

[@NicoChin]

Thank you verry much

[@akshitmehta2680]

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

[@jensnystrom8250]

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?

[@jayasreecarey7843]

Thanks a lot ! was looking for the exact requirement

[@adityasinha5167]

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

[@norbertschmidt6532]

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.

[@CurbalEN]

Hi Norbert, Curbal is me, so either works and glad it was useful!

[@mehdihammadi6145]

Thanks for the reminder Ruth, as always simple and efficient

[@CurbalEN]

I too have to remind myself to stay away from &... Habits die hard!

[@abhishekstatus_7]

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

[@navzme]

Perfect. Exactly what I was looking for. Thanks

[@CurbalEN]

🥳

[@googlegoogle-gg3dp]

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 column.....I tried it but it will take almost 2 hours and eventually system hanged....any suggestions pls

[@irinasultani4525]

Big thank you !

[@alterchannel2501]

thank you so much for this

[@Unbox747]

Nice, short and sweet!

[@CurbalEN]

That is how we like it :)

[@godfreykiggundu7787]

Wow!!! Wonderful

[@xodrinker]

Highly educational. Thx Ruth 👍

[@CurbalEN]

Perfect!

[@Jugulator31]

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

[@CurbalEN]

Wonderful to hear!

[@Bluesun0714]

Thank you for sharing the great tips.

[@dinoroy8962]

so nice, thank you...

[@MrSpiritUAll]

Nice midweek vlog❤️. See you friday🥂

[@CurbalEN]

With another DAX Fridays!! 😀

[@indykoncepts2878]

Thank you 🙏

[@paspuggie48]

Now I know 😀. Cracking solution again Ruth 👏👍

[@CurbalEN]

Thanks Paul :)

[@txreal2]

Thanks for the video. I've been struggling to find a way to concatenate parts of 3 columns to create a primary key in PQ. For ex: first 4 of Last Name, first 4 of First Name, and DOB. Any help is appreciated.

[@RonoyPaul]

Great thankful 🤗

[@mahathmasadineni2884]

I have a question here if you see last rows when there is some blank rows or nulls are not coming properly do have any vedio to solve the issue

[@CurbalEN]

It depends on what your business logic is. Post some sample data in the power bi community to get personalized help

[@mahathmasadineni2884]

@@CurbalEN thank you for your response. I will watch every day 2 vedios of you to learn a something new. Great job madam. Keep it up.

[@CurbalEN]

Then soon you will know as much as I do :)

[@Milhouse77BS]

3:25 I notice same problem with nulls involved in date calculations. I try to convert nulls to zero or empty string if use in adding a column.

[@CurbalEN]

I have been dealing with dynamic tables where I dont know how many columns will load so I need to avoid hardcoding column names. This quick trick does it:)

[@vineetyoutubr5]

Really helpful. thanks, Ruth

[@apurvamaheshwari8025]

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?

[@dianayang-manzana7712]

Hi Ruth, I've been watching loads of your vides and find them very easy to follow. Thank you! I am hoping you can help me with the following : I have a headcount data set with over 30,000 recorded for both employees and contractors combined. There are HQ County, HQ State, Work County and Work State in the dataset. I used what you shown to combine HQ County + HQ State and Work County +Work State as 2 new columns. But Power BI ended up freeze when I used the Flow Map which I think it's due to record numbers. Any suggestion on how to approach this for Mapping? Will it work if I create another table with only HQ/Work data and link to the original data ( like in another of your video) and use it for Map? Please help~ thank you~~

[@FirmanMaulanaAditya]

[Expression.Error] We cannot apply operator & to types Text and Number. can you help me? i got error like this, i've been change duplicate date to number but i just got like that

[@FirmanMaulanaAditya]

i used automatic not manual merge column

[@simonferns9487]

How do I concatenate 2 number values into one column? (Without deleting the original ones - NOT merge). I always get the null error

[@simonferns9487]

Let’s say: 3078 and 2020. The desired value is 30782020

[@haydeemera6631]

Thanks Ruth.

[@dannyhometown]

Thanks Ruth, Good one Text.Combine. Marked. :P

[@CurbalEN]

Saving me from a lot of trouble lately :)

[@raheem0046]

Item5: result should have been ",Bike,8" instead your results were"Bike,8" which is still not correct. I may be wrong, correct me if am

[@mariaalcala5159]

Thank you very much! I was wondering if you could use this function to create CSV files. With macros you need to do a lot of looping. Would it be possible?

[@StefanoVerugi]

sort of alternative to the methods shown here you could merge the row into a record, convert it to list and combine values with a comma I tried with a simple table and it correctly put all the "," as separator,to avoid type error before adding the column format all columns as text = Table.AddColumn(#"Previous step/query", "Custom", each Text.Combine(Record.ToList(_),",")) once you export it to Excel as a table, you can use the 'Text to Columns' option in data tools and save it in CSV format PS if you have null values select columns and use Replace Values in Transform (null to empty cell) before hope it helps

[@diegolozano2397]

thanks a lot

[@IlariaLongoDorni]

Thanks for suggestion

[@suryasahoo3771]

Thanks Ruth

[@bandipragnyank2038]

This is a complex process

[@ringovski1980]

Null is a column of unknown data type, a blank column is a empty string or other type.

[@CurbalEN]

Thanks!

[@MrDhunpagla]

It is wonderful 😇😇😇😇 Thanks

[@CurbalEN]

It is once you get the habit to use it:)

[@erickadx8269]

Gracias!

[@Acheiropoietos]

So&" "&helpful&", "&thank&" "&you&"."&"

[@CurbalEN]

😊😊

[@totvabe1]

Missing your forest environment … 😥

[@CurbalEN]

Going up there next weekend, will do some intros outside again:)

[@fxsignal1830]

hey Ruth, would you like get married with an italian Data scientist? :)

[@CurbalEN]

Imagine the table conversations! 🤓🤓

[@fxsignal1830]

@@CurbalEN ha ha 😂

[@sassouusasoo3607]

It's hard to understand you.

[@CurbalEN]

:(