Downloads
Power Query MTable: build and convertIntermediate

Table.FromRows

Builds a table from a list of rows, where each row is itself a list of values.

Syntax
Table.FromRows(rows as list, optional columns as any, optional type as nullable type) as table

Returns: A new table with one row for each item in the list.

What it does

Table.FromRows builds a table from values you write in the query. Each row is a list in braces, and the names of the columns are given as a second list. It is handy for a small mapping table, such as a set of account type codes, that you do not want in a spreadsheet. For larger tables, load them from their source instead.

Syntax and arguments

Example

A small mapping table turns the account type codes in a ledger into the sign and label that the financial statements use.

let
    TypeMap = Table.FromRows(
        {
            {"A", "Asset", "Balance sheet"},
            {"L", "Liability", "Balance sheet"},
            {"E", "Equity", "Balance sheet"},
            {"R", "Revenue", "Income statement"},
            {"X", "Expense", "Income statement"}
        },
        {"Code", "Type", "Statement"}
    )
in
    TypeMap

Each inner list becomes one row, and the names in the second list become the column headings. The table can then be joined to a ledger on the Code column.

Things to watch

In business terms

A mapping from internal codes to report captions is a rule the finance team needs to see. Writing the mapping in the query makes it visible, versioned with the report, and easy to review.

Related functions

All Power Query M functions · View as Markdown · M basics