Table.FromRows
Builds a table from a list of rows, where each row is itself a list of values.
Table.FromRows(rows as list, optional columns as any, optional type as nullable type) as tableReturns: 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
- rows: a list of rows. Each row is a list of values in braces, and each row must have the same number of values. Required.
- columns: the column names, in braces, in the same order as the values. Optional, but recommended.
- type: a table type that sets the column types. Optional.
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
- Every row must have the same number of values as there are columns, or the table will be wrong.
- The values are not typed by default. Set types after the step, or use the optional type argument.
- Keep the mapping table short and documented. A reviewer should be able to read every line.
- Query folding: a typed-in table has no source to fold to, so steps that join it to a database table usually run in Power Query.
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.