10 awesome Power Query tricks you NEED to know! | Excel Off The Grid

[@ExcelOffTheGrid]

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

[@larmondoflairallen4705]

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.

[@ExcelOffTheGrid]

That's a good tip. Thank you for sharing. 👍

[@BIGorilla]

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!

[@kndeepak]

When when when???!!!

[@ExcelOffTheGrid]

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.

[@decentmendreams]

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 .

[@ExcelOffTheGrid]

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 🤣

[@OzduSoleilDATA]

I love the line-feed feature. And combining text in a single cell is pretty slick! 😎

[@ExcelOffTheGrid]

Thanks Oz - I’m glad you enjoyed it 👍

[@EricHartwigExcelConsulting]

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_work]

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. 😄

[@ExcelOffTheGrid]

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.

[@giorgioberardi3940]

Tip 9 is by far the best in terms of widespread applicability. No worst tip in my view. Well done!

[@ExcelOffTheGrid]

Yes, lots of uses for #9 - I hope you can put it to good use.

[@tracywilliams61]

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

[@ExcelOffTheGrid]

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! 😁

[@tibibara]

Brilliant tutorial Mark, thanks a lot!

[@ExcelOffTheGrid]

Glad it was helpful! 😁

[@GrainneDuggan_Excel]

Mark, your explanation for multi-step custom colums using let is so helpful.

[@ExcelOffTheGrid]

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_ES]

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.

[@ExcelOffTheGrid]

👍 That’s a pretty useful tip that I’ve used in real world projects.

[@ExcelWithChris]

Great stuff. Best one for me is adding a column using a cell ref.

[@ExcelOffTheGrid]

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.

[@Oprimaita]

Great collection of good tips. My preference goes to 8 and 9 !

[@ExcelOffTheGrid]

8 & 9 - I really kept you waiting until the end until the good stuff.

[@Oprimaita]

@@ExcelOffTheGrid There were some good things right from the start 😉

[@Bhaskar_Joshi_there]

1:03 => Even you can see the line break within data preview. To do that go to View Tab > Data Preview > Show whitespace

[@ExcelOffTheGrid]

I had forgotten about that setting - that's a good tip.

[@gabrielgordon]

no worst tip! All great! Thanks

[@ExcelOffTheGrid]

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!

[@raimundojs9547]

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!

[@ExcelOffTheGrid]

The Jack Bauer of Excel... I'll take that 😁

[@NestorCirhuza]

5 AND 6 are my favorite tips. Thank you

[@ExcelOffTheGrid]

Great stuff. I think #5 should really be the default behaviour - it would save so many issues.

[@GeertDelmulle]

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?

[@ExcelOffTheGrid]

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.

[@chrisgilbert9921]

Another GREAT video full of tips that will make my life easier. Thanks, Mark!

[@ExcelOffTheGrid]

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. 👍

[@leonidkoyfman814]

Great collection of practical and helpful tips. Thank you.

[@ExcelOffTheGrid]

Glad it was helpful!

[@user-dn5gd1rn9f]

Excellent tips, as always! How could we not like your ideas?

[@ExcelOffTheGrid]

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.

[@michaeljones2843]

The first tip is awesome and is one I can use the most, Thanks!

[@ExcelOffTheGrid]

Awesome that is great news!

[@Ganja1974]

Incredible tips! thanks for making my life easier using few of them right now!

[@ExcelOffTheGrid]

Using some of them straight away... Awesome!

[@japa6225]

Thanx's for blowing my mind. Going to revisit tomorrow. 🍻🍻

[@ExcelOffTheGrid]

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_david]

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.

[@ExcelOffTheGrid]

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.

[@pt3470]

Great useful video! Favourite was steps 8 and 9. Do you teach formulas for PQ in your course?

[@ExcelOffTheGrid]

Thank you. We don't currently have a M code course in our program. But it should be coming later in the year.

[@ja3mi2]

Tip #6 - very handy.

[@ExcelOffTheGrid]

Great - I hope you can put it to good use.

[@90hsilva]

Rename and expand columns 👍

[@Darshanam-Jatra]

Simply brilliant ❤

[@ExcelOffTheGrid]

Thank you 😁

[@tlee7028]

Another AWESOME tutorial !

[@ExcelOffTheGrid]

Thank you. That's very kind of you to say.

[@KennedyMateko]

Fabulous! Thanks Mark

[@ExcelOffTheGrid]

Thanks, I'm glad they were useful.

[@HimanshuSingh-lk2my]

Nice 👍

[@ExcelOffTheGrid]

Thank you! Cheers! 😁

[@gonzuic]

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

[@ExcelOffTheGrid]

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.

[@patrickharilantoraherinjat2994]

Just amazing Mark. Thank you

[@ExcelOffTheGrid]

Glad you enjoyed it

[@chrism9037]

Awesome Mark! Great video

[@ExcelOffTheGrid]

Thanks! I'm glad you enjoyed it.

[@user-yz6ie2qv6p]

Hi. Please could you do a similar video on loading/cleaning up PDF files to present in a long list...

[@ExcelOffTheGrid]

PDF's are a pain! I know your struggles.

[@zzota]

Hi Mark, all very useful, thank you.

[@ExcelOffTheGrid]

My pleasure! Glad it was useful.

[@marcus_barao]

Thanks you for your video! Very powerfull tricks.

[@ExcelOffTheGrid]

You're welcome, I hope you can put them to good use.

[@OZbMG8jsJTX14AWYne4omBw]

Молодец!

[@ouvangouvang5055]

Great vidéo.

[@ExcelOffTheGrid]

Thank you.

[@hazemali382]

Wow More than great

[@ExcelOffTheGrid]

Thanks, I’m glad you enjoyed it. 😁

[@TheLaopi]

Great, i learned a lot, thks

[@ExcelOffTheGrid]

Thank you, I'm glad it was helpful!

[@deepakn9796]

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

[@SairamBalasubramaniam]

Mark your tips challenge with Chandeep was not as cutting edge as this one. Save the best for last. Kudos

[@ExcelOffTheGrid]

Thank you. Without the Chandeep challenge, I wouldn't have thought about creating this video. So it's all thanks to Chandeep really. 😁

[@beautifulthoughts1775]

How to make Real Estate CRM with help of Google Sheet.. please making video.🤝

[@marcusmayer1055]

👍👍👍

[@arpankumar4487]

why powerquery does not support .xlsb file?????

[@mohitchaturvedi8931]

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.

[@ExcelOffTheGrid]

Most of my viewers are watching on a desktop, so I optimize for that... sorry.

[@DinoAMAntunes]

Hi Please i´m not finding the excel file to follow along? Tks in advance

[@ExcelOffTheGrid]

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.

[@alexrosen8762]

Great and useful tips & tricks which are really helpful in real life scenarios

[@ExcelOffTheGrid]

Thank you. I hope you can put them to good use. 👍