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

[@ExcelOffTheGrid]

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

[@larmondoflairallen4705]

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.

[@ExcelOffTheGrid]

That's a good tip. Thank you for sharing. 👍

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

[@kndeepak]

When when when???!!!

[@ExcelOffTheGrid]

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.

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

[@ExcelOffTheGrid]

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 🤣

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

[@ExcelOffTheGrid]

I had forgotten about that setting - that's a good tip.

[@bilalsheikh9127]

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

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

[@ExcelOffTheGrid]

Somebody estimated that 40% of scenarios can be solved with the Power Query UI. So that means that 60% requires some additional knowledge. It's worth investing the time.

[@giorgioberardi3940]

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

[@ExcelOffTheGrid]

Yes, lots of uses for #9 - I hope you can put it to good use.

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

[@ExcelOffTheGrid]

👍 That’s a pretty useful tip that I’ve used in real world projects.

[@OzduSoleilDATA]

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

[@ExcelOffTheGrid]

Thanks Oz - I’m glad you enjoyed it 👍

[@EricHartwigExcelConsulting]

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.

[@New2you09]

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

[@ExcelOffTheGrid]

Glad it was helpful! 😁

[@GrainneDuggan_Excel]

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

[@ExcelOffTheGrid]

Thank you, I'm glad it was helpful. 😁

[@timtunbridge]

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.

[@ExcelOffTheGrid]

Yes, that will definitely help in that scenario. 👍

[@westleyempeigne6541]

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!

[@chrisgilbert9921]

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

[@ExcelOffTheGrid]

Happy to help!

[@gabrielgordon]

no worst tip! All great! Thanks

[@ExcelOffTheGrid]

Thanks 😁

[@NestorCirhuza]

5 AND 6 are my favorite tips. Thank you

[@ExcelOffTheGrid]

Great stuff. I think #5 should really be the default behaviour - it would save so many issues.

[@leonidkoyfman814]

Great collection of practical and helpful tips. Thank you.

[@ExcelOffTheGrid]

Glad it was helpful!

[@ExcelWithChris]

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

[@ExcelOffTheGrid]

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.

[@tracywilliams61]

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

[@ExcelOffTheGrid]

Thank you - clarity is always key.

[@raimundojs9547]

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!

[@ExcelOffTheGrid]

The Jack Bauer of Excel... I'll take that 😁

[@japa6225]

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

[@ExcelOffTheGrid]

Good stuff - I hope you can pick up a few techniques.

[@ExcelerateYourBiz-or2we]

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.

[@ExcelOffTheGrid]

Yeah - that is a really useful technique. 👍

[@EasternGroup-v8z]

Hi, 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?

[@hidekel7916]

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

[@MaureenPesch]

Excellent tips, as always! How could we not like your ideas?

[@ExcelOffTheGrid]

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 😁

[@abbaslokhandwala8445]

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

[@ExcelOffTheGrid]

Yes, these tips are for more intermediate users. I've got another PQ tips video in the pipeline, so I might cover some more beginner level tips there.

[@Ganja1974]

Incredible tips! thanks for making my life easier using few of them right now!

[@ExcelOffTheGrid]

Using some of them straight away... Awesome!

[@Oprimaita]

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

[@ExcelOffTheGrid]

8 & 9 - I really kept you waiting until the end until the good stuff.

[@Oprimaita]

@@ExcelOffTheGrid There were some good things right from the start 😉

[@VIPULDJHAVERI]

Simply brilliant ❤

[@ExcelOffTheGrid]

Thank you 😁

[@michaeljones2843]

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

[@ExcelOffTheGrid]

Awesome that is great news!

[@tibibara]

Brilliant tutorial Mark, thanks a lot!

[@ExcelOffTheGrid]

Glad it was helpful! 😁

[@bretogden3838]

Tip 6 is awesome!

[@KennedyMateko]

Fabulous! Thanks Mark

[@ExcelOffTheGrid]

Thanks, I'm glad they were useful.

[@ja3mi2]

Tip #6 - very handy.

[@ExcelOffTheGrid]

Great - I hope you can put it to good use.

[@tlee7028]

Another AWESOME tutorial !

[@ExcelOffTheGrid]

Thank you. That's very kind of you to say.

[@pt3470]

Great useful video! Favourite was steps 8 and 9. Do you teach formulas for PQ in your course?

[@ExcelOffTheGrid]

Thank you. We don't currently have a M code course in our program. But it should be coming later in the year.

[@StephanBenne]

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.

[@alexrosen8762]

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

[@ExcelOffTheGrid]

Thank you. I hope you can put them to good use. 👍

[@gonzuic]

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

[@ExcelOffTheGrid]

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.

[@pierre-yves_david]

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.

[@ExcelOffTheGrid]

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.

[@chrism9037]

Awesome Mark! Great video

[@ExcelOffTheGrid]

Thanks! I'm glad you enjoyed it.

[@patrickharilantoraherinjat2994]

Just amazing Mark. Thank you

[@ExcelOffTheGrid]

Glad you enjoyed it

[@tomhaase1386]

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.

[@ExcelOffTheGrid]

In the Split Column dialog, I’m sure there are options in the advanced section to achieve that.

[@90hsilva]

Rename and expand columns 👍

[@zzota]

Hi Mark, all very useful, thank you.

[@ExcelOffTheGrid]

My pleasure! Glad it was useful.

[@lukasj7689]

decent tips!

[@ouvangouvang5055]

Great vidéo.

[@ExcelOffTheGrid]

Thank you.

[@HimanshuSingh-lk2my]

Nice 👍

[@ExcelOffTheGrid]

Thank you! Cheers! 😁

[@NishatKarim-b4x]

Hi. Please could you do a similar video on loading/cleaning up PDF files to present in a long list...

[@ExcelOffTheGrid]

PDF's are a pain! I know your struggles.

[@marcus_barao]

Thanks you for your video! Very powerfull tricks.

[@ExcelOffTheGrid]

You're welcome, I hope you can put them to good use.

[@txreal2]

Great tricks 👍👍. I think for newbie -- > intermediate, the long codes maybe overwhelming. Personally, I try to balance stealing codes with readability; like months from now :) (especially when you're unaware source data has been changed, or maybe use #metadata to reference a step somewhere else if possible )

[@TheLaopi]

Great, i learned a lot, thks

[@ExcelOffTheGrid]

Thank you, I'm glad it was helpful!

[@seaman4798]

Thank you for the video. Regarding Part 5. Why aren't you using shorter code?: Tables.Combine(Source[Data]) It will concatenate all the tables and output the same result.

[@ExcelOffTheGrid]

It doesn’t output the same results; it removes all the existing columns. Which may or may not be what you want.

[@seaman4798]

​@@ExcelOffTheGrid Yes. It will remove columns from the table that holds our nested tables. But the columns of the nested tables are preserved. In 90% cases it is what we actually want. At least, this approach is worth mentioning IMHO.

[@SSi-nq3rt]

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)

[@ExcelOffTheGrid]

Thanks for sharing! I’m glad you’ve found them useful.

[@OZbMG8jsJTX14AWYne4omBw]

Молодец!

[@GeertDelmulle]

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?

[@ExcelOffTheGrid]

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.

[@hazemali382]

Wow More than great

[@ExcelOffTheGrid]

Thanks, I’m glad you enjoyed it. 😁

[@oscarpeters2372]

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 👍

[@ExcelOffTheGrid]

You are correct, and we’ve got loads other videos about data structure which teach those concepts. This is a video of simple M code changes to solve common transformation problems.

[@marcusmayer1055]

👍👍👍

[@deepakn9796]

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

[@EasternGroup-v8z]

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?

[@ExcelOffTheGrid]

Yes, it's possible. But you will need quite a bit of skill to achieve it.

[@EasternGroup-v8z]

@@ExcelOffTheGrid Someone already helped me with below code but its very time consuming and have to ask colleagues to stop working on the folder where all files are stored. Can you please guide/help me to achieve as mentioned above? Sub CopyValuesFromFiles() Dim sourceFolder As String Dim sourceFiles As Object Dim sourceFile As Object Dim WbSource As Workbook Dim wsDestination As Worksheet, ArrB() As Variant, ArrB2() As Variant Dim Rng As Range, LastRow As Integer, Arr() As Variant, ArCnt As Integer, Cnt As Integer On Error GoTo ErFix Application.ScreenUpdating = False Application.DisplayAlerts = False ' Set the destination worksheet modify sheet name accordingly Set wsDestination = ThisWorkbook.Worksheets("Customers") ' Set the path to the source folder modify accordingly sourceFolder = "Z:\DOCUMENTS\INVOICES- 24-25\" ' Create a FileSystemObject to work with files in the folder Set sourceFiles = CreateObject("Scripting.FileSystemObject").GetFolder(sourceFolder).Files ' Loop through each file in the folder For Each sourceFile In sourceFiles ' Check if the file is an Excel file If sourceFile.Name Like "*.xlsm*" Then 'load ranges to arrays ArCnt = ArCnt + 1 ReDim Preserve Arr(ArCnt) ReDim Preserve ArrB(ArCnt) ReDim Preserve ArrB2(ArCnt) ' Open the source workbook Set WbSource = Workbooks.Open(sourceFile.Path) ' Copy the values from B53 to O153 Set Rng = WbSource.Worksheets(1).Range("B53:O153") Arr(ArCnt - 1) = Rng ArrB(ArCnt - 1) = WbSource.Worksheets(1).Range("B1") ArrB2(ArCnt - 1) = WbSource.Worksheets(1).Range("B15") ' Close the source workbook without saving changes WbSource.Close savechanges:=False End If Next sourceFile 'Place the values to the destination worksheet For Cnt = LBound(Arr) To UBound(Arr) - 1 With wsDestination LastRow = .Range("A" & .Rows.Count).End(xlUp).Row End With wsDestination.Range("A" & LastRow + 1).Resize(UBound(Arr(Cnt)), 14).Cells.Value = Arr(Cnt) wsDestination.Range("O" & LastRow + 1).Value = ArrB(Cnt) wsDestination.Range("O" & LastRow + 2).Value = ArrB2(Cnt) Next Cnt ErFix: If Err.Number 0 Then MsgBox "Error" Else ' Display a message when the copying is complete MsgBox "Copying customer information from files complete." End If Application.ScreenUpdating = True Application.DisplayAlerts = True Set sourceFiles = Nothing End Sub

[@DinoAMAntunes]

Hi Please i´m not finding the excel file to follow along? Tks in advance

[@ExcelOffTheGrid]

I've pinned a comment at the top, with the URL and instructions.

[@beautifulthoughts1775]

How to make Real Estate CRM with help of Google Sheet.. please making video.🤝

[@winter9798]

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

[@ExcelOffTheGrid]

Based on your question, I would say that you're not using Power Query in the right way. You're mixing data and presentation and causing high levels of complexity. What you load into Excel should be normalized data with a single month column and a single value column. Once the data loaded into Excel, you then use Formulas / PivotTables on the data to create the required layout. Check out this video to understand why you want this structure: kzbin.info/www/bejne/rqfXoItshp56gtE

[@arpankumar4487]

why powerquery does not support .xlsb file?????

[@SairamBalasubramaniam]

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

[@ExcelOffTheGrid]

Thank you. Without the Chandeep challenge, I wouldn't have thought about creating this video. So it's all thanks to Chandeep really. 😁

[@vishalborase5506]

seriously where a data analyst draw a line while all the youtubers are coming up with these millions of tips and making us feel like we are still so far behind :)

[@ExcelOffTheGrid]

Each time the technology moves we are all catching up. You either learn in an ad hoc path by watching KZbin videos, or a structured path using a course. One is free, confusing and slow, the other is paid, clear and fast. Either way, we are all still learning.

[@mohitchaturvedi8931]

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.

[@ExcelOffTheGrid]

Most of my viewers are watching on a desktop, so I optimize for that... sorry.