M code revelation!

[mikemck]

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.

 

[Ken Puls]

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:

<Applied Step Name> = <Formula in the formula bar> ,

The Applied Step Name will be 'escaped' if it contains special characters (like spaces):

#"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:

	[B]#"Changed Type"[/B] =		Table.TransformColumnTypes(
			[B]Source[/B],
			{
				{"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/powerquery-m/power-query-m-function-reference

Cheers,

 

[mikemck]

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.

 

[Ken Puls]

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:

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:

{ {"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. )

 

[mikemck]

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.

 

[Ken Puls]

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.