The language resembles F#.
The language is case sensitive.
Interesting functions
File.Contents(path as text, optional options as nullable record) as binary
File.Contents return a file's content as
binary. This is needed, for example, to open an
Excel Workbook and transform it to a
table with
Excel.Workbook:
Excel.Workbook(workbook as binary, optional useHeaders as nullable logical, optional delayTypes as nullable logical) as table
Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table
Table.SelectRows seems to kind of the equivalent of the
SQL where clause.
Table.SelectRows(table as table, condition as function) as table
The following line seems to correspond to select * from allRows where colXYZ = 'value ABC':
selectedRows = Table.SelectRows(allRows, each ([colXYZ] = "value ABC")),
Compare with Table.RemoveRows
Table.SelectColumns() / Table.RemoveColumns()
Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table
Table.RemoveColumns(table as table, columns as any, optional missingField as nullable number) as table
Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table
Add a column named added that contains the sum of val one and val two:
sumAdded = Table.AddColumn(inputTable, "added", each [val one] + [val two], type number),
Concatenate two text columns:
concatAdded = Table.AddColumn(inputTable, "concatenated", each Text.Combine({[txt one], " ", [txt two]}), type text),
Table.ReorderColumns(table as table, columnOrder as list, optional missingField as nullable number) as table
Create a table
#table (
columns as any, // Typically a list of column names, but can also be a table type, a number of columns or null.
rows as any // A list where each element is itself a list that corresonds to a row
)
map equivalent
List.Transform(list as list, transform as function) as list
The following snippet doubles every element in the list
List.Transform (
{ 11, 3, 29 },
each 2 * _
)
List.Transform can be used to create lists (rows) from a list. The produced rows can then be returned as a table
let
numbers = {5, 1, 6, 4, 2, 3},
rows = List.Transform(
numbers,
each { _ , _*3 , _*_ }
),
table = #table (
{"num", "times 3", "squared"},
rows
)
in
table
Eval
Expression.Evaluate(document as text, optional environment as nullable record) as any
The following evaluates to 42:
Expression.Evaluate( " 7*6 " )
Passing «symbols» (?) to Expression.Evaluate:
Expression.Evaluate(
"
f ( {11, 22, val_3 })
",
[ f = List.Sum ,
val_3 = 9
]
)
Excel.CurrentWorkbook
Excel.CurrentWorkbook() returns a table that lists the tables of the current
Excel workbook. The returned table has the following two columns:
-
Content (which itself is a table)
-
Name (whose data type is string?)
Excel.CurrentWorkbook() as table
Excel.CurrentWorkbook() can be used to refer to
named ranges to get values for processing:
var = Excel.CurrentWorkbook(){[Name="rangeName"]}[Content]{0}[Column1],
Record.FieldValues
Record.FieldValues gets values (not the names) from a record.
Record.FieldValues(record as record) as list
The following evaluates to {42, "Hello World", "foo, bar, baz"}.
Record.FieldValues([
num = 42,
txt = "Hello World",
val = "foo, bar, baz"
])
Record.FieldValues might be used to in Table.FromList, but I am not sure what its purpose exactly is:
Table.FromList (
{
[ txt = "one" , num = 1 , meta = "foo" ],
[ WHAT = "two" , THE = 2 , HECK = "bar" ], // Note the differing record labels!
[ txt = "three", num = 3 , meta = "baz" ],
[ txt = "four" , num = 4 , meta = "last"]
},
Record.FieldValues, // Function
{"txt", "num", "meta"}
)
Date and time related
DateTime.LocalNow() evaluates to the current date and time on the system.
DateTime.LocalNow() as datetime
When interested in the data part of a date time, DateTime.Date() is the thing to be used
DateTime.Date(dateTime as any) as nullable date
Getting data from the internet
Web.Contents(url as text, optional options as nullable record) as binary
Table.FromList
Table.FromList(
list as list,
optional splitter as nullable function,
optional columns as any,
optional default as any,
optional extraValues as nullable number
)
as table
By default,
Table.FromList assums the given list a list of text that contains commas where the text needs to be split. Thus, it is almost predestined to read
CSV files. The following snippet produes a 4 rows, 3 columns table:
Table.FromList ( { "one,1,foo", "two,2,bar", "three,3,baz", "four,4,last" })
But compare also with Csv.Document().
Table.FromRecords
Table.FromRecords({
[a = 1, b = 2],
[a = 3, b = 4]
}),
Table.PromoteHeaders
Table.PromoteHeaders(table as table, optional options as nullable record) as table
Table.PromoteHeaders is used if a table's first row contains column names:
Table.PromoteHeaders(table as table, optional options as nullable record) as table
Table.Group
Table.Group(
table as table, // Data to be aggregated
key as any, // Non aggregated columns
aggregatedColumns as list, //
optional groupKind as nullable number,
optional comparer as nullable function
)
as table
Change values in a given column
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table
Some candidates for the
replacer function include
-
Replacer.ReplaceValue
-
Replacer.ReplaceText
Table.ReplaceValue can for example be used to replace null values with zeroes, in the following example in the column named foo:
no_nulls = Table.ReplaceValue(chosen_columns, null, 0, Replacer.ReplaceValue, {"foo"})
Select data with SQL with Value.Native
The function
Value.Native allows to select data from a
database with
SQL.
Value.NativeQuery(
target as any ,
query as text,
optional parameters as any ,
optional options as nullable record
) as any
Because native queries may alter
data in the database and are therefore potentially unsafe so that their execution needs to be approved of.
The following example select from an
MS Access database:
let
accdbBinary = File.Contents("P:\ath\to\db.accdb"),
accdb = Access.Database(accdbBinary, [CreateNavigationProperties=true]),
result = Value.NativeQuery(accdb, "select * from country")
in
result
TODO
Create a list of dates
// Found at https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/
let
DateList = List.Dates(#date(2020,1,1), 500, #duration(1,0,0,0)),
AddColumns = List.Transform(
DateList,
each {_, Date.Month(_), Date.Year(_)}
),
DateTable = #table(
type table[Date=date, Month=number, Year=number],
AddColumns
)
in
DateTable
// Found at https://blog.crossjoin.co.uk/2016/06/03/creating-tables-in-power-bipower-query-m-code-using-table/
let
Source = {
Number.From(#date(2015,1,1))..Number.From(#date(2020,12,31))
},
ConvertToTable = Table.TransformColumnTypes(
Table.FromList(
Source,
Splitter.SplitByNothing(),
{"DateKey"},
null,
ExtraValues.Error
),
{ {"DateKey", type date}})
in
ConvertToTable