Power Query - Group Suppliers, Join with Line Feed From Multiple Text Files - Excel Magic Trick 1580

In this video we create a Material Supplier Report with All Suppliers Listed in Single Cell With Line Feed. We use a combination of Power Query Built-in featured like Group By and Merge Columns, and then we edit the M Code and use the Power Query Functions: Table.AddIndexColumn, Combiner.CombineTextByEachDelimiter, List.Transform, Text.From and Text.Combine. We also learn about a Line Feed in Power Query (Line Feed in M Code): #(lf) and how to use multiple delimiters in list syntax when merging or joining columns in Power Query.

Related Videos:
Count, Join then Lookup Suppliers & List in Single Cell With Line Feed, Excel Magic Trick 1579
Unique Invoice / Vendor List with Items Listed Horizontally - Excel Dynamic Array Formula- EMT 1575
Power Query M Code Group By Formula to Transform Invoice Table - Excel Magic Trick 1576

[arnaucs]

I found your channel 3 days ago while looking for some videos related to Power Query, and to be honest, my jaw literally dropped when I saw the amount of effort that you put on every video (really instructive exemples, exercises, PDF's that give you a step by step of every video).
I am one year away of finishing my double degree in statistics and economics and I haven't learnt anything of this powerful tool, and the fact that I have a talented person like you explaining something that can be life-changing for me as an statistician is amazing. I can't discribe in words how grateful I am for your filantropy.
Thanks.

[mattschoular]

Fantastic! There are lots of great functions and tricks for M code in this video. Thanks Mike.

[NoShadowOfDoubt]

This example is great for an AR aging report .... nice

[jyotipatel]

Hi Mike, it is awesome. Thanks for help.

[ContentedSoul]

Mike, Once you have your merged list you could have used the "expander" button in the header, which would have given you two options; the second one to concatenate the values and thence the method of concatenation. This would have saved you amending your written code. Nice problem to chew on!

[ayobamiologun]

Mike is just wow!!! Have you been invited over by Microsoft at all, with the way you untie the knots in their products...?

[rrrprogram]

Uff... You are AMAZING MIKEEE.... Looks like... I need to revisit all ur previos power query videos.... Lot to catch up...

[GeertDelmulle]

Every time I watch your next PQ video you take it to the next level...
Great stuff!

[tryoutlearn]

You are a magician. I love your magics in excel.

[wayneedmondson]

Hi Mike.. WOW.. Power Query M Code magic. Going to have to spend some time in MSPTDA to get the hang of it. Thanks for the great video and inspiration for learning : )) Thumbs up!!

[pmsocho]

Awesome teaching! Great transformations! :)

[johnborg]

Thanks Mike. Formula version is preferred from my end, but this is great and thanks. :) :)

[johnmatta]

Hello magical Mike, greetings from a very old ExcelIsFun subscriber.

I recently had s challenge with a dataset where there were some invisible unicode charachters that I was trying to get rid of using power query. The challenge are:

1. The unicodes were all over the whole table
2. I can't really know ecactly what unicodes are there because zi can't see them in the data..I used Excel on a sample of the data and could identify some codes using UNICODE() formula and found couple 8236 & 8237 but I can't confirm that this is all in there.

Would really appreciate if you can make a video on that.
Cheers
John

[amirhossein]

how can enable IntelliSense for m code in excel?

[NoShadowOfDoubt]

Mike at step # 5 after source in the MaterialSupplierReport query the file looks for folder start, I have updated the source to see it in my desktop, but step number 5 "Expanded Table Column1" still looks for your desktop folder.... how to update that? thanks

[iuh]

4:00 My Excel 2016 and Power Query doesn't show color and doesn't give hints to the functions in formula. Are there hints and colors in Excel 2016? or just in Excel 365?