Power Query Advanced Editor - Pro Tips Made Easy

Power Query's Advanced Editor isn't just for Pros. In this video we demystify the advanced editor so anyone can feel comfortable modifying and writing Power Query M code. Includes creating variables, custom functions and commenting code.
0:00 Introduction
1:22 M Code Structure & Editing
4:07 Creating Variables
5:33 Using Variables in Custom Columns
10:26 Commenting Code
11:54 Writing Functions
Learn Power Query here: www.myonlinetraininghub.com/e...
View our comprehensive courses: www.myonlinetraininghub.com/
Connect with Mynda on LinkedIn: / myndatreacy

[@dougmphilly]

I received a spreadsheet with a power query but it only looked up one year and one corporate division. The provider received the spreadsheet from our IT department and I would have to wait for him to submit a revision request. Dude, just edit the code. Opened the advanced editor found what I needed to change, and the query now works for my needs. IT was like in shock that someone not from IT could do this. Who needs MIT or Cal Tech when I have Mynda.

[@MyOnlineTrainingHub]

Thanks Doug, glad this was useful for you.

[@morris5984]

Wow. This is one of the most helpful power query videos I have seen yet. Thank you for sharing!!!

[@MyOnlineTrainingHub]

You're welcome Bryan.

[@nadeemrajabali7839]

I have used advance editor sometimes, but never knew that we can create steps in between that can be called in custom columns or later in the code. Well done, great work.

[@Raymclau]

Wow this is the best Advance editor Video i have found yet Thank you very much first to you Mynda for this exceptional Chanel and also to Phillip for these very clear tips

[@MyOnlineTrainingHub]

Wow, thank you, Raynald!

[@rjbjajjjmj1]

EXCELLENT!! Really, very helpful. Thanks a ton!

[@YouExcelTutorials]

Great tutorial and well paced! Thanks for sharing Phil and Mynda!

[@MyOnlineTrainingHub]

You're welcome

[@chrism9037]

This was really great, thanks Mynda!

[@taylanyuksek2844]

Brilliant video! Short, precise and adds a lot of value to a person who is looking for ramping up his/her PQ skills.

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@aikkee]

Great tutorial on the M syntax. The key features of assigning variables and writing simple functions is very handy. Thank you for creating this video.

[@MyOnlineTrainingHub]

No worries Aik.

[@javiercabello3573]

This was great ! Thanks! hopefully you can do more videos of this kind with main focus on Functions. 👍👍

[@MyOnlineTrainingHub]

Thanks Javier, I'll make as note in my blog post/video plans to do more like this. Phil

[@wayneedmondson1065]

Great video! Thanks for this more advanced look under the hood with the Advanced Editor. Very helpful :)) Thumbs up!!

[@MyOnlineTrainingHub]

Thx Wayne

[@JSUG4219]

Excellent tutorial. Please, please teach us more about advanced M coding!

[@MyOnlineTrainingHub]

OK Jack, check out more of my vids on PQ. I cover a lot of advanced ideas there.

[@z.719]

Great video! Learned a lot tricks that didn't know before.

[@MyOnlineTrainingHub]

Glad to hear it!

[@shoaibrehman9988]

Epic tutorial Phil, superb video. Please continue this type of knowledge. Thanks a lot

[@MyOnlineTrainingHub]

Thx Shoaib, I'll try to do more videos like this

[@darrylmorgan]

Hi Mynda!Great Explanation Of The Advanced Editor Big Thumbs Up To Phil...Thank You Both :):)

[@MyOnlineTrainingHub]

You're welcome Darryl

[@tedpetito387]

Thanks, very useful information of which I was not previously aware.

[@MyOnlineTrainingHub]

Glad to help Ted

[@alexrosen8762]

Super useful tutorial and quite rare. More tutorials like these please :-)

[@MyOnlineTrainingHub]

Thx Alex.

[@masterboxxx]

really the best one who explain things

[@MyOnlineTrainingHub]

Thanks for your kind words, Ahmed!

[@mattschoular8844]

Very good overview. Thanks Phil

[@MyOnlineTrainingHub]

No worries Matt

[@BrainyBrunetteBarbie]

The Advanced Editor is awesome…I absolutely love it. This was super helpful, thank you! Mynda, I know you like your Excel-themed T-shirts…Phil should wear one here that says let Advanced Editor in

[@MyOnlineTrainingHub]

😁 Great idea!

[@craigbeuthin4299]

Fantastic, really needed that.

[@MyOnlineTrainingHub]

Thx Craig

[@jengiuliano1143]

Excellent tutorial!

[@MyOnlineTrainingHub]

Glad you liked it, Jen!

[@chukwumanwankwo92]

Love it!!. Interesting thing about adding a list in advanced editor. This means it might be possible to refer to an external dataset as a list or even a dynamic web page (possibilities seem endless)

[@MyOnlineTrainingHub]

Absolutely!

[@shrikantbadge3978]

Thanks m gonna finish this playlist soon

[@MyOnlineTrainingHub]

Great to hear 👏

[@elecirlisboa9155]

Excelente aula, parbéns!!

[@MyOnlineTrainingHub]

Thx Elecir

[@hamidsh4789]

Excellent...Thank you...

[@MyOnlineTrainingHub]

My pleasure :-)

[@juja2819]

Thank you :)

[@kennethstephani692]

Great video!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Kenneth!

[@TGithinji]

Great stuff

[@Bondoz007]

Thanks - excellent!

[@MyOnlineTrainingHub]

Thank you! Glad you liked the video 😊

[@nazarkamal8831]

Finally my some doubts has cleared thanks 🙏🙏🙏🙏👍👍

[@MyOnlineTrainingHub]

Glad to hear that 😊

[@Fxingenieria]

Excellent !!!!

[@MyOnlineTrainingHub]

Many thanks!!

[@GrainneDuggan_Excel]

Very clear and I formative. Thanks Phil! I hope you are going to offer an advanced MCode course on MOTH. Sign me up now!

[@MyOnlineTrainingHub]

Thx Grainne. I'll add a M Course to our To Do List.

[@JonathanExcels]

Good information

[@BjornK75]

Thanks for the superb tutorial. Is it feasible to use power query to do some of the basic calculations rather than relying on DAX? Which is more processing power efficient?

[@a436076]

Hi, general rule of thumb is data manipulation = PQ, calculations = DAX. There are articles on some PBI sites which explain the performance related aspects

[@thebhaskarjoshi]

Super advanced tutorial

[@MyOnlineTrainingHub]

Glad you think so!

[@user-nk9ew1eg8o]

Great sharing , while I do have one inquiry, I just encountered "Total wasn't recoginized xxx" message when I added same custom column as video, any advice?

[@MyOnlineTrainingHub]

Hard to say without seeing your file. You're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@Giovanni-vx8xl]

Thanks for sharing. Do u have a video about DAX for Power Bi? Thanks

[@MyOnlineTrainingHub]

DAX is a huge topic which I cover in my Power Pivot course: www.myonlinetraininghub.com/power-pivot-course

[@Seftehandle]

Great tutprial!! What does => mean in the let result in part at the end?

[@MyOnlineTrainingHub]

The => is part of the function declaration and indicates that the steps following it form the function code.

[@williamarthur4801]

Thanks, love anything to do with custom functions and advanced editor, but I do get in a mess with multiple nested let and in statements, the sort of thing is where I need; let xxxxx in let xxxx in, or let xxx let xxx in x in y , if that makes sense.

[@MyOnlineTrainingHub]

Nested LETs can be tricky.

[@paoloscano7897]

Hi Mynda. Thank you very much for your videos. They are extremely helpful. I wonder if you could help with a problem. I am not able to connect an excel worksheet to an Access dB protected with password. I have tried different ways to no avail. Can you make a video about it? I looked for solutions over the internet finding none. Thanks in advance.

[@MyOnlineTrainingHub]

There's no workaround here. You need to unprotect the Access DB.

[@paoloscano7897]

@@MyOnlineTrainingHub I was afraid of that. Thank you.

[@paoloscano7897]

@@MyOnlineTrainingHub I think I have just found a possible solution. Lucky strike I suppose. I first established a connection with an unencrypted db. Then I edited the connection adding the password and deleting what I guessed was inappropriate (password = "" etc).Here in after the lines. Please double check me. Thank you. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\RamsesII\Documents\dbMobili.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password=prova;Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

[@paoloscano7897]

PS i also changed the db path with the password protected db. It works.

[@7213261]

Thanks a lot! Please make more Advanced Editor`s video!)😀

[@MyOnlineTrainingHub]

OK Sergey, I'll try!

[@sunderjangraharsvikawoods2259]

nice

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@loupasternak]

so far 370 likes and 1 downvote from an IT guy who fears for his job. ;-) Another excellent tutorial !

[@MyOnlineTrainingHub]

Thanks Lou

[@naveen200848]

Thanks how can i get the percentage of subtotals like apple, banana etc

[@MyOnlineTrainingHub]

Hi Naveen, you can group the rows based on the Top Product, using Sum as the aggregate for the Amount column. Then add a new custom column diving the new Sum by the Total #"Grouped Rows" = Table.Group(#"Added Custom2", {"Top Product"}, {{"Sum", each List.Sum([Amount]), type nullable number}}), #"Added Custom3" = Table.AddColumn(#"Grouped Rows", "Custom", each [Sum] / Total) Regards Phil

[@naveen200848]

@@MyOnlineTrainingHub so tnank you

[@stormingorman5]

When you create the "Total" Variable and then divide each line by that total.. how do I create a total for a specific category? For instance if I have Group A with several items in Group B underneath group A, how do I total all of B and reflect that total for Group A? And then reflect each item in Group B as a percentage of Group A? Will provide data if anyone is able to help.

[@MyOnlineTrainingHub]

Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@karunakargm4139]

I want to learn power query and power pivot...like beginner to expert

[@MyOnlineTrainingHub]

Hi, we've got courses on both www.myonlinetraininghub.com/excel-power-query-course www.myonlinetraininghub.com/power-pivot-course Regards, Phil

[@tedmoy]

Great information, but not really that easy to understand. I wish I had learned this in college, but when I was in college, Excel was just coming out :(

[@MyOnlineTrainingHub]

Everything becomes easier the more you're familiar with it. Perhaps you'll find this introduction to Power Query a good place to start: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@physicsmostafa1774]

what is wrong with the good old SQL, why did microsoft had to invent {M} and DAX ?

[@MyOnlineTrainingHub]

I don't know, but I'd say they had a good reason 😉

[@user-ty8wu1xd2d]

You can speak in an Iraqi ☺️

[@MyOnlineTrainingHub]

Sorry, I'm afraid not.

[@user-ty8wu1xd2d]

Okay🙃💔

[@julieernst3942]

Eeekk, advanced editor is very intimidating 😧

[@MyOnlineTrainingHub]

Hi Julie, once you get a basic understanding of the code i.e. the steps, step naming conventions etc, it all starts to fall into place. Take a few small steps (no pun intended) modifying/writing your own M code and you'll gain confidence. You can then start to use functions to do some really cool stuff. Even write your own functions.