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

[AlexisOlson]

Tagging a few folks who I think might be interested and might not otherwise stumble across this post:

@ImkeF, @BA_Pete, @lbendlin, @Greg_Deckler, @edhans, @wdx223_Daniel, @m_dekorte, @watkinnc, @bcdobbs, @parry2k, @smpa01, @ValtteriN

[parry2k]

@AlexisOlson very useful, thanks for sharing. Cheers!!

[smpa01]

@AlexisOlson This is awesome with auto-generated schema for the table.

[PinataFarm]

useful, thanks

[ImkeF]

Sweet, that's really cool.
Very well done, especially for including all the types here as well.
Now we just need a M-Library so we can have this function readily at hand in the PQ editor 🙂

[m_dekorte]

@AlexisOlson this is great!

And will be so useful to a lot of users. Thanks for sharing!

[Rickmaurinus]

@AlexisOlson ,

That's a fun one. Also really like how you used Table.Schema and inserted your single values into it to extract the type. Have you tested it on bigger tables yet, if so how did it go?

[ValtteriN]

Thanks for tagging me! Interesting read and quite useful idea. 

[bateoben]

I looking for some one who can intergrate share point and Oracle . Please emai me on bateaugustine@gmail.com         Our company is looking for any tool that can intergrate share point ...

[parry2k]

@bateoben on the surface it should be doable. Just emailed you. Let me know when you can connect. Cheers!! 

[jian123]

useful, thanks.