Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders File Reference for this video: 0212 Power Query M code tricks.zip
@larmondoflairallen47052 ай бұрын
For the "Group By" text concatenation, I find it super helpful to also use List.Sort so that any combination of the same items appears the same way, rather than in whatever random order they have in the data (e.g. "Alpha, Bravo, Charlie" always appears as such, rather than "Bravo, Alpha, Charlie" or "Charlie, Alpha, Bravo"). The default sort order is ascending, so you don't need to specify that, but you can sort it using the Order.Descending option if you want.
@ExcelOffTheGrid2 ай бұрын
That's a good tip. Thank you for sharing. 👍
@BIGorilla2 ай бұрын
Hey Mark. Such a great video once again. My favorite tip here is the Grouping technique that allows you to ignore capitalization. Make life much easier. I got a fun article coming up that also shows you how you can summarize one column and respect case, while ignoring the case for another. In other words, custom comparers. I think you might enjoy that too!
@kndeepak2 ай бұрын
When when when???!!!
@ExcelOffTheGrid2 ай бұрын
I will certainly take a look - I'm sure you'll pull out some killer info that the rest of the universe had no idea about.
@decentmendreams2 ай бұрын
Thanks Mark! The beauty of PQ is that there is 99 ways to do the same thing . Your presentation clean. Your solutions clean. All fabulous. I am going to pick #8 .
@ExcelOffTheGrid2 ай бұрын
Thanks. Yes, there are 99 ways - and at least 95 of those are a bad idea. I know, I've done many of them before 🤣
@OzduSoleilDATA2 ай бұрын
I love the line-feed feature. And combining text in a single cell is pretty slick! 😎
@ExcelOffTheGrid2 ай бұрын
Thanks Oz - I’m glad you enjoyed it 👍
@EricHartwigExcelConsulting2 ай бұрын
I agree the line feed code is really awesome! Thank you Mark! I was using Character.FromNumber(10) but I like "#(lf)" because it's less typing and still works with concatenating / & things together.
@benlinford_work16 күн бұрын
The kinds of problems you are solving here with built in hard coding is along the lines of some of the things that have disincentivised me from taking the time to learn and adopt PowerQuery in my work, but you're helping to drag me along in really getting to grips to learn how we can work around these things here. Thanks Mark. Hope you're keeping well. Quality content continuing to drip. 😄
@ExcelOffTheGrid12 күн бұрын
Somebody estimated that 40% of scenarios can be solved with the Power Query UI. So that means that 60% requires some additional knowledge. It's worth investing the time.
@giorgioberardi39402 ай бұрын
Tip 9 is by far the best in terms of widespread applicability. No worst tip in my view. Well done!
@ExcelOffTheGrid2 ай бұрын
Yes, lots of uses for #9 - I hope you can put it to good use.
@tracywilliams612 ай бұрын
All of ticks I've experienced at least once but by far #8 is my favorite. You are a great teacher you explain the steps in a way that brings clarity to how pq makes sense THANK YOU My least favorite is NONE
@ExcelOffTheGrid2 ай бұрын
Thank you - clarity is always key.
@New2you09Ай бұрын
Great video. Nice to see someone doing videos targeted at the more advanced users. 😊
@ExcelOffTheGridАй бұрын
Glad it was helpful! 😁
@tibibara7 күн бұрын
Brilliant tutorial Mark, thanks a lot!
@ExcelOffTheGrid6 күн бұрын
Glad it was helpful! 😁
@GrainneDuggan_Excel2 ай бұрын
Mark, your explanation for multi-step custom colums using let is so helpful.
@ExcelOffTheGrid2 ай бұрын
Thank you, I'm glad it was helpful. 😁
@timtunbridgeАй бұрын
Number 9 for me. I have a particular long and complex additional column and this allows me to build and test the steps incrementally. Thank you.
@ExcelOffTheGridАй бұрын
Yes, that will definitely help in that scenario. 👍
@IvanCortinas_ES2 ай бұрын
Wowww. Great tutorial Mark. My favourite tips: Combine text in a cell with group by and Group by and ignore case. Thank you for sharing all tips.
@ExcelOffTheGrid2 ай бұрын
👍 That’s a pretty useful tip that I’ve used in real world projects.
@ExcelWithChris2 ай бұрын
Great stuff. Best one for me is adding a column using a cell ref.
@ExcelOffTheGrid2 ай бұрын
I love that technique. I used to have a really long way to do it and then one day 💡 - I've never done it the old way ever again.
@Oprimaita2 ай бұрын
Great collection of good tips. My preference goes to 8 and 9 !
@ExcelOffTheGrid2 ай бұрын
8 & 9 - I really kept you waiting until the end until the good stuff.
@Oprimaita2 ай бұрын
@@ExcelOffTheGrid There were some good things right from the start 😉
@Bhaskar_Joshi_there2 ай бұрын
1:03 => Even you can see the line break within data preview. To do that go to View Tab > Data Preview > Show whitespace
@ExcelOffTheGrid2 ай бұрын
I had forgotten about that setting - that's a good tip.
@gabrielgordon2 ай бұрын
no worst tip! All great! Thanks
@ExcelOffTheGrid2 ай бұрын
Thanks 😁
@westleyempeigne6541Ай бұрын
Hi Mark, great tips! My fav are trick 3,4,5 and 8! Leaning new things all the time but also need to try and incorporate these into my work!
@raimundojs95472 ай бұрын
Mark is the Jack Bauer of Excel (doesn't he look like Kiefer Sutherland?), a super agent! Thank you for sharing! Very useful tricks with many concepts to be explored. Great!
@ExcelOffTheGrid2 ай бұрын
The Jack Bauer of Excel... I'll take that 😁
@NestorCirhuza2 ай бұрын
5 AND 6 are my favorite tips. Thank you
@ExcelOffTheGrid2 ай бұрын
Great stuff. I think #5 should really be the default behaviour - it would save so many issues.
@GeertDelmulle2 ай бұрын
Here’s a tip for you: (a rule of thumb of mine): if you need List.Zip, your query is not as simple as it can be. In general List.Zip can be avoided if you use the right combination of functions (e.g. Table.ToRows). BTW, FYI: List.Zip is the list equivalent of Table.Transpose. Do you agree?
@ExcelOffTheGrid2 ай бұрын
A lot of List transformations are not very efficient, but unless it has a significant impact on refresh time, I don't tend to worry too much. Sometimes lists aren't from the same Table, so List.Zip isn't always the same as Table.Transpose. But in this circumstances, I agree. Yes, Table.ToRows could be a suitable alternative in this scenario.
@chrisgilbert99212 ай бұрын
Another GREAT video full of tips that will make my life easier. Thanks, Mark!
@ExcelOffTheGrid2 ай бұрын
Happy to help!
@ExcelerateYourBiz-or2weАй бұрын
Now that you show it, it looks so simple, but my favourite tip is grabbing data from a cell such as a date (tip #8). I've managed this different ways but this is MUCH better and much cleaner! Almost feel like an idiot not having figured that one out before. LOL🙈 Thanks for all the great tips and I love that this is a little more advanced for those of us who need that.
@ExcelOffTheGridАй бұрын
Yeah - that is a really useful technique. 👍
@leonidkoyfman8142 ай бұрын
Great collection of practical and helpful tips. Thank you.
@ExcelOffTheGrid2 ай бұрын
Glad it was helpful!
@user-dn5gd1rn9f2 ай бұрын
Excellent tips, as always! How could we not like your ideas?
@ExcelOffTheGrid2 ай бұрын
Thanks - sometimes I can go a bit crazy and i get comments like "nobody will ever do this!" So, I'm glad this one hit the mark 😁
@tomhaase1386Ай бұрын
Very nice tricks, I used trick #9 it helped me a lot. Trick #1 with the line feed is great. But I have a problem, I have a list with those line feeds address. How do I get it back into its normal each column separated? Thank you for your very much for your tutorials.
@ExcelOffTheGridАй бұрын
In the Split Column dialog, I’m sure there are options in the advanced section to achieve that.
@michaeljones28432 ай бұрын
The first tip is awesome and is one I can use the most, Thanks!
@ExcelOffTheGrid2 ай бұрын
Awesome that is great news!
@Ganja19742 ай бұрын
Incredible tips! thanks for making my life easier using few of them right now!
@ExcelOffTheGrid2 ай бұрын
Using some of them straight away... Awesome!
@japa62252 ай бұрын
Thanx's for blowing my mind. Going to revisit tomorrow. 🍻🍻
@ExcelOffTheGrid2 ай бұрын
Good stuff - I hope you can pick up a few techniques.
@bretogden3838Ай бұрын
Tip 6 is awesome!
@SSi-nq3rtАй бұрын
Defenately Nr. 5 for me. I often have to add new colums after a few months. To go back every time and add those Colums manually... was just a mess. Thank you (also tip 1 is nice)
@ExcelOffTheGridАй бұрын
Thanks for sharing! I’m glad you’ve found them useful.
@pierre-yves_david2 ай бұрын
My preferred: tip #9, let / in used in a custom column formula (readability). Tip #3 is cool also (to make it simpler). I suggest for future ideas how to type columns automatically, e.g., using Type.Union, without hard coded column names of course (dynamic typing of dynamic columns). Done what follows but probably better solutions exist: let Source = (ParamTable) => let ToUntypeTable=List.Transform(Table.ColumnNames(ParamTable), each { _ , type any}), UnTypedTable= Table.TransformColumnTypes(ParamTable, ToUntypeTable), ToRetypeTable= List.Transform(Table.ColumnNames(ParamTable), each { _ , Type.Union(List.Transform(Table.Column(UnTypedTable, _ ), Value.Type))}), RetypedTable= Table.TransformColumnTypes(ParamTable, ToRetypeTable) in RetypedTable in Source My concern is that a column of integers is typed as decimal numbers (suboptimal). And difficult to type the parameter expect by the function (created from a parametric request) as a table: a parameter, which is table associated to a sample request, is difficult to create, can't find the way easily.
@ExcelOffTheGrid2 ай бұрын
Thank you. I have a custom function for auto-typing columns. But it's not as good clean as yours. Looks like I got a bit of study to do.
@pt347023 күн бұрын
Great useful video! Favourite was steps 8 and 9. Do you teach formulas for PQ in your course?
@ExcelOffTheGrid12 күн бұрын
Thank you. We don't currently have a M code course in our program. But it should be coming later in the year.
@ja3mi22 ай бұрын
Tip #6 - very handy.
@ExcelOffTheGrid2 ай бұрын
Great - I hope you can put it to good use.
@90hsilvaАй бұрын
Rename and expand columns 👍
@Darshanam-Jatra2 ай бұрын
Simply brilliant ❤
@ExcelOffTheGrid2 ай бұрын
Thank you 😁
@tlee70282 ай бұрын
Another AWESOME tutorial !
@ExcelOffTheGrid2 ай бұрын
Thank you. That's very kind of you to say.
@KennedyMateko2 ай бұрын
Fabulous! Thanks Mark
@ExcelOffTheGrid2 ай бұрын
Thanks, I'm glad they were useful.
@HimanshuSingh-lk2my2 ай бұрын
Nice 👍
@ExcelOffTheGrid2 ай бұрын
Thank you! Cheers! 😁
@gonzuic2 ай бұрын
Thank you Mark, that was a great video, in your Power Query course, do you have that content it and more examples about advanced M code? Thanks
@ExcelOffTheGrid2 ай бұрын
At present our Power Query course does not go into much M. When we re-record we may have a separate M course, but that is a while off yet.
@patrickharilantoraherinjat29942 ай бұрын
Just amazing Mark. Thank you
@ExcelOffTheGrid2 ай бұрын
Glad you enjoyed it
@chrism90372 ай бұрын
Awesome Mark! Great video
@ExcelOffTheGrid2 ай бұрын
Thanks! I'm glad you enjoyed it.
@user-yz6ie2qv6p2 ай бұрын
Hi. Please could you do a similar video on loading/cleaning up PDF files to present in a long list...
@ExcelOffTheGrid2 ай бұрын
PDF's are a pain! I know your struggles.
@zzota2 ай бұрын
Hi Mark, all very useful, thank you.
@ExcelOffTheGrid2 ай бұрын
My pleasure! Glad it was useful.
@marcus_barao2 ай бұрын
Thanks you for your video! Very powerfull tricks.
@ExcelOffTheGrid2 ай бұрын
You're welcome, I hope you can put them to good use.
@OZbMG8jsJTX14AWYne4omBw2 ай бұрын
Молодец!
@ouvangouvang50552 ай бұрын
Great vidéo.
@ExcelOffTheGrid2 ай бұрын
Thank you.
@hazemali3822 ай бұрын
Wow More than great
@ExcelOffTheGrid2 ай бұрын
Thanks, I’m glad you enjoyed it. 😁
@TheLaopi2 ай бұрын
Great, i learned a lot, thks
@ExcelOffTheGrid2 ай бұрын
Thank you, I'm glad it was helpful!
@deepakn97962 ай бұрын
Hello there, i have been using the power query for quite sometime , can you please tell how to extract if the sharepoint list have more than 5000lines in it? it throws error
@SairamBalasubramaniam2 ай бұрын
Mark your tips challenge with Chandeep was not as cutting edge as this one. Save the best for last. Kudos
@ExcelOffTheGrid2 ай бұрын
Thank you. Without the Chandeep challenge, I wouldn't have thought about creating this video. So it's all thanks to Chandeep really. 😁
@beautifulthoughts17752 ай бұрын
How to make Real Estate CRM with help of Google Sheet.. please making video.🤝
@marcusmayer10552 ай бұрын
👍👍👍
@arpankumar44872 ай бұрын
why powerquery does not support .xlsb file?????
@mohitchaturvedi89312 ай бұрын
your camera focuse late and text appears blurred for sometime. and your screen is so large that it gives very small font on mobile screen.
@ExcelOffTheGrid2 ай бұрын
Most of my viewers are watching on a desktop, so I optimize for that... sorry.
@DinoAMAntunes2 ай бұрын
Hi Please i´m not finding the excel file to follow along? Tks in advance
@ExcelOffTheGrid2 ай бұрын
I've pinned a comment at the top, with the URL and instructions.
@vishalborase5506Ай бұрын
seriously where a data analyst draw a line while all the youtubers are coming up with these millions of tips and making us feel like we are still so far behind :)
@ExcelOffTheGridАй бұрын
Each time the technology moves we are all catching up. You either learn in an ad hoc path by watching KZbin videos, or a structured path using a course. One is free, confusing and slow, the other is paid, clear and fast. Either way, we are all still learning.
@alexrosen87622 ай бұрын
Great and useful tips & tricks which are really helpful in real life scenarios