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 =>
    Source = 
    SortRows = 
            {{"Position", Order.Ascending}}),
    RemoveColumns = 
            {"Name", "TypeName"}),
    AddCustom = 
            Expression.Identifier([Name]) & " = " & [TypeName]),
    Output = 
        "[" & Text.Combine(AddCustom[TypeNames], ", ") & "]"

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:



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]

4 responses

  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:

    fTableType = (InputTable as table) as type =>
    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)
    // Main body
    ,ExistingTable = #table (type table [Column1 = number, Column2 = text], {})
    ,Table = #table (fTableType(ExistingTable), {{1, “A”}, {2,”B”}})

    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

  2. Pingback: Editing Static Tables in Power BI – Dingbat Data

  3. Pingback: Creating static tables that can be edited via its M code – Dingbat Data

  4. Thanks for this code!
    I adjusted it, so the Table.ExpandTableColumn will keep the assigned column types after expansion

    The table that I’m expanding is stored in a column called #”Removed Other Columns”[FirstRow]

    and from there on the code runs like this:

    TableStructure = let
    InputTable = List.First(#”Removed Other Columns”[FirstRow]),
    Source = Table.Schema(InputTable),
    SortRows = Table.Sort(Source,{{“Position”, Order.Ascending}}),
    RemoveColumns = Table.SelectColumns(SortRows,{“Name”, “Kind”}),
    AddCustom = Table.AddColumn(RemoveColumns, “TypeNames”,
    each Expression.Identifier([Name]) & ” = ” & [Kind]),
    Output =
    “[” & Text.Combine(AddCustom[TypeNames], “, “) & “]”
    #”Expanded FirstRow” = Table.ExpandTableColumn(#”Removed Other Columns”, “FirstRow”, Table.ColumnNames(List.First(#”Removed Other Columns”[FirstRow]))),
    #”Re-apply TableStructure” = let
    TableSchema = Expression.Evaluate(“#table (type table ” & TableStructure &”, {})”),
    Records = Table.ToRecords(#”Expanded FirstRow”),
    Result = Table.InsertRows(TableSchema, 0, Records)
    in Result
    #”Re-apply TableStructure”

Leave a Reply

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

%d bloggers like this: