Reflection with Power Query/M language to get information about tables and columns

For a solution I’m currently working on, I needed to get a list of all the tables of my solution with some information (name, position and type) of their respective columns. The goal was to execute this task on the Power BI Desktop application and pushing the dataset to the service was not an option.

To reach this goal, we’ll need to use a methodology known as reflection (or introspection) where the code is reading and parsing itself.

The first step is to list all the tables of my solution. To execute this step, I’ll use the keyword #sections. This keyword is from the same family than #shared but in place of returning all the native functions and the solution-specific tables, constants and functions, it will return all the sections. A section is equivalent to a namespace in .Net or Java, it’s a kind of container where content (functions, tables, constants) is uniquely named. In the current implementation of Power Query/M language, the content that you’re creating is always placed in the namespace Section1. This navigation is returning a list of records that we’ll turn into a table for easiness.

source = Record.ToTable(#sections[Section1])

As visible in the above screenshot, usage of this function returns the different constants, tables and functions written in my solution. In our case we just need to limit our scope to the tables. We can filter the content of my table based on the type of the value contained in my second column. To achieve this the function Value.Is combined with the token type table is the right choice. I’ll also remove all objects for which the name is starting by an underscore to avoid to work on this table created by this script.

holdTables = Table.SelectRows(
    source
    , each (
        Value.Is([Value], type table) and not Text.StartsWith([Name], "_")
    )
)

To get the list of columns and some information about them, we can use the function Table.Schema. In our case we’re only interested by the column name, position and type and we’ll limit the expansion to these cases.

schemaTable = Table.AddColumn(
    holdTables
    , "Schema"
    , each Table.Schema([Value])
),
expandedSchema = Table.ExpandTableColumn(
    schemaTable
    , "Schema"
    , {"Position", "Name", "TypeName"}
    , {"Schema.Position", "Schema.Name", "Schema.TypeName"}
)

The next step is to perform a bit of clean-up by removing some columns and typing remaining columns. Then, we can sort the table by table name and then by column position.

cleanedTable = Table.TransformColumnTypes(
    Table.RemoveColumns(
        expandedSchema
        ,{"Value"}
    )
    ,{
        {"Schema.Position", Int64.Type}
        , {"Schema.Name", type text}
        , {"Schema.TypeName", type text}
    }
),
sortedTable = Table.Sort(
    cleanedTable
    ,{
        {"Name", Order.Ascending}
        , {"Schema.Position", Order.Ascending}
    }
)

The full code is available here under.

let
    source = Record.ToTable(#sections[Section1]),
    holdTables = Table.SelectRows(source, each (Value.Is([Value], type table) and not Text.StartsWith([Name], "_"))),
    schemaTable = Table.AddColumn(holdTables, "Schema", each Table.Schema([Value])),
    expandedSchema = Table.ExpandTableColumn(schemaTable, "Schema", {"Position", "Name", "TypeName"}, {"Schema.Position", "Schema.Name", "Schema.TypeName"}),
    cleanedTable = Table.TransformColumnTypes(Table.RemoveColumns(expandedSchema,{"Value"}),{{"Schema.Position", Int64.Type}, {"Schema.Name", type text}, {"Schema.TypeName", type text}}),
    sortedTable = Table.Sort(cleanedTable,{{"Name", Order.Ascending}, {"Schema.Position", Order.Ascending}})
in
    sortedTable

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