Results 1 to 6 of 6

Thread: M code revelation!

  1. #1
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    17
    Articles
    0
    Excel Version
    2016

    M code revelation!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I just had a serious revelation. I've been viewing the advanced editor and studying the M code there, and I get overwhelmed and discouraged at the complexity of the coding.

    I realized that viewing the applied steps themselves, just in the formula bar, is a completely different experience and much easier to understand.

    As an example, I had a query where I was getting the formula firewall error and solved it by bringing the files into my parameter query.

    In my parameter query, I used drill down on the parameter, added a custom step and wrapped what was displayed in File.Contents.

    Clicking the fx to add a custom step resulted in this in the formula bar: = #"Forecast File Folder & Name"

    All I did was wrap that like this: File.Contents("Forecast File Folder & Name")
    Voila, my files are now landed in my parameter query.

    Looking at the advanced editor, this is what I see : Custom1 = Excel.Workbook(File.Contents(#"Forecast File Folder & Name"), null, true)

    Well damn, that's a lot more complex than what I wrote. I started reviewing more queries and just the applied steps and using the gear icon to see what was done, and I'm actually thinking that maybe I can get to a point where I might be able to actually use some M code if I need to in cases where the UI is enough.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,404
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Mike,

    That's an interesting case. What's happened here is that Power Query has actually looked at your File.Contents(<file>) and recognized that it is an Excel file. It therefore helped you out by automatically adding the function to extract the workbook contents for you. The secret to the raw M code is that it is always in this format:

    Code:
    <Applied Step Name> = <Formula in the formula bar> ,
    The Applied Step Name will be 'escaped' if it contains special characters (like spaces):
    Code:
    #"Promoted Headers"
    And for any code written by Power Query, you'll also see a reference to the Previous step name as well. So in this case, the current step name is Changed Type, the previous step name is Source:
    Code:
    	#"Changed Type" =		Table.TransformColumnTypes(
    			Source,
    			{
    				{"Date", type date},
    				{"Category", type text},
    				{"Amount", Int64.Type}
    			}
    		)
    (In this one there is no comma at the end of the line because it is the last step in the query.)

    Hopefully that helps a bit. From there, I'd recommend doing what you're doing. Then the next step is to learn how the individual Power Query functions work, which you can find here: https://docs.microsoft.com/en-us/pow...tion-reference

    Cheers,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    17
    Articles
    0
    Excel Version
    2016
    Hi Ken,

    That seems easy enough for me to grasp, although I'm still a bit overwhelmed, or confused, looking at all of those lines of code. I'll put more time into looking though the link you provided. Slow & steady.

    Thank you very much on this one.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,404
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Just to clarify... don't start with that link. That documentation there is not user friendly at all. It's a resource to use when you are trying to figure out how to work with one specific function.

    For example, if I break down the code I gave you above, it uses the Table.TransformColumnTypes function, which has a signature like this:
    Code:
    Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table
    The first parameter (table) is "source" which (if you were to look at that step in the applied steps window) show as a table
    typeTransformations requires a list which is a specific type of object in Power Query. In M code, they are shown within curly braces. So in this case this part is the list:
    Code:
    { {"Date", type date}, {"Category", type text}, {"Amount", Int64.Type} }
    To be fair, it is actually a list that contains three individual lists, as each { } pair is a list of it's own.

    The final parameter "optional culture" is not defined.

    Like I say, this is not stuff you want to start with, but if you want to reverse engineer it to start understanding how the code is put together, it's one way. (Another would be to read chapters 19-20 of M is for Data Monkey, where we describe how M code works. )
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    17
    Articles
    0
    Excel Version
    2016
    I'm very glad you said that, as I can't really understand it, at a practical level. The syntax, I think, is what I have trouble with.

    I have the M is for Data Monkey, and have read it several times. Always learn something new each time I read it, or understand something I didn't previous get at fundamental level.

    BTW, I would just like to say it's very cool that you are here.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,404
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'll be honest, it took me a long time to wrap my head around M, and yeah, the syntax is... weird. But it just takes time and perseverance. Trust me on this. When I first started, I never thought I'd be able to read it, let alone explaining it to others.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •