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

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

★ Get the example file ★

Sign up for our free Insiders program and get the example file:

★ 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 ★

★ Where to find Excel Off The Grid ★

#MsExcel

[ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0212 Power Query M code tricks.zip

[larmondoflairallen]

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.

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

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

[abbaslokhandwala]

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

[tibibara]

Brilliant tutorial Mark, thanks a lot!

[OzduSoleilDATA]

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

[Newyou]

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

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

[giorgioberardi]

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

[ExcelWithChris]

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

[tracywilliams]

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

[Oprimaita]

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

[GrainneDuggan_Excel]

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

[raimundojs]

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!

[NestorCirhuza]

5 AND 6 are my favorite tips. Thank you

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

[chrisgilbert]

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

[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

[leonidkoyfman]

Great collection of practical and helpful tips. Thank you.