Literals in Power Query (M language)
Table
| We can create empty table by specifying number of columns to create. For values we just have to provide empty list. #table(4,{}) |  |
| This is how we create regular table. All the columns will have unspecified type. #table( {"A", "B"}, { {1, 2}, {3, 4} } ) |  |
| We can assign type to table columns. #table(type table [Digit = number, Name = text], {{1,"one"}, {2,"two"}, {3,"three"}} ) |  |
Some information about Tables
| It is possible to make an union of several tables with "&" operator. Orphan columns will be filled with nulls. #table({"A","B"}, {{1,2}}) & #table({"B","C"}, {{3,4}}) |  |
Tables are considered the same if for each column in one table there is equivalent column in another table. Order of columns is not important.
#table({"A","B"},{{1,2}}) = #table({"A","B"},{{1,2}}) // true
#table({"A","B"},{{1,2}}) = #table({"X","Y"},{{1,2}}) // false
#table({"A","B"},{{1,2}}) = #table({"B","A"},{{2,1}}) // trueNumber
| There are three ways to write literal numbers. Regular = -1.5 Scientific = 2.3e-5 Hexadecimal = 0xff |  |
| There are also two special symbols for infinity and for "not a number". Infinity is something we get when we try to divide one and zero. If we try to divide zero and zero, we will get "NaN". "NaN" is the only value that is not equal to itself. Infinity = #infinity NaN = #nan |  |
Type
| With "type" keyword we can create literal types. ListOfNumbers = type { number } TwoNumberFieldsRecordType = type [ X = number, Y = number ] DateTimeType = type datetime TextType = type text |  |
Logic, text and null
| Logic literals are TRUE and FALSE. Text literals are wraped with quotes. Null is a special literal which symbolizes missing data. TRUESymbol = true FALSESymbol = false TextLiteral = "word" NullSymbol = null |  |
Record
| This is how we can write record literal. Record = [ A = 1, B = 2 ] |  |
Some information about records
Records are equal if for each field in one record ther is an equal field in another record. Order of fields is not important.
[ A = 1, B = 2 ] = [ A = 1, B = 2 ] // true
[ B = 2, A = 1 ] = [ A = 1, B = 2 ] // trueWe can make a union of several records. If we have one field in more than one record, then the value from the last record with that field will be the final one. In our example x will be assigned the value 3, and not the value 1.
[ x = 1, y = 2 ] & [ x = 3, z = 4 ] // [ x = 3, y = 2, z = 4 ] List
| List is created by placing comma separated list inside of curly brackets. List = {1, 2, 3} |  |
Some information about lists
Two lists are equal if the have the same elements and position of those elements is the same.
{1, 2} = {1, 2} // true
{2, 1} = {1, 2} // falseWe can concatenate several lists with & operators.
{1, 4} & {2, 3} // list concatenation: {1, 4, 2, 3} Special Signs
| Special signs, that are invisible, can be written by their symbolic presentation. TAB = #(cr) LineFeed = #(lf) CariageReturn = #(tab) I used some unusual unicode brackets in the "Expression" column to prevent typed text to be identify as a special sign. In the "Result" column, we can see that both "cr" and "lf" signs are causing line break. |  |
If we want to type two characters "#(", we have to write them like "#(#)(", because those characters has to be escaped. Two signs "#(cr)#(lf)", when consecutive, can be typed as "#(cr,lf)", the result would be the same.
Unicode Signs
| We can enter any Unicode symbol in Power Query by using their codes. YenSymbol = "#(00A5)" CopyRightSymbol = "#(00A9)" CapitalTheta = "#(0398)" |  |
Binary – list of bytes
| Binary data is actually list of bytes. We can present those bytes with letters, hexadecimal numbers or ordinary numbers. BinaryByLetters = #binary("AQID") BinaryByHexidecimalNumbers = #binary( {0x00, 0x01, 0x02, 0x03} ) BinaryByRegularNumbers = #binary({65, 66, 67}) |  |
Date and time
Date and time can be expressed with these literals.
#time(hour, minute, second)
#date(year, month, day)
#datetime(year, month, day, hour, minute, second)
#datetimezone( year, month, day, hour, minute, second, offset-hours, offset-minutes)
#duration(days as number, hours as number, minutes as number, seconds as number) | For each argument there is a limit on what values that argument could have. 1 ≤ year ≤ 9999 1 ≤ month ≤ 12 1 ≤ day ≤ 31 0 ≤ hour ≤ 23 0 ≤ minute ≤ 59 0 ≤ second ≤ 59 -14 ≤ offset-hours ≤ 14 -59 ≤ offset-minutes ≤ 59 |
Some information about dates and times
| We can concatenate date and time. | #date(2013,02,26) & #time(09,17,00) // #datetime(2013,02,26,09,17,00) |
| We can add duration to date or to time. | #datetime(2010,05,20,0,0,0) + #duration( 8, 0, 0 ) //#datetime(2010,5,20,8,0,0) #time(8,0,0)+#duration(30,5,0,0) //#time(13,0,0) |
| Duration can be multiplied. | #duration(2,1,0,15.1) * 2 // #duration(4, 2, 0, 30.2) |
| Durations can be divided. | #duration(2,0,0,0) / #duration(0,2,0,0) //24 |
| Dates and times can be converted to numbers and from. | Number.From(#datetime(2020, 3, 20, 6, 0, 0)) // 43910.25 Date.From(43910) // #date(2020,3,20) Time.From(0.7575) // #time(18,10,48) Duration.From(2.525) // #duration(2,12,36,0) |
Sample file can be downloaded from here:
. At last, we will see all custom percentiles we have entered (6).
, all other cells will return Error.
.
