“Just because you could doesn’t mean you should”… So I’m asking the Power Query and M fans & experts here if we “should” pipe M:
Background: With M you can nest your expressions like in Excel to group commands that belong together. But this has some disadvantages like:
- Reading:
- the execution order of the functions doesn’t match the reading order
- the function name and the arguments are torn apart
- Writing:
- if you write an additional function around the existing expression which then fails, it is very laborious to manually delete all the code to go back to the previous state (especially, if you have trailing function arguments)
- if you later recon that you need an intermediate step of the nested expression and need to split up the statement, the same problems occur
So instead of this code:
we could write it like so:
This code works in M if you have a record (“M”) in your queries that contains Kim Burgess’ cool code and an additional record (“M”) that he has kindly helped me with:
All that still folds!
Honestly, it doesn’t look pretty in my eyes (yet), but it works and eliminates the disadvantages mentioned above. With some help of Expression.Evaluate, we could further clean it up to match the magrittr-style for example, but I’ve been warned to use this function, so not sure what to prefer at the end.
Please let me know your thoughts & stay queryious 😉
File to download: ShouldWePipeM.zip
Question: How would this code look like
Table.TransformColumnTypes(toTable,{{“id”, Int64.Type}, {“name”, type text}, {“status”, type text}, {“link”, type text}, {“urlname”, type text}, {“description”, type text}, {“country”, type text}, {“city”, type text}, {“localized_country_name”, type text}, {“localized_location”, type text}, {“join_mode”, type text}, {“visibility”, type text}, {“who”, type text}, {“timezone”, type text}, {“members”, Int64.Type}, {“lon”, type number}, {“lat”, type number}, {“state”, type text}}),
Good point Miguel!
Didn’t consider a scenario where everything would be piped by default. So a single function that isn’t nested like this would then look like so:
= M[M](toTable, {{Table.TransformColumnTypes, {{“id”, Int64.Type}, {“name”, type text}, {“status”, type text}, {“link”, type text}, {“urlname”, type text}, {“description”, type text}, {“country”, type text}, {“city”, type text}, {“localized_country_name”, type text}, {“localized_location”, type text}, {“join_mode”, type text}, {“visibility”, type text}, {“who”, type text}, {“timezone”, type text}, {“members”, Int64.Type}, {“lon”, type number}, {“lat”, type number}, {“state”, type text}}}}),
So the first 2 arguments are swapped and 2 additional curly bracket-pairs are needed.
I think that “piping” is what the PQ GUI is all about. Each step effectively pipes the result of the previous step to the next one. “let” makes things explicit, and the unnesting makes the code readable, as opposed to Excel formula language (and thank you DAX team! for adding similar syntax).
Trouble ensues when you resort to the (so-called) Advanced Editor.
The more I tweak the M, the easier it is to lose all of the steps. But damn, it’s great to have those steps; for testing and for maintenance, etc..
There’s just a natural tension between what Microsoft has invented in the PQ GUI along with the demands of a hand-written functional language program. I’m impressed with what they’ve come up with so far and am anticipating what the “real IDE” will be.
It’s a difficult problem, and the various other places that Power Query is sure to show up in will add other difficult requirements for the IDE (e.g. when will be be able to emit multiple outputs from a single query?).
In the meantime, I’ve been trying to evolve a coding style that leverages today’s GUI and also accommodates hand-coded M.
I’ve learned that the query must be organized into top-to-bottom steps, but some of the steps may be:
StepX = let /*do some stuff */
The GUI will show and resolve the step, but won’t walk into the nested let. This is often good enough though — maintainers can examine the result of the “subprogram” in the GUI.
Also the result of the query must refer to the last step’s result. I use:
Result = /* last step */
in
Result
Note: You can also include steps anywhere in-between which are not referenced elsewhere in the query. The GUI will show them. This is useful for adding (sort of) unit tests, or debugging steps, or sanity checks.
Sometimes I add a couple of steps at the top for navigating #shared that “exist” only in the PQ GUI and are there for the developer (me).
I beg forgiveness if you do all these things already too, but I haven’t seen any discussion of these ideas elsewhere. (And thanks for the link to Kim Burgess’s work!).
Cheers
Thank you very much for this detailed response, this is an interesting perspective.
Are you a developer/programmer by trade or do you come from the Excel-world?
Cheers, Imke
I’m a developer who works with end users enough to have learned Excel programming along the way (often the “last mile” and/or the first one for many systems in my experience). A friend, who’s lucky enough to be doing DAX these days, pointed me to your helpful blog. Thanks.
Thank you! The reason why I asked is that I expect developers to be more comfortable with long code than Excel-users.
So I still try to use as little words/expressions in my code to make it look less intimidating.
Thanks for the cools tricks around lazy evaluation, I’m still not utilizing them to the full extend. Just recently saw Marcel Beug using “empty steps” as comments which can be seen in the Query Settings: https://youtu.be/ZvP8SoIz3VE?t=405 . Finding that very cool as well.
Hey Imke,
I see a potential here for an utility library that enhances the functional aspect of Power Query. The JavaScript world has a few good examples namely Underscore.JS, Ramda.JS, Lodash.
So I’ve wrapped the m-tools repo code into a library which I hope I can extend and include in my projects.
https://github.com/Hugoberry/PowerQueryFunctional
Pull requests are more than welcome
Hey Igor, very cool!
Will send you my chained chain soon (still find that better for later debugging) 🙂