Power Query Advanced Editor - Pro Tips Made Easy

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

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

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

[@this-is-bioman]

This video is indeed awsome! Finally someone explains how the Advanced Editor works. I can't stand all the basics tutorials any more 😅

[@MyOnlineTrainingHub]

Glad you liked it!

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

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

[@YouExcelTutorials]

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

[@MyOnlineTrainingHub]

You're welcome

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

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

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

[@rjbjajjjmj1]

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

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

You're welcome Darryl

[@wayneedmondson1065]

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

[@MyOnlineTrainingHub]

Thx Wayne

[@chrism9037]

This was really great, thanks Mynda!

[@masterboxxx]

really the best one who explain things

[@MyOnlineTrainingHub]

Thanks for your kind words, Ahmed!

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

[@tedpetito387]

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

[@MyOnlineTrainingHub]

Glad to help Ted

[@z.719]

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

[@MyOnlineTrainingHub]

Glad to hear it!

[@alexrosen8762]

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

[@MyOnlineTrainingHub]

Thx Alex.

[@craigbeuthin4299]

Fantastic, really needed that.

[@MyOnlineTrainingHub]

Thx Craig

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

[@mattschoular8844]

Very good overview. Thanks Phil

[@MyOnlineTrainingHub]

No worries Matt

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

[@nazarkamal8831]

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

[@MyOnlineTrainingHub]

Glad to hear that 😊

[@jengiuliano1143]

Excellent tutorial!

[@MyOnlineTrainingHub]

Glad you liked it, Jen!

[@shrikantbadge3978]

Thanks m gonna finish this playlist soon

[@MyOnlineTrainingHub]

Great to hear 👏

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

[@TGithinji]

Great stuff

[@juja2819]

Thank you :)

[@hamidsh4789]

Excellent...Thank you...

[@MyOnlineTrainingHub]

My pleasure :-)

[@kennethstephani692]

Great video!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Kenneth!

[@Bondoz007]

Thanks - excellent!

[@MyOnlineTrainingHub]

Thank you! Glad you liked the video 😊

[@Fxingenieria]

Excellent !!!!

[@MyOnlineTrainingHub]

Many thanks!!

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

[@小宇王-r4o]

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

[@elecirlisboa9155]

Excelente aula, parbéns!!

[@MyOnlineTrainingHub]

Thx Elecir

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

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

[@thebhaskarjoshi]

Super advanced tutorial

[@MyOnlineTrainingHub]

Glad you think so!

[@JonathanExcels]

Good information

[@loupasternak]

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

[@MyOnlineTrainingHub]

Thanks Lou

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

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

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

[@sunderjangraharsvikawoods2259]

nice

[@MyOnlineTrainingHub]

Glad you liked it 😊

[@7213261]

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

[@MyOnlineTrainingHub]

OK Sergey, I'll try!

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

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

[@غلغل-ح2ن]

You can speak in an Iraqi ☺️

[@MyOnlineTrainingHub]

Sorry, I'm afraid not.

[@غلغل-ح2ن]

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.