How to share M-code in PowerBI and Power Query

One of M’s advantages is that you can share M-code in textform and it will run instantly on a different computer. So no need to actually exchange a file. Provided that you pass the data on as well with your code, like you can see in this example.

But it would be a bit tedious to type in all the data manually – so let’s M do this automatically for us:

1 Automatically transfer a list into textform to share M-code

let
qList= {"This", "is", "a", "pretty", "short", "list"},
Source = qList,
CoreString = Text.Combine(List.Transform(Source, each Text.From(_)), """, """),
FullString = "= {"""& CoreString &"""}"
in
FullString

2 Automatically transfer a table into textform to share M-code

let
qTable= Table.PromoteHeaders(Table.FromColumns({ {"Column1" ,"This" ,"an" ,"shorter"}, {"Column2" ,"is" ,"even" ,"table"} })),
Source = qTable,
DemoteHeaders = Table.DemoteHeaders(Source),
ListOfColumns = Table.ToColumns(DemoteHeaders),
ConvertToTable = Table.FromList(ListOfColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
CoreString = Table.AddColumn(ConvertToTable, "Custom", each Text.Combine(List.Transform([Column1], each Text.From(_)),""" ,""")),
FullString = "= Table.PromoteHeaders(Table.FromColumns({ {"""& Text.Combine(CoreString[Custom], """}, {""")&"""} })),"
in
FullString

The techniques used are fairly simple (and very similar to what I’ve used in the Expression.Evaluate post): Transform your “real” content into a list or a list of lists. From there, combine the elements with the separator-signs that will build your desired output:

“, “ as separators for text and
“}, {“ as separators for lists/columns

This will return your “core” string:

Last step is to build the “wrapper”, providing the start-and end-values for your strings:

For the list it’s pretty simple: {“ But as the “ are used as a marker for text as well, you need to use 3 of them in order to make 1 into the actual output.

The table one is a bit more demanding. What you do is basically reversing all steps that transformed your table to text:

Table.DemoteHeaders -> Table.PromoteHeaders
Table.ToColumns -> Table.FromColumns
The rest is similar to the List-operation, in addition you need to separate the columns by “}, {“.

So if your data is quite large, you might want to put in a filter before your transform, but apart from that: Ready to go!

Enjoy & stay queryious 🙂

Edit 2016-Apr-11: Code in step “CoreString” has been changed in order to deal with non-text-formats as well.