10 awesome Power Query tricks you NEED to know! | Excel Off The Grid
Вставка
- Опубліковано 29 чер 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
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
★ About this video ★
In this video, we look at simple, yet powerful 10 Power Query M code tricks which you can apply today!
0:00 Introduction
0:15 Tip 1: Using line feed
1:41 Tip 2: Rename column by position
2:57 Tip 3: Rename column by position in a nested table
5:09 Tip 4: Rename column based on a list in a nested table
7:00 Tip 5: Expand columns without using column names
8:28 Tip 6: Combine text in a cell with group by
10:17 Tip 7: Group by and ignore case
11:29 Tip 8: Get cell by position
13:01 Tip 9: Multi-step custom column
14:44 Tip 10: Declare column data type when adding a column
15:29 Wrap up
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
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
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.
That's a good tip. Thank you for sharing. 👍
Great video. Nice to see someone doing videos targeted at the more advanced users. 😊
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!
When when when???!!!
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.
no worst tip! All great! Thanks
Thanks 😁
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.
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 .
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 🤣
I love the line-feed feature. And combining text in a single cell is pretty slick! 😎
Thanks Oz - I’m glad you enjoyed it 👍
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.
1:03 => Even you can see the line break within data preview. To do that go to View Tab > Data Preview > Show whitespace
I had forgotten about that setting - that's a good tip.
Great stuff. Best one for me is adding a column using a cell ref.
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.
Tip 9 is by far the best in terms of widespread applicability. No worst tip in my view. Well done!
Yes, lots of uses for #9 - I hope you can put it to good use.
Another AWESOME tutorial !
Thank you. That's very kind of you to say.
Fabulous! Thanks Mark
Thanks, I'm glad they were useful.
Another GREAT video full of tips that will make my life easier. Thanks, Mark!
Happy to help!
Awesome Mark! Great video
Thanks! I'm glad you enjoyed it.
Mark, your explanation for multi-step custom colums using let is so helpful.
Thank you, I'm glad it was helpful. 😁
Just amazing Mark. Thank you
Glad you enjoyed it
Great collection of practical and helpful tips. Thank you.
Glad it was helpful!
Incredible tips! thanks for making my life easier using few of them right now!
Using some of them straight away... Awesome!
Thanks you for your video! Very powerfull tricks.
You're welcome, I hope you can put them to good use.
5 AND 6 are my favorite tips. Thank you
Great stuff. I think #5 should really be the default behaviour - it would save so many issues.
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.
👍 That’s a pretty useful tip that I’ve used in real world projects.
Great, i learned a lot, thks
Thank you, I'm glad it was helpful!
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?
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.
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
Thank you - clarity is always key.
Simply brilliant ❤
Thank you 😁
Молодец!
The first tip is awesome and is one I can use the most, Thanks!
Awesome that is great news!
Thanx's for blowing my mind. Going to revisit tomorrow. 🍻🍻
Good stuff - I hope you can pick up a few techniques.
Great collection of good tips. My preference goes to 8 and 9 !
8 & 9 - I really kept you waiting until the end until the good stuff.
@@ExcelOffTheGrid There were some good things right from the start 😉
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!
The Jack Bauer of Excel... I'll take that 😁
Great vidéo.
Thank you.
Hi Mark, all very useful, thank you.
My pleasure! Glad it was useful.
Great and useful tips & tricks which are really helpful in real life scenarios
Thank you. I hope you can put them to good use. 👍
Tip #6 - very handy.
Great - I hope you can put it to good use.
Nice 👍
Thank you! Cheers! 😁
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.
Wow More than great
Thanks, I’m glad you enjoyed it. 😁
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.
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.
Excellent tips, as always! How could we not like your ideas?
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 😁
Hi. Please could you do a similar video on loading/cleaning up PDF files to present in a long list...
PDF's are a pain! I know your struggles.
👍👍👍
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
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.
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
Mark your tips challenge with Chandeep was not as cutting edge as this one. Save the best for last. Kudos
Thank you. Without the Chandeep challenge, I wouldn't have thought about creating this video. So it's all thanks to Chandeep really. 😁
How to make Real Estate CRM with help of Google Sheet.. please making video.🤝
Hi Please i´m not finding the excel file to follow along? Tks in advance
I've pinned a comment at the top, with the URL and instructions.
why powerquery does not support .xlsb file?????
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.
Most of my viewers are watching on a desktop, so I optimize for that... sorry.