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}}) // true

Number

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 ]        // true

We 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} // false

We 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: