Search notes:
Power Query M formula language: data type table
A table can be thought of a
list of lists, but with additional features such as
column names
designated key-columns (see Table.Keys … ?
Brackets
Brackets allow to select a column from a table and returns it as a list.
let
tbl = #table (
{"col_1", "col_2", "col_3" },
{
{"foo" , 42 , "one" },
{"bar" , 99 , "two" },
{"baz" , 0 , "three" }})
in
tbl[col_3]
Curly brackets
Curly brackets allow to select a row from a table. The returned value is a
record :
The following example extracts the the third row:
let
tbl = #table (
{"col_1", "col_2", "col_3" },
{
{"foo" , 42 , "one" },
{"bar" , 99 , "two" },
{"baz" , 0 , "three" }})
in
tbl{2}
Extracting value of a given row in a named column
Because brackets that are applied on a table return a list, and curly brackets that are applied on a list return an element, the value of the 3rd record, 2nd row can be extracted from a table like so:
let
tbl = #table (
{"col_1", "col_2", "col_3" },
{
{"foo" , 42 , "one" },
{"bar" , 99 , "two" },
{"baz" , 0 , "three" }}
)
in
tbl[col_3]{1}
Similarly, the value of the 3rd row in column col_1 can be extracted like so:
let
tbl = #table (
{"col_1", "col_2", "col_3" },
{
{"foo" , 42 , "one" },
{"bar" , 99 , "two" },
{"baz" , 0 , "three" }})
in
tbl{2}[col_1]
Select * from … where …
Curly brackets can also be used to select a row where a column value equals to a given value, thus mimicking the SQL statement select * from … where …:
let
tbl = #table (
{"col_1", "col_2", "col_3" },
{
{"foo" , 42 , "one" },
{"bar" , 99 , "two" },
{"baz" , 0 , "three" }})
in
tbl{ [col_3="two"] }
