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.
@@ExcelOffTheGrid yes, when adding multiple columns, step by step, AI will merge those steps using list.transform it's pretty slick when it works out. AI isn't infallible though so you have to know what you're looking at and QC check it.
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!
Thank you for those kind comments about the videos. I'm always trying to create good content and constantly improve. This is the formula I use for Bankers Rounding: =IF(MOD(C3,1)=0.5,MROUND(C3,2),ROUND(C3,0)) Where C3 is the value to round.
Thank you Mark! I used pretty much all of them, but you gave me tips on using them better with sub tips I didn't know. For example I would rename steps but didn't know you could add a long description in Properties. But I rename steps getting rid of spaces: this removes the (annoying ?) double quotes and hash (i.e. "# ") which I find easier to read and tweak my Power Query script in Advanced Editor, often with Excel formula cheats or in SSMS Studio.
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.
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 🙏🙌🤩.
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
Thanks Mark, great tips. I think I made it to 8 out of 10, but I didn't know #9 and I didn't know about bankers' rounding, and a great reminder of the others. Just one (very geeky) thing I once came across - like you, I like to disable automatic Change Types but I was once downloading PDF bank statements from a folder. It worked perfectly with automatic change types enabled but was a disaster without - no idea why, and a very specific and unusual case
5:48 Question: What if you had connected to a folder or files within a folder, but someone at your organization changes the location of the folders, which breaks your query? Are there any measures/controls we can put in place to prevent/mitigate the query breaking? Thanks!
Just re-point the query to the new folder - and everything will work as before. Instructions here: exceloffthegrid.com/power-query-source-data-location/
4/10 thanks for the new knowledge! Still struggeling with sorting columns efficiently AND correctly (still cannot find any good solution on that) at pirvot tables... Any how well done!
There are some work around using self referencing Tables. But the complexity to manage it correctly usually causes more issues than just loading all the files.
Power query is another layer of data managing and is really a powerfull tool that is not well known and explored. The use of a proper language is an advantage and disadvantage at the same time.
Thanks for sharing, it's very useful . But I want to ask how to change the name of source file name or directory without editing the content in Power Query hope you can understanding my poor English
Regarding Tip #9, Excel needs a way to set "disable background refresh" as the default for future queries. I absolutely despise this setting and the need to clear it each and every time. I just finished making a file with many separate queries and it just becomes an annoying extra step for each and every one of them. But otherwise, some really great tips. 👍
@@ExcelOffTheGrid Thanks, I found a macro to do that, and even put a link to it on the Ribbon for quick access. Step 1: make the file and queries. Step 2: run the macro. 👍
Hi there. A non Excel related question. Could you please share your lighting techniques when recording your videos? Would be great to see your setup. Regards. Chris (South Africa)
I've literally just changed it today. So, you will see it on a video in a few weeks. I use a soft-light at about 45 degrees for my face. The background is just cheap LED lights shining at a wall. The rest is playing around with the Brightness / Contract / Saturation to get more shadow into the image.
Great news 😁 You can sign up to our Office Scripts course here: courses.exceloffthegrid.com/office-scripts-course It's also part of our Excel Academy membership, which is available here: courses.exceloffthegrid.com/academy
Just ran into an issue. I have years at top of matrix, and months as rows. If i use the Difference formula in dax I can get diff from one month to the other (going down the rows), but how (if matrix stays in same layout) can I get Difference from year to year across my matrix?
9/10 - didn’t know tip no. 9 (then again I always load the data straight to the Pivot Table Cache and don’t have the issue that way). All the others have been active (or should I say: “deactive”? ;-) for the longest time by now. Good tips of course, should be part of PQ-M 101, but I’m biased :-)
@@ExcelOffTheGrid It gets even better: if you copy the pivot table (or the entire sheet for that matter) to another one/location, explicitly updating one single pivot table updates all the others as well. On looking at the data: you can always make a PT that looks like a table… (provided that PT is not too big for your sheet). Of course, in this scenario it may be beneficial to load the data to the data model and get your PT from there, or explicitly avoid that route if you want to make use of grouping in your PT. BTW: you did know that the PT cache can hold very large tables, right? I mean: (way) larger than f.i. 1,5 mio records. PT’s work well with them too. I looked into all this some years ago…
Mark, if I may, I would suggest a video on data firewall. I don’t understand neither conceptually nor practically what’s wrong, even if I understand what is aimed (avoiding sensitive data output in query folding). If I remember correctly, you have already addressed the topic, the two different error messages… It is the only topic on which I need something complementary to get it. My workaround is horrific: parameter to shunt the firewall 😱 Such a shame, should have said Talk Talk.
why is #3 even necessary, rounding like a banker is not 'normal'. #8, this is so much nicer than my workaround, selecting just that 'root' in the Path Column. 6/7 out of 10 that I already use in some shape or form.
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.
Interesting - hopefully you learn from changes which the AI has made and try to build the query better next time.
@@ExcelOffTheGrid yes, when adding multiple columns, step by step, AI will merge those steps using list.transform it's pretty slick when it works out. AI isn't infallible though so you have to know what you're looking at and QC check it.
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!
Thank you for those kind comments about the videos. I'm always trying to create good content and constantly improve.
This is the formula I use for Bankers Rounding: =IF(MOD(C3,1)=0.5,MROUND(C3,2),ROUND(C3,0))
Where C3 is the value to round.
OMG! Disabling the auto Change Data Types!! Love that tip!
Yes, that one is pretty useful. 👍
Yes, I thought the same thing. I hate t when Power Query does that automatically.
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
Thank you Mark! I used pretty much all of them, but you gave me tips on using them better with sub tips I didn't know. For example I would rename steps but didn't know you could add a long description in Properties. But I rename steps getting rid of spaces: this removes the (annoying ?) double quotes and hash (i.e. "# ") which I find easier to read and tweak my Power Query script in Advanced Editor, often with Excel formula cheats or in SSMS Studio.
Unchecking enable background refresh - didn't know that. Thank you!😊
That's one is really useful - I'm glad you won't have the double refresh issue ever again.
Excellent! The Jack Bauer of Excel strikes again. Thank you for sharing! The rounding info is a life saver.
Ha ha ha... 😁 Thanks
Thanks for the tips ! The "default load" one was exactly what I was looking for !
That was the first tip - amazing!
Wow these tips are super useful. I've been a victim to the double refresh all the time! Thanks so much for this video, new subscriber here.
9/10. The part about rounding numbers was new to me.
Me, too!
The last three tips are new to me.
Excellent tips, Mark! I’m an advanced PQ user & learned useful techniques today. Thank you!
Glad it was helpful! - There is always something new to learn.
I don't think I knew any of these! Very nice video- concise and to the point- subscribed!! Thanks!
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
That's a really useful technique - I'm glad I could fill in the 1 gap. 😁
Wow, that’s really good that you knew 9 of 10… I had only known 4 of 10.
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.
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 🙏🙌🤩.
That is awesome! Glad I could help you out for the last 12 months. 😁
Very precious, thanks Mark. 5/10 already knew. The bests : avoid double refresh and rounding numbers
Double refresh is a really useful one - I hope you can put it to good use.
I probably knew a bout half of these. But great video. I love your information.
Well… you now know the other half too. I hope you can put them to good use.
it's the enumeration of some self discovered tips thanks
Great video!
Close and load to greyed out: THANK YOU!. This was making me crazy.
I know, right… why can’t it just show us the options again and then we can choose a new location.
I knew this one… Once you create the query and load it initially, you have to load it through the Queries & Connections (Queries) menu in Excel!
Those are great tips Mark! Knew only 3 of them!
Wonderful and awesome techniques, for me Adding documentation was amazing. Thanks Sir for sharing such a great video.
Great, I hope you can put it to good use.
Awesome tips. Especially changing the settings
9/10 , thanks for this, quick and straight forward.
9/10 - Great stuff. 😁
Efficient and to the point. As always, discovering new features thanks to Mark.
Thank you, I'm glad you liked it. 😁
No 10, I like the most! thanks Mark
Great video as always, thanks Mark!
Thank you, I appreciate that 😁
Great list of tips Mark! 🎉
Thanks Grainne 😁
Informative 🙏🏻
Thanks 🙂
Great tips- thank you!!!
Glad it was helpful!
Wonderful!
9.5... I use #8 on SharePoint vs folders
👏
On SharePoint it is even more critical - it can have a MASSIVE impact on refresh times.
@@ExcelOffTheGrid your academy for the win (plus Celia's snap reports class☺️)
does it work when you're not a site admin?
You're right. I should and I didn't but I do now! Thanks a bundle.
Exactly. You've accelerated to 10/10 in 7 minutes. 😁
These are fantastic
Thank you 😁
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
Thanks, I appreciate that feedback 😁
I've got an older video about parameters - here is the address ruclips.net/video/28T0XzGNZyM/видео.html
10x pure Gold! Thanks 😄
Thank you. 😁
Brilliant video, super useful, thanks!!
Glad it was helpful! 😁
Great tips!
Thanks Mark, great tips. I think I made it to 8 out of 10, but I didn't know #9 and I didn't know about bankers' rounding, and a great reminder of the others.
Just one (very geeky) thing I once came across - like you, I like to disable automatic Change Types but I was once downloading PDF bank statements from a folder. It worked perfectly with automatic change types enabled but was a disaster without - no idea why, and a very specific and unusual case
That is very odd... it really should make no difference what so ever. Hopefully I will never come across that. Sounds like a real headache 😬
Thanks, Mark. 6/10 for me!
6/10 - Good work!
Thanks!
5:48 Question: What if you had connected to a folder or files within a folder, but someone at your organization changes the location of the folders, which breaks your query? Are there any measures/controls we can put in place to prevent/mitigate the query breaking?
Thanks!
Just re-point the query to the new folder - and everything will work as before.
Instructions here: exceloffthegrid.com/power-query-source-data-location/
Very Nice, thanks!
4/10 thanks for the new knowledge!
Still struggeling with sorting columns efficiently AND correctly (still cannot find any good solution on that) at pirvot tables... Any how well done!
Sorting columns in tricky due to how PQ evaluates the process - often Table.Buffer is the solution.
Excellent!
Can you gives another tips for me 🙏
my source data is in the folder, Is there a trick to refresh query only for the file I just updated ?
There are some work around using self referencing Tables. But the complexity to manage it correctly usually causes more issues than just loading all the files.
Except of rounding option rest of tips I've learned on my practice. :)
But anyway, thank you for reminding them.🤗
These are the tips you "should know", and you do, so that's a good thing 😁
Amazing 🎉
Thanks 😄
As usual “to the mark!” 😃
Thanks again! 😁
Great tips. Thanks
Thanks Chris. 😁
Thanks for sharing
My pleasure 👍
Thanks
You're welcome. 😁
Power query is another layer of data managing and is really a powerfull tool that is not well known and explored. The use of a proper language is an advantage and disadvantage at the same time.
Getting to know M code is tricky - thankfully we can do a lot just through the user interface.
Thanks for sharing, it's very useful .
But I want to ask how to change the name of source file name or directory without editing the content in Power Query
hope you can understanding my poor English
Check these out:
exceloffthegrid.com/power-query-source-data-location/
exceloffthegrid.com/power-query-source-cell-value/
Regarding Tip #9, Excel needs a way to set "disable background refresh" as the default for future queries. I absolutely despise this setting and the need to clear it each and every time. I just finished making a file with many separate queries and it just becomes an annoying extra step for each and every one of them. But otherwise, some really great tips. 👍
You could create a macro which changes all the queries in the workbooks.
@@ExcelOffTheGrid Thanks, I found a macro to do that, and even put a link to it on the Ribbon for quick access. Step 1: make the file and queries. Step 2: run the macro. 👍
Hi there. A non Excel related question. Could you please share your lighting techniques when recording your videos? Would be great to see your setup. Regards. Chris (South Africa)
I've literally just changed it today. So, you will see it on a video in a few weeks.
I use a soft-light at about 45 degrees for my face. The background is just cheap LED lights shining at a wall.
The rest is playing around with the Brightness / Contract / Saturation to get more shadow into the image.
7/10 - not bad 😅
7/10 - That's a solid pass - good work.
Sir, I would love to take your Office Scripts course. Can you provide the link to sign up and pay for the course?
Great news 😁
You can sign up to our Office Scripts course here: courses.exceloffthegrid.com/office-scripts-course
It's also part of our Excel Academy membership, which is available here: courses.exceloffthegrid.com/academy
Just ran into an issue. I have years at top of matrix, and months as rows. If i use the Difference formula in dax I can get diff from one month to the other (going down the rows), but how (if matrix stays in same layout) can I get Difference from year to year across my matrix?
Are you using the visual calculation method? If so, my answer is no idea; I've not had chance to look at that yet.
9/10 - didn’t know tip no. 9 (then again I always load the data straight to the Pivot Table Cache and don’t have the issue that way).
All the others have been active (or should I say: “deactive”? ;-) for the longest time by now.
Good tips of course, should be part of PQ-M 101, but I’m biased :-)
Load straight to the Pivot Cache! Then you can can't see the data separately... you Maverick! 😂
@@ExcelOffTheGrid It gets even better: if you copy the pivot table (or the entire sheet for that matter) to another one/location, explicitly updating one single pivot table updates all the others as well. On looking at the data: you can always make a PT that looks like a table… (provided that PT is not too big for your sheet).
Of course, in this scenario it may be beneficial to load the data to the data model and get your PT from there, or explicitly avoid that route if you want to make use of grouping in your PT. BTW: you did know that the PT cache can hold very large tables, right? I mean: (way) larger than f.i. 1,5 mio records. PT’s work well with them too. I looked into all this some years ago…
Not to show off, but 10/10.
That's awesome - good work. 🏆
Mark, if I may, I would suggest a video on data firewall. I don’t understand neither conceptually nor practically what’s wrong, even if I understand what is aimed (avoiding sensitive data output in query folding).
If I remember correctly, you have already addressed the topic, the two different error messages… It is the only topic on which I need something complementary to get it. My workaround is horrific: parameter to shunt the firewall 😱
Such a shame, should have said Talk Talk.
6 out 10
Pretty good... and now having watched the video you're now 10/10. 😁
1 from 10
But now having watched the video, it's 10/10 😁
around 6-7
How to map dynamic and static data in excel. I want my comments to remain with corresponding lines When i refresh data. Thnx
That's covered in this video: ruclips.net/video/8cmuEpF3oOg/видео.html
@@ExcelOffTheGrid wow! Thanks 👍
5/10
why is #3 even necessary, rounding like a banker is not 'normal'.
#8, this is so much nicer than my workaround, selecting just that 'root' in the Path Column.
6/7 out of 10 that I already use in some shape or form.
Bankers rounding is actually the default in VBA, it’s quite common in programming languages - which I agree is not ‘normal’.
@@ExcelOffTheGrid I suddenly fear for all the VBA that I've done in my lifetime. Well, now I know. Just have to find a way to remember it 😁
0/10
I had no idea about these tips.
That's OK. You've now do, so you're now at 10/10. 😁
@@ExcelOffTheGrid yeah! indeed... thanks a lot bro 👏👏
3/10 🙈
But now you know 10/10 - so you've instantly jumped to the next level. 😁
Awesome as always. Thanks Mark❤
Thanks Kebin. 😁