Excel Magic Trick 1422: Power Query M Code: Join all Items From Invoice into One Cell

Download Files:
Learn how to use the Power Query user interface and type M Code to quickly take all the items from an invoice and list them in a cell with a delimiter. The Data is in a Proper Data Table Format and we want to group all invoice items together in a single cell.

This trick comes from pmsocho at YouTube.

Power Query Underscore = Get all the data in the row

[ljubicar]

Thanks a lot for this video! I saw it today and already using this amazing trick! In this video, you joined items from one column, and I needed items from 2 columns to be displayed as a list, so I merged that two columns in one (since I didn't know how to write M code for both columns), used your trick, and after I finished transformation I unmerge them. Thanks again! I am thrilled with this. By the way, this option "Extract values from list" doesn't work in previous versions of power query, so I had to use Power BI advanced editor.

[mohamedchakroun]

Great Tips Thanks to Pomoscho what a great team

[karljolivet]

I actually needed to do this tomorrow for a report. You just saved me a lot a time. I always love your videos

[aduck]

I was looking for this :) thank you very much for sharing your knowledge.

[rrrprogram]

Great one mike... I am now watch all those videos which i have missed earlier...

[SanjayKumar-yxgc]

Awesome & very informatics video, Mike. We always like & share your videos.

[zohurulislam]

This is what I have been looking for a very long long time. Thank you sir.
But if in the list (item) there are duplicates, is there any way to remove that?

[drsteele]

Super video, Mike. I wonder though, what would we do if there were duplicates but we wanted them omitted from the resulting Table? For example, add another 1003 Quad. How would we then get just a list of all the Invoices with Items but listed just once each?

[Alexey]

how to make the concatenated
values in cells been sorted?

[Sal_A]

Cool tip. Why do you use a _[Item] instead of just [item] since you are only interested in the column instead of the Invoice and Item column? Does _ [column name] denote all rows for a specific column? Does _ alone with no column specified denote all rows for all columns?

[Meolimo]

Hi Mike I wonder if you or somedy else can help me, I looking to find the equivalent of the Excel MOD function convers in M code ?
Thank you for your precious help :-)