I was chatting with my friend Scott of www.tinylizard.com today, discussing the structure of the M language, and how it’s kind of weird compared to VBA, C# and such. Well, “what the #?”, I figure. I’m going to do a blog post on what the # sign actually signifies today, as it’s kind of confusing when you’re just starting with M.
A Little M Code:
I knocked up a bit of M code today to illustrate the challenge for someone just getting into this language. Here’s a snap of the beginning of each line:
Okay, so every line starts with something followed the = character… but…
What the #?
The question that I’m looking at specifically is… why do some lines start with # and some don’t? (The second lines starts Source = and the fourth start ReplacedValue = but all others start with the # character.)
Literally…
The secret is all in the step name. Check out the recorded steps for this query:
Notice anything about the two highlighted entries compared to the others? No spaces!
When Power Query comes up against a space in the code it expects that there is another command or parameter coming next. To deal with this, we need to tell Power Query that “Hey, I want you to literally use this…” I’ve always heard this referred to as “escaping literals”, and M isn’t the only language that this happens in.
But it’s not sufficient to just use the # sign either. It needs to be the # sign followed by two sets of quotes… kind of like how we refer to text in formulas in Excel. So basically what you get is:
#”Filtered Rows” =
I Don’t Like It!
Yeah, me either. I think it makes the code look horribly ugly. It’s easy to avoid if you want to though, although it takes a little work. Just right click the step, choose Rename, and remove the space:
At this point your M code will be updated to remove the literals, and you’ll be back to cleaner code.
You could also edit the M code manually to remove the # and the leading and trailing quotes. If you do this, however, just make sure that you get all instances of them, as there will always be two in your recorded code, and possibly more if you’ve been manually tweaking it:
While I’m not a big fun of ugly code, I’ve also got to accept that I’m one of a very small percentage of users who will actually read it. Most people will use the UI for this kind of stuff, so making it read easier there is probably the right design decision.
At any rate, now you know what the # character is all about.
4 thoughts on “What the #? Literally…”
That's some great info. It took me a while to wrap my head around the #!
How do describe the {} in this code:
#"Capitalized Each Word" = Table.TransformColumns(Source,{},Text.Proper)
What's the deal with the triple quotes that I've stumbled across in M?
Can you translate your R code snippet from this post into layman's terms? That would really help me trudge through the M code learning curve.
I wish we could just re-use Source throughout the code like this:
Source = Excel.CurrentWorkbook()...
Source = Table.Transform...
in
Source
I split time between R and Excel, so I'm used to massaging data in R that way.
Cheers!
Jimmy
Hey Jimmy,
You might find this article somewhat relevant to your M journey
http://www.poweredsolutions.co/2015/02/04/getting-started-understanding-power-query-m-language/
the thing about reusing the Source is that, it wouldn't be a best practice.and the way that Power Query handles that is that instead of creating a new step it actually does all that *excel current workbook and table transform* all in one take like this> Source = Table.Transform *ExcelCurrentWorkbook
You can test that out using the UI. Try removing a column and then remove another one right after that 🙂 - it will not add a new step but it'll add that to the same step that you're in.
I know some R but I never really scrapped or rearranged data with R. I only use it for Machine Learning scenarios and for testing purposes or when I need a cool graph that is not part of Excel. *the apply functions actually have some similarities to the X functions in DAX*
Anyway, I've written a lot already 🙂 / thanks for reading
Hey Jimmy,
Can't help with the R, as I've never touched that program.
With regards to the {} portion of that code... this is one of those "oh brother" moments. Here's the help article on the TransformColumns function: https://support.office.microsoft.com/en-us/article/TableTransformColumns-523173e7-96ed-4d33-918b-4329b4be6223
It helpfully lists the second parameter as "The list of transformOperations to run.". Typically the curly braces surround an array, so I'd say in this case that it's providing no list of any special operations, since there is nothing between the braces. What bugs me though, is where is the link in that article to a valid list of transformOperations? Would be helpful to know what valid choices there are!
Thanks for the pointers!