M · Power Query

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]

5 thoughts on “A Function To Generate The M Code For A Table Type

  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. 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”

  3. This was the missing piece I was looking for to quickly add columns to a table. I’ve come up with 5 different methods to quickly add columns to a table.

    //Named Colmuns, no Data Types
    AddNewColumns = PreviousStep & #table({“Number Column”, “Text Column”, “Date Column”}, {})

    //Name Columns with different Data Types
    AddNewColumns = PreviousStep & #table( type table
    #”Number Column”=number,
    #”Text Column”=text,
    #”Date Column”=date
    ], {})

    //Generic Columns no Data Types
    AddNewColumns = PreviousStep & #table(List.Transform({1..5}, each “Undefined” & Text.From(_)), {})

    //Generic Columns with equivalent Data Types (thanks to this post)
    AddNewColumns = PreviousStep & #table(Expression.Evaluate(“type table [” & Text.Combine(List.Transform({1..5}, each “Undefined” & Text.From(_) & ” = number”), “, “) & “]”), {})

    //Generic Columns using List.Generate with equivalent Data Types
    Add_Multiple_Columns = List.Accumulate({1..5}, PreviousStep, (state, current) => Table.AddColumn(state, “UserDefined” & Text.From(current), each null, type number))

Leave a ReplyCancel reply

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