Should we pipe M?

“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

Comments (3) Write a comment

  1. 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}}),

    Reply

    • 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.

      Reply

  2. Pingback: #Excel Super Links #138 – shared by David Hager | Excel For You

Leave a Reply