Getting Started with the Power Query Advanced Editor & M CODE for Beginners

Learn and understand the Power Query Advanced Editor which shows the behind the scenes code that Power Query is creating with each step by step transformation. Knowing these basics will help up your Power Query skills and introduce you to M Code, which is Power Query's formula language.
✅ Please see link to video mentioned in this video:
🎦Use Power Query to Combine Data from Multiple Sheets into One Sheet in Excel: • Easiest Way to Combine...
⏲️Video Timestamps:
00:00 What is the Power Query Advanced Editor and Understanding M Code
06:03 What do the Different Types of Brackets Do in Power Query?
11:15 How to Access the Full List of Power Query Functions
✅ If you would like to buy me a coffee, so I can get my caffeine in to keep creating as many videos as possible for you :). It's really quick and easy, please use this link:
☕www.buymeacoffee.com/missmicr...
✅ For more videos please subscribe:
🔔 / @missmicrosoft
✅ For more Power Query videos please use the links below:
🎦 Power Query in Excel Beginners Tutorial: Combine Multiple Excel Sheets, Automate & Transform Data: • Excel's Power Query: T...
🎦 Power Query Age Analysis - Create an Excel Age Analysis Report Using Power Query: • Power Query Age Analys...
🎦 Power Query Error Handling - How to Deal with Power Query Errors Part 1: • Power Query Error Hand...

[@anillpatel]

Excellent video presentation. Watch this video first to get a good understanding of the way Power Query works and the syntax for M Code. Thanks Melissa.

[@MissMicrosoft]

Thank you Anil! Glad it was helpful! 😊

[@malchicken]

Thank you for explaining the possible versions of blue text vs. green text. A video focused on each of those key terms would be much appreciated. Thank you 🙏🏽.

[@nuqwestr]

I use Udemy but your videos surpass all available there, as most instructors feel the need to inject "overt" personality and extraneous comments in their videos. I've learned more from you in 20-minutes than others I've watched for hours. I sent you a coffee, and will again. Miss Microsoft, you are a star.

[@MissMicrosoft]

Wow thank you so much @nuqwestr! I’m glad the videos are helpful, and thank you so much for the coffee, your support is greatly appreciated!😊

[@heishoi]

thank you , this is great video on M Code for beginner 👍👍👍

[@MissMicrosoft]

You’re most welcome!

[@JJ_TheGreat]

I ❤ Power Query!!

[@MissMicrosoft]

You watched the video! Thank you for watching :)

[@adaeben]

Nice. Well done. Very deliberate

[@MissMicrosoft]

Thank you 😊

[@pradeep4645]

Very useful, thank you.

[@MissMicrosoft]

You’re most welcome 😊

[@kailash680]

Very helpful. This information I missed in my Power Query course. Thank you.

[@MissMicrosoft]

So glad it was helpful!

[@heishoi]

Thanks

[@MissMicrosoft]

Thank you! I highly appreciate the support 😊

[@heishoi]

@@MissMicrosoft thank you for your efforts in making those videos. They are very beneficial to Excel users

[@dcpowered]

I am new to Power Query and this video is very helpful! Thanks a lot! Could you please explain how to use code shared by others as copied from their advanced editor?

[@MissMicrosoft]

I'm so glad to hear this video helped you Shakeel! I will definitely make a video on how to copy code shared by others. Please be sure to have your notifications turned on so that you know when that video is released, also I will add the link here as soon as it's up.

[@MissMicrosoft]

Hi Shakeel, I've made the video, please check it out here: kzbin.info/www/bejne/aIKXe5aZZ66ih6c

[@laarum5323]

how can i save query settings for later use ?

[@MissMicrosoft]

Hi, please check out this video: kzbin.info/www/bejne/aIKXe5aZZ66ih6c

[@saratoffice]

In power query if a step is wrongly deleted then how can we undo it ?

[@MissMicrosoft]

Hi Sarat Office, there is no undo if you delete a step in the Applied Steps in Power Query. I'd suggest as a workaround making a duplicate of your query and loading it to Excel to save it, then you have a backup of it if you incorrectly delete a step in the original query. Also if you're editing in the Advanced Editor then you can use Ctrl +Z to undo. The other trick you could also use if you wrongly delete a step, is instead of using "Close & Load" where the query will be loaded to Excel, rather click the "X" at the top right corner of your Query Editor to close the editor, it will then ask if you want to keep your changes, click "Discard" and your query will be loaded in Excel before the step was deleted.

[@thebhaskarjoshi]

Is there any full form of "M Language"?

[@MissMicrosoft]

Hi Bhaskar, not that I know of, Power Query Formula Language is a powerful query language optimized for building queries that mashup data. It's a functional, case sensitive language similar to F#, which can be used with Power Query, hope this helps.

[@JJ_TheGreat]

9:38 I find it interesting that to pull a column from a table, you use square brackets “[]” and it returns a list… Whereas, using the same type of brackets (square: “[]”) without indicating the table name actually returns/creates a record (NOT a LIST). To create a list, you have to enter the curly brackets - what I called “braces” - “{}”. This seems counter-intuitive to me!

[@MissMicrosoft]

It does seem counter-intuitive, but I will do my best to explain: tables in Power Query are made up of columns and rows. The basic structure of columns are lists and the basic structure of rows are records. So when returning a column from a table, a list will be returned. To return that list (column) we need to tell Power Query that the list is actually a column in a table, and the square brackets are what does this. The curly braces are used for lists that are not in a table because we don't need to go through a table to get to the list. The basic structure of rows are records. The square brackets without indicating a table give you a record, (like the curly braces without indicating a table give you a list). A record is made up of field names on the left, think of these as your column headers, and each field name (column header) has a value on the right, (think of the value as the single row in a table). So when you're accessing rows from a table, you're working with records. The key to understanding when to use the square brackets is that in a table they pull out an entire column as a list (the basic structure) and in a record they pull out specific field values. Examples: Square brackets in a table context: Sales[Revenue] returns the Revenue column as a list from the Sales table. Square brackets in a record context: Customer[Name] returns the value "John" from the Name field in the Customer record.

[@JJ_TheGreat]

5:51 And it becomes a text string!

[@MissMicrosoft]

Yes exactly!

[@neerajkaushik2800]

I appreciate your effort....i will highly recommend everyone to subscribe your channel

[@MissMicrosoft]

Thank you so much for your support and for recommending my channel, I really appreciate it!