A Function To Generate The M Code For A Table Type

This is going to sound obscure, and it is, but recently I’ve been using the #table() intrinsic function in M a lot – specifically the version that takes a table type as its first parameter (as I describe here) – and because it’s a bit of a pain to have to write the M code for a table type by hand, I’ve written an M function that takes a table and returns the text for the M code that is needed to define a table type. Here it is:

(InputTable as table) as text =>
let
    Source = 
        Table.Schema(InputTable),
    SortRows = 
        Table.Sort(
            Source,
            {{"Position", Order.Ascending}}),
    RemoveColumns = 
        Table.SelectColumns(
            SortRows,
            {"Name", "TypeName"}),
    AddCustom = 
        Table.AddColumn(
            RemoveColumns, 
            "TypeNames", 
            each 
            Expression.Identifier([Name]) & " = " & [TypeName]),
    Output = 
        "[" & Text.Combine(AddCustom[TypeNames], ", ") & "]"
in
    Output

Nothing complex here, but now I’ve posted this I know that in the future I’ll be able to Google for it when I’m working onsite with a customer and I need it!

To give you an idea of how it works, take the table that is returned by the following M expression, which calls the public TripPin OData web service:

OData.Feed(
"https://services.odata.org/TripPinRESTierService/Airports", 
null, 
[Implementation="2.0"])

image

Passing this table to the function above returns the following text, the M code for a record that lists the names of the columns in this table and their data types, suitable for use with #table:

[Name = Text.Type, IcaoCode = Text.Type, 
IataCode = Text.Type, Location = Record.Type]

One response

  1. Just what I needed, and it works brilliantly! thanks for this.T
    A few things you might want to consider:
    1. It took me an incredible amount of googling to land here (I had almost given up). It wouldn’t be a problem if, as underlined by Dale Hohm* two years ago, the useful info on M wasn’t still made of bitty tips (of which this page is) scattered around the internet.
    2. Even though I had a function to create the string for a row type (which is what M is expecting after the directive “type table”), I still had to work out how to make your trick work. As it is, a novice M user would not stand a chance of using it. Please consider adding a full example.
    3. For some reason TypeName doesn’t work for me. PQ complains that Number.Type or Text.Type “are not valid in this context” when I evaluate the expression. However, the trick works well with the Kind column of the schema.

    Here is what the full code looks like for me:

    let
    fTableType = (InputTable as table) as type =>
    let
    Source = Table.Schema (InputTable)
    ,SortRows = Table.Sort (Source, {{“Position”, Order.Ascending}})
    ,AddCustom = Table.AddColumn (SortRows, “TypeNames”,
    each Expression.Identifier ([Name]) & ” = ” & [Kind])
    ,TypeString = “type table [” & Text.Combine(AddCustom[TypeNames], “, “) & “]”
    ,Output = Expression.Evaluate (TypeString)
    in
    Output
    // Main body
    ,ExistingTable = #table (type table [Column1 = number, Column2 = text], {})
    ,Table = #table (fTableType(ExistingTable), {{1, “A”}, {2,”B”}})
    in
    Table

    Note 1: I put the comma at the beginning of a line rather than the end; it is easier to spot when one is missing.
    Note 2: Given the small size of the schema table, I didn’t think removing columns was a useful addition to the code, so for simplicity I removed that step. Hopefully I am not missing something important and arcane.

    * https://social.technet.microsoft.com/Forums/ie/en-US/84b93387-8209-457c-9e47-6b574a308ec4/how-to-learn-m-as-a-language-wheres-my-user-guide-and-reference-manual

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: