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

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

Great collection of practical and helpful tips. Thank you.

Simply brilliant ❤

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.

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

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

Hi Mark, all very useful, thank you.

The MissingField.Ignore was good! I didn't know there was a way of handling errors like that!! Thanks!!

Just amazing Mark. Thank you

👍👍👍

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

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

Wow More than great

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

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

Nice 👍

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

no worst tip! All great! Thanks

Tip4 is a very cool and innovative way to mingle the poorly structured tables (I was not aware of the "MissingField.Ignore"), but it is an overall excellent list (also I already knew and practiced various).

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

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.

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.

Great video. Nice to see someone doing videos targeted at the more advanced users. 😊

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.

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)

Rename and expand columns 👍

Tip 6 is awesome!

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!

Great useful video! Favourite was steps 8 and 9.

Do you teach formulas for PQ in your course?

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

Brilliant tutorial Mark, thanks a lot!

Is it possible to run any background Power Query to bring up data from particular range: B53:O153 + Cell "B1" + Cell "B15" from hundreds of files lying in the same folder?

Nice Tutorial, but it seems I'm still a beginner and need to check out and learn the basics of Power Query from you earlier videos, Thanks

Hello Mark. I find step 3 in a nested Table very useful. I regularly import 'files from folder'. Thus nested table transformations are very useful but also very advanced. Because steps you need are not on the ribbon.

While I understand the purpose of this video to teach PQ functionality, I believe a better approach is to teach users to first bring their data is a normalized or un-pivoted state, rather than transforming it in this pivoted state. That makes them (the user) and the data better prepared for further transformation. Other than that - very instructive 👍

I am still looking for how to add a total column that includes new data as new monthly data is added jan, feb, mar... easy to do but when i add Apr sales data - how does it automatically include Apr

El tip 6, 8, 10 fueron brutales, no los conocía, me suscribo 🎉

decent tips!

Very informative videos. Kindly also let us know how to merge split rows without transposing for larger datasets.

Nr 8 ! Thanks 👌

Watched the video, each tip felt very useful. The one I have usecase in my work is "Rename Column by position". Thank You so much for all the tips.

BRAVO! 4 the last 2 tips!

tip 1 is going to solve an issue for work that alot of people will thank me for. thank you.

What power query should have is an option where it scans the steps and see if you did something that can remove an unneeded step like in the last example.
This however should only be done on user command to avoid steps being removed/combined if you don't want that.

In any case very usefull tips, was looking for the linefeed one myself.

Amazin

Hi Mark, thank you, really great tips! If you want to group ignoring case, why not to first convert (upper/lower/capitalize) the column to group by? Doing as you suggested, is there the chance to obtain some text in upper case and some other in lower case, depending on the 1st occurrence found scanning the column?

Videos like this make me realize just how much I still have to learn. I can't wait to start utilizing several of these tips.