Concatenate, Merge or Combine multiple rows into one value - Power Query for Power BI

A relatively new feature of Power Query that helps you concatenate, merge or combine multiple rows of data into a single value with just a few clicks. The video showcases some advanced scenarios where you might want to shorten the end value and only take in consideration a few elements to concatenate.
Download the Origin file here:
drive.google.c...

[@lenac3587]

This is exactly what I'm looking for to compile a list of survey questionnaires! We have seen alot about merging columns but less about merging rows. After 2 years this is still so relevant. Danke!

[@davidpwongphoto]

I started using Power Query on and off. OMG this is so good. Exactly what I needed.

[@davidpwongphoto]

The other great things you talked about the multiple sort columns with 1 and 2 where the text was so dark, userscan't really see them. So I learned 2 things in one video. Please do more.

[@industrialrevolution2884]

You are a genius, I've spent 10 hours trying to read forums to achieve the same and i stumbled upon your video. I got what i was looking for, thank you very much. Subscribed as well.

[@ThePowerUser]

glad this helped! :)

[@prabhupandiarajan862]

Fantastic. Spent almost a day to combine the multiple rows in one cell.

[@lukasseifriedsberger3208]

Thanks bud! Really didn't know that amazing concatenation feature of grouped rows existed, and it was EXACTLY what i needed!

[@decentmendreams]

Greetings, I am always amazed by your strong command of PowerBI/Power Query. Your analytical skills are just incredible. I follow all your videos which I find them very helpful. Thank you so much.

[@davidubl1288]

Thanks for introducing me to Aggregate All Rows and Table.Column. Incredibly powerful! Kick started my thinking to solve a problem I've been struggling with for a long time.

[@nicheplayer]

What David said. This is *super* helpful.

[@hapcoding]

Thank you. It really solve my problem. What I want is make a unique row before pivoting my column. Your video solves my problem. Thank you so much.

[@athulyaok9633]

Mine too

[@REgamesplayer]

To think that I tried to do this task for a week. I could not find anyone talking about in forums. I tried programming, but PBI seems to be particularly awful with for functionality if it even has it. You however had brought exactly what I needed to finish the most annoying second half of my project. I had a very nasty DWH table with relevant data not only in different rows, but columns also. It took an ungodly amount of time and actions just to clean it up properly.

[@phyberoptx]

This helped me avoid some snowflaking in my data model. Thank you very much!

[@vimalmanavalan]

one of the coolest video i ever seen in powerbi. thanks alot for the explanations. it really helped me a lot.. love from India

[@sahilsharma-cx4bp]

Thank you so much, it solved one of my biggest issue

[@chrisnosal]

this was a very useful video. I was googling what I was trying to do and got lucky as this was the first video that came up and was exactly what I needed. Thanks!

[@osmanyldiz]

I was about to go ballistics before I find this, you solved my problem, thanks mate

[@natusikyagusik]

I am so thankful that I found your video. I am new to the Power BI, and this method was a lifesaver. Thank you for sharing!

[@ThePowerUser]

glad to hear that! thx for watching

[@natusikyagusik]

@@ThePowerUser Question. Once I convert a column into the list, I am losing the automated update feature. Every time I need to re-run the report I'll have to rebuild the list. Is any way to perform the same function without losing connection to the data in the source table?

[@ThePowerUser]

Natasha hey! I’m not sure I’m following. I’d recommend that you post your full scenario and samples to the official power query forum: social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@ensysconsultores]

Just what I needed. I was playing around with get data from PDF and some lines of text were split in several lines. With this trick I can put them back in the same row. Thanks

[@sirasnet6499]

You are the best I have seen in power query. Good

[@jb360360]

Mind BLOWN in 2021. Thank you 🙏🏾 sir.

[@naomieketa7823]

Thank you! If you only knew what this unlocked and how much of it helped...

[@ljubomirtepsic3780]

thanks a lot :) if you ever go to Zagreb, Croatia please contact me for a beer :)

[@hob0k1ller]

Excellent! Just what I was looking for. Saved me so much time!

[@valentaci]

Exactly what I was looking for. Many Thanks!

[@TechGuyChad]

Thank you very much for this. I was struggling to figure out how to do this for my invoices to multiple part number aliases and this worked perfect! Thank you thank you thank you!

[@AP-eb8hd]

Exactly what I was looking for. Thanks for the upload.

[@richardpearcephotography599]

Thank you!!!! Like many of the comments below, exactly what I was looking for.

[@LearnersPage]

helps a lot in real time work .....great knowledge sharing ....brilliant work ....Keep doing ....

[@frearfactor6680]

Excellent explanation, very useful. Thanks so much

[@dashound6]

So very very close to what I need to do! Thanks for the point in the right direction!

[@ThePowerUser]

nice! Glad to know that you were able to reach your desired solution

[@dashound6]

@@ThePowerUser I ended up with a combination of Group By and using a filter to achieve success. Thanks again!

[@topjimmy44]

Beautiful! Exactly what I needed. Great explanation.

[@PaulBailey3]

This was very useful. Thank you.

[@ulisesgabrielignotflores6291]

This made my life easier, thank you!!!

[@Chris4BigD]

Excellent solution. Very easy to follow and adapt.

[@fodoranna3977]

Your explanaitions helped me so much! Thank you a lot, I loved

[@stevengregoire5234]

The video was what I needed when I needed it. Thank you

[@gustavomartinho5305]

Thank you very much! It was pretty hard to find this solution and you made it a piece of cake.

[@ThePowerUser]

nope - thank YOU for watching the vid :) glad you found it helpful

[@yemiakinwande7039]

Thank you - this is Very, very, useful - just what I needed at this time. High quality tutorial, well explained and Super clear.

[@adunbar22]

Excellent Video! This is exactly what I was looking for and needed. Thank you very much.

[@chakrabmonoj]

Hey - this is an excellent lesson. I was wondering if the same technique could be modified to return the bottom 3? would these work : 1. If we sorted sales in Ascending order? or, 2. If we changed the FirstN parameter to -3, for example? Or, 3. Is there a BottomN equivalent in M?

[@ThePowerUser]

I think it's probably better if you define the sort (perhaps ascending or descending) and then grab the top 3 rows that way. There is a Table.LastN function, but it'll rely on your own logic. Sorry for the late reply.

[@nboisen]

Brilliant Video. Very, very helpful. Thanks

[@iannorth5112]

Awesome, just what I needed. Thankyou very much.

[@gabrielmolina7934]

Thank you, it was so easy here than in SQL

[@joefromdc]

Good wrk, but next time pls zoom in more often. At times it was hard to see

[@jonathankemp6803]

Nice one bro, that has saved me!

[@karensunshine]

Excellent, I was able to redo with no problems, thank you

[@kigayo0834]

As a reference, I practiced. Thank you very much.

[@ThePowerUser]

Nope! thank YOU for watching the vid :) hope you enjoyed it

[@trileyguy]

Thanks for the video, very helpful!

[@VIBHOREVARSHNEY]

This doesn't work with Direct Query. Any alternative for Direct Query Models?

[@jonathanschmidt1268]

Do you have a solution if you're trying to consolidate based on multple criteria? In your example above, what if you were trying to summarize the sales person and region, but you were also trying to break out this data by year. I tried to do this using your method in the video, but when I brought in the extra dimension Power Query did not like it. Any solution would be a big help.

[@ThePowerUser]

it really depends on what your output should look like. I highly suggest that you post your full scenario with sample data and sample output on the official Power Query Forum> social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[@rajsekharjesollureddi8737]

It's very valuable information and understandable very clear thank you

[@Ihatesxhoolsomuxh]

This is great, I followed your process but I have duplicate rows under "SalesPerson" (User ID) example because I have multiple rows due to other categories. So when I merge and extract values I am pulling duplicate values. Is there a way to group by distinct User ID? hope this makes sense.

[@ThePowerUser]

hey! is up to you. You can choose the way that you group things - it can be based on multiple columns and not just on one. Here's the official documentation on how you can use the group by feature with multiple columns: learn.microsoft.com/en-us/power-query/group-by

[@simonduffill3355]

Fantastic function and explained very well. thanks so much.

[@faisallodhi4891]

Thank you, exactly what i needed.

[@zinaromanov9899]

Well done. that was exactly what I need :)

[@matussensel8026]

Thank you for this video! Helped a lot!

[@davidalvarado3455]

Thank you so much!

[@DhruvDua88]

You are a power query god.

[@timothylim8728]

Thank you! I been looking everywhere and they all just say group by and "All Rows" but that didnt do the job

[@justinalnz]

When I load the conditional column to table, all data are displayed on the same line even though i have used "Carriage Return" as deliminator. What shall I do to have the data line by line line in the same cell? Thanks!

[@26SSD]

instead of "Carriage Return" select "Line Feed"

[@mr-ys3wr]

hi! Is there a way to keep the other columns?

[@siddarthak.l.1898]

Thanks you saved a lot of time

[@bhattrahul78]

It is great , very well explained

[@cesarmr111]

Thank you very much!. This was very useful, exactly what I wanted!. Genius!

[@wasio44]

Exactly what i needed! thanks!!

[@geetanshu100]

Doing a great job making these videos. Keep it up..This was really helpful

[@AlessandroSilva-dl3vq]

olá ótima aula Obrigado pelas dicas!. Você tem algum exemplo que mostre de uma tabela Clientes e outra Tabela Vendas quais cliente não estão comprando para ser mostrado em um mapa . onde também possamos filtrar com segmentadores por tipo de produtos ou por vendedor quais eles não atenderam na base de clientes. Obrigado pela atenção desde já!

[@rauljimenez5485]

Isso seria melhor fazer-lo com DAX, nao com M Faría uma medida na qual voce filtra a tabela de clientes com a condicao [Soma de Vendas] = 0

[@rafiffauzan859]

Hi, I have tried this technique but the value shows data format error: We couldn't convert to number. All the columns in text and this made me confused. I can't find the solution even in powerbi forums. Thanks

[@ThePowerUser]

hey! could you share the link to your question on the Power BI forum?

[@trevormorethe4432]

my group By function ignore previous sorting step, what may be the cause?

[@ThePowerUser]

hey! basically when the grouping happen some things get stripped away as the order of the rows gets shifted. You can buffer your table before doing the grouping and that should make sure that the order of your rows remains intact

[@KemalYerlikaya]

Great work. This video is a life saver. Thank you.

[@fullsailbrad]

Thank you. This was perfect!

[@xiangyu326]

Very well explained, thank you.

[@rafaelurgellesorue9486]

Thank you VERY MUCH! You are doing great!

[@thijswestra4595]

Hello thanks for the effort and sharing with us I am using adding a new custom column select the columns I need and combine them with the & sign including the spaces and other signs I need. Instead of concatenate This works Are there any disadvantages in the way I do it?

[@ThePowerUser]

hey! I wouldn't compare these as those are 2 fundamentally different ways to concatenate values. One is a one-to-one relationship, and the one showcased in this video is for one-to-many relationships

[@thijswestra4595]

@@ThePowerUser aah yes i understand now.... Thanks

[@danielbarreto4227]

Excelente Video, me aclaró muchas dudas.. Gracias

[@user-du1uk7lt8j]

VERY useful. Thank you

[@LotfyKozman]

Awesome video. It is very useful and practical example

[@ThePowerUser]

thanks!

[@MuhammetMustafaAslan]

Great video, Thanks very much!

[@michaeljones2843]

Awesome example, thank you very much, I'm going to try this right away. :-)

[@ThePowerUser]

thank you for watching the vid! hope you enjoyed it

[@ashish31095]

Thanks. It helped.. But I need a filter in the report that is a dropdown list of all these values, but not comma separated. On selecting a value from this filter, it should show the comma separated result containing that filtered value.. Is that possible!? Appreciate your help :)

[@ThePowerUser]

what about duplicating the column? that way you keep the original and do the transformation on the other one. That would be the main concept.

[@ernestagrikinaite-bartkevi5346]

Thank you! How can I remove empty values before creating the list? I now receive this result A, , B, , , , but I only want to have this A, B

[@ThePowerUser]

hey, you can select the column, go to the transform tab and then select an icon that reads "trim". That should get rid of the empty spaces from the values within that column

[@jenga52]

Hi! These videos are amazing! I'm a bit stuck though...I'm using excel 2013 power query...I have got as far as to get the 'list', but I have no option to 'Extract Values'...how can I work around not having this option?

[@ThePowerUser]

Hey Tom. Thanks for the comment. The problem might be the version of Power Query. I'd recommend that you try download the latest version of Power Query for Excel 2013 from here: www.microsoft.com/en-us/download/details.aspx?id=39379

[@wolfrey11]

Very, Very helpful. Thanks alot!

[@annmunro9468]

That is genius. Thank you for sharing that.

[@goureshgundami5239]

Can we do it by DAX method? I mean not to use power query.

[@ThePowerUser]

probably yes. Concatenatex could help you with it.

[@jamezday]

Bro this saved my life 👍

[@rogervandecraen9797]

great usefull tip !! thanks roger

[@seanjames8126]

Thanks for this amazing video

[@enginklc2448]

Gratitude for your work!

[@gustavotubino8831]

Excellent! Thnaks for you help!

[@kartickumar638]

Awesome video. Though stuck as my values in not aggregating for Amount. Please let me know what i am missing ?

[@ThePowerUser]

Hey Kartic! Would you mind posting your full scenario with sample dataset and sample output on the official Power Query forum? social.technet.microsoft.com/Forums/en-US/home?forum=powerquery I'm super active in there and that would be the best place to chat about your situation.

[@kartickumar638]

Raised a question social.technet.microsoft.com/Forums/en-US/d04c9a21-f7dd-4b19-af8f-9cb51bf5efc5/aggregating-the-value-of-account-after-combining-the-multiple-row-into-one?forum=powerquery

[@Amr-Ibrahim-AI]

Thank you for the good tip.

[@sergiomira741]

Hi friend!!! greetengs from Colombia... I have a question.. If I Want concat all values in a cell and after to creat a variable it's posible? For example I have a column named DATA 1 2 3 4 and I want to build a variable named Data_number with all number and to add simple quotes... I would need this result... Data_number ('1','2','3','4')

[@ThePowerUser]

Hola Sergio! ¿Qué tal si agregas las comillas antes de concatenar los valores? Creo que sería la mejor opción. No estoy 100% seguro de a qué te refieres con lo de crear una variable, pues puede interpretarse de diferentes maneras dependiendo de la integración de Power Query, pero si es solo crear una consulta dentro de Power Query que te brinde ese valor deberías de poder crearlo sin problemas luego de resolver el tema de las comillas. Saludos desde Panamá!

[@mv_nirvana]

How do we get rid of duplicate values in the extracted list?

[@ThePowerUser]

you could use the UI by clicking the "remove duplicates" button

[@mv_nirvana]

@@ThePowerUser that doesn't work in duplicates in a single cell. I went with List.Distinct

[@ThePowerUser]

​@@mv_nirvana the UI has an option to "remove duplicates" when you're either in a List or a Table. It uses Table.Distinct for tables and List.Distinct for lists. When you're in the "List" contextual menu you will get a "Manage items" group and the option to "remove duplicates" will be there. If instead you're trying to remove duplicates within a string, that would be something different and you might need to use Text functions for it and define what substrings you might want to consider a duplicate.

[@sifisongobese3113]

Hi, Thank you so much. This really helped me.

[@alexrodrigovieira4093]

Thanks man, very helpful!

[@m1m2004]

Nice work! it is possible to do it in directQuery mode?

[@ThePowerUser]

dont think so

[@kochharmanish]

This is really really really SUPERB!

[@babakroostai635]

Thanks for that saved my time...

[@diegolozano2397]

muchas graciss compañero por el video, mas adelante podrias hacer uno en español, abrazos desde colombia

[@ThePowerUser]

Hola Diego! ¿Qué tema en específico te gustaría ver en español? Ando en el proceso de fijarme un nuevo horario para los vídeos y ando pensando en crear otro canal en español

[@kristofferaamot7914]

Thanx, man! You really saved my day :)

[@amuthavan660]

Awesome really helped a lot...👍