Sharing Power Query tables

[AlexisOlson]

[Note: All of the queries below are included in the attached PBIX file. I recommend opening up that file to follow along.]

As I mentioned in How to create json table for easier sharing pbix file, creating a table using the Enter Data tool results in an M code query that looks like the following [this is Table1 in the attached PBIX]:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4pKi0lQgZahvqG9kYGCoFKsTrWQEFEgC4rTEnGKQpJG+EUjSCCxpDBRIRmg01jcGyRkrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", type text}, {"C", type logical}, {"D", type date}})
in
    #"Changed Type"

That's a lot of ugly and opaque code for something that can be written more cleanly as

let
    Source =
        Table.FromRows(
            {
                { 1, "a", true, #date(2001, 1, 1) },
                { 2, "b", false, #date(2002, 2, 2) },
                { 3, "c", true, #date(2003, 3, 3) }
            },
            type table [A = number, B = text, C = logical, D = date]
        )
in
    Source

This format doesn't have gibberish text as compressed binary data, which makes it more readable not only for fellow humans but also for change tracking / version control systems like Git.

To achieve this, I've written a custom function that converts a query into M code like the simplified example above.

let
  func =
    (TableName as table) as text =>
    let
      Source = Table.ToRows(TableName),
      Indent = Text.Repeat(" ", 16), 
      RowSeparator = " },#(lf)" & Indent & "{ ", 
      Schema = Table.Schema(TableName), 
      ValToText = (x) =>
        let
          xType = Value.Type(x), 
          /*Hack to convert data type to plain text*/
          xTypeText = Text.BeforeDelimiter(
            Table.Schema(
              #table(type table [Col = Value.Type(x)], {{x}})
            ){0}[TypeName], 
            "."
          ), 
          result = 
            if x = null then
              "null"
            else if xType = Text.Type or xType = Any.Type then
              """" & x & """"
            else if xType = Number.Type or xType = Logical.Type then
              Text.From(x)
            else if xType = Date.Type then
              "#date("
                & Text.From(Date.Year(x)) & ", "
                & Text.From(Date.Month(x)) & ", "
                & Text.From(Date.Day(x))
                & ")"
            else if xType = Time.Type then
              "#time("
                & Text.From(Time.Hour(x)) & ", "
                & Text.From(Time.Minute(x)) & ", "
                & Text.From(Time.Second(x))
                & ")"
            else if xType = DateTime.Type then
              "#datetime("
                & Text.From(Date.Year(x)) & ", "
                & Text.From(Date.Month(x)) & ", "
                & Text.From(Date.Day(x)) & ", "
                & Text.From(Time.Hour(x)) & ", "
                & Text.From(Time.Minute(x)) & ", "
                & Text.From(Time.Second(x))
                & ")"
            else if xType = DateTimeZone.Type then
              "#datetimezone("
                & Text.From(Date.Year(x)) & ", "
                & Text.From(Date.Month(x)) & ", "
                & Text.From(Date.Day(x)) & ", "
                & Text.From(Time.Hour(x)) & ", "
                & Text.From(Time.Minute(x)) & ", "
                & Text.From(Time.Second(x)) & ", "
                & Text.From(DateTimeZone.ZoneHours(x)) & ", "
                & Text.From(DateTimeZone.ZoneMinutes(x))
                & ")"
            else if xType = Duration.Type then
              "#duration("
                & Text.From(Duration.Days(x)) & ", "
                & Text.From(Duration.Hours(x)) & ", "
                & Text.From(Duration.Minutes(x)) & ", "
                & Text.From(Duration.Seconds(x))
                & ")"
            else
              xTypeText & ".FromText(""" & Text.From(x) & """)"
        in
          result, 
      DataRows_Text = Text.Combine(
        List.Transform(
          Source,
          each Text.Combine(List.Transform(_, ValToText), ", ")
        ), 
        RowSeparator
      ), 
      ColTypes_Text = Text.Combine(
        List.Transform(
          Table.ToRecords(Schema),
          each [Name] & " = " & [Kind]
        ), 
        ", "
      ), 
      Query =
"let
    Source =
        Table.FromRows(
            {
                { " & DataRows_Text & " }
            },
            type table [" & ColTypes_Text & "]
        )
in
    Source",
      StandardDataTypes = {
        "binary", 
        "date", 
        "datetime", 
        "datetimezone", 
        "duration", 
        "logical", 
        "number", 
        "text", 
        "time",
        "any"
      }, 
      UnhandledTypes = List.RemoveItems(Schema[Kind], StandardDataTypes), 
      Output =
        if not List.IsEmpty(UnhandledTypes) then
          "Unsupported Column Types"
        else
          Query
    in
      Output,
  documentation = [
    Documentation.Name =  " Table_to_M ",
    Documentation.Description = " Converts a table into M code text that recreates the table as query. "
  ]
in  
  Value.ReplaceType(func,Value.ReplaceMetadata(Value.Type(func), documentation))

To use this function, right-click in the Queries pane to create a new blank query.

Then rename this new query Table_2_M and paste the code above into the Advanced Editor (replacing the blank query code). [Alternatively, you can copy this function from the attached PBIX file and paste it into other query editors.]

Once this is done, you should see the function in the Queries pane prefixed with a little "fx" icon and when you click on the function, there should be a dropdown where you can choose to invoke the function on any of the other queries you have (if any) in your Queries pane.

Suppose we define the query I started the post with as Table1 so that now it shows up in the Queries pane.

We can then choose this table/query from the Enter Parameter dropdown list in the Table_To_M and click Invoke. The result should be a new query called Invoked Function that outputs M code text that looks like this:

If you paste this code into the Advanced Editor of a new or existing query, it should return the same table as the ugly code at the start of this post.

Let me know what you think or if you run into any errors I didn't come across in testing.

--Alexis O.

PQ Tables.pbix