10 Power Query tips EVERY user should know! | Excel Off The Grid

★ Want to automate Excel? Check out our training academy ★

★ About this video ★
In this video, we look at 10 Power Query tips that every user should know.

How many did you know? Let me know in the comments.

0:00 Introduction
0:21 Tip 1 Default load settings
1:09 Tip 2 Close & Load greyed out
1:50 Tip 3 Rounding errors (?)
2:39 Tip 4 Edit auto generated column names
3:06 Tip 5 Disable auto Changed Type steps
3:54 Tip 6 Adding documentation
4:43 Tip 7 Phantom errors
5:08 Tip 8 Connect to folders efficiently
5:49 Tip 9 Avoid the double refresh
6:25 Tip 10 Reusing queries
6:57 How did you do?

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[atbird]

OMG! Disabling the auto Change Data Types!! Love that tip!

[ennykraft]

I knew most of them but wasn't aware that Excel and Power Query use different rounding methods. Thank you so much for bringing that to my notice. Then I had lots of fun writing a formula that uses banker's rounding in Excel.

That's one of the reasons why I like your videos so much. After pretty much every single one, I end up thinking about the problem and coming up with my own solutions. Inspiring creativity is the best kind of teaching!

[patrickschardt]

I will making the Folder.Contents change tomorrow. I didn’t know about that. I knew of most of the other ones but it’s always good to refresh the mind

[richardmas]

Thanks for the tips ! The "default load" one was exactly what I was looking for !

[karolinab]

Unchecking enable background refresh - didn't know that. Thank you!😊

[raimundojs]

Excellent! The Jack Bauer of Excel strikes again. Thank you for sharing! The rounding info is a life saver.

[westleyempeigne]

Those are great tips Mark! Knew only 3 of them!

[CastielTheAngel]

it's the enumeration of some self discovered tips thanks

[imatzav]

9/10. The part about rounding numbers was new to me.

[Adam_K_W]

These are all great! I think I was aware of roughly 7 of those and have used a few of them. My personal favorite PQ shortcut is once I've worked out a query, especially a complex query, I will go to the advanced editor, copy the all of the query language there and drop it into Microsoft Co-Pilot and ask it to Q.C. check my query and optimize it. If there are multiple steps where I've added a number of columns one at a time or something like that, AI will combine those steps for me into a single step and give me a new concise query that I can paste back into the advanced editor. **Make sure to save the original language to a word document or simply duplicate the query you're going ask AI to rework before you do this. That way, if AI gives you a buggy bit of M language, you can easily revert to the one you know works well.

[StopWhining]

Close and load to greyed out: THANK YOU!. This was making me crazy.

[jimfitch]

Excellent tips, Mark! I’m an advanced PQ user & learned useful techniques today. Thank you!

[JenMayB]

Oh my gosh, I already knew all of those because I’ve been following you for over a year! I think I’m most proud of myself and my PQ journey thanks to you and your mutuals on YT 🙏🙌🤩.

[boissierepascal]

Very precious, thanks Mark. 5/10 already knew. The bests : avoid double refresh and rounding numbers

[alterchannel]

I knew 9 out of 10. Believe it or not i didn't know about the first one and I love it so much. Thanks

[BackPly]

9/10, thanks for this, quick and straight forward.

[IvanCortinas_ES]

Efficient and to the point. As always, discovering new features thanks to Mark.

[garyknott]

Great tips, thanks Mark. I especially liked the import from main folder without sub-folders. This means I can save old data file back-ups there and they will be ignored, great.

[EricaDyson]

You're right. I should and I didn't but I do now! Thanks a bundle.

[KaanSiyasal]

I really liked your tips and tricks regarding power query. I haven't searched for your other videos yet, but if you don't have one yet, I'd like to see your videos regarding parameters