Developing queries for Power BI and Power Query I had to look into documentation or check my previous solutions from time to time in order to get answers to same questions again and again. So, I ended up with a creation of cheat sheet for myself. Couldn’t wait more for cheat sheet from Gil Raviv (know, he plans to make it, stay tuned).
My version is not nicely formatted as DAX Reference Card from PowerPivotPro, but still helpful. It helps me with rarely used symbols and data types, and vice versa, frequently used pieces of M code. E.g. Carriage Return symbol in Power Query, get Excel cell value, or work with datetime and duration types etc.
You may download my version of cheat sheet in docx and PDF, or just bookmark this page. Be careful when copy samples with quotes from web page. I couldn’t force wordpress to show ” instead of “ ”. Copy from PDF or docx instead.
Data Types
| Kind | Literal | Comment |
| Null | null | Empty value, void
1 * null = null // be careful! |
| Logical | true / false | |
| Number | 0 1 -1 1.5 2.3e-5, 0xff | Whole / decimal number, number in hex |
| Time | #time(9, 15, 0) | #time( hour, minute, second )
#time(24,0,0) = #time(0,0,0) If hour is 24, then minute and second must be 0 0 ≤ hour ≤ 24, 0 ≤ minute ≤ 59, 0 ≤ second ≤ 59 |
| Date | #date(2013, 2, 26) | #date( year, month, day) |
| DateTime | #datetime(2013, 2, 26, 9, 15, 0) | #datetime( year, month, day, hour, minute, second ) |
| DateTimeZone | #datetimezone(2013, 2, 26, 9, 15, 0, 9, 0) | #datetimezone( year, month, day, hour, minute, second, offset-hours, offset-minutes )
0 ≤ year ≤ 9999, 0 ≤ month ≤ 12, 1 ≤ day ≤ 31 0 ≤ hour ≤ 23, 0 ≤ minute ≤ 59, 0 ≤ second ≤ 59 -14 ≤ offset-hours + offset-minutes / 60 ≤ 14 |
| Duration | #duration( 0, 1, 30, 0) | #duration( days, hours, minutes, seconds ) |
| Text | “hello” | Just text in quotes
Special characters =”#(cr,lf)” same as =”#(cr)#(lf)”, string to check =”a#(cr,lf)b” = “a#(tab)b” // a b = “a” & “b””c” // ab”c |
| Binary | #binary(“AQID”) | If you work with binary – you know |
| List | { 1, 2, 3 }, { 1 .. 10 },
{“A”..”Z”, “a”..”z”} |
Comma separated values in curly brackets |
| Record | [ A=1, B=2 ] | Comma separated “Field Name = Value” in square brackets |
| Table | Simple way:
#table( { “X”, “Y” }, { { 1, 2 }, { 3, 4 } } ) Preferable: with specified column types #table( type table [Digit = number, Name = text], { {1,”one”}, {2,”two”}, {3,”three”} } ) |
result:
#table( list of field names, list of lists with values for rows of future table ) #table( { “Field1 Name”, “Field2 Name” }, { { “Field1 Value1”, “Field2 Value1” }, { “Field1 Value2”, “Field2 Value2” }, { “Field1 Value3”, “Field2 Value3” } } ) Empty table: #table( {“A”, “B”}, {} ) |
| Function | (x) => x + 1
in general: (optional Num as nullable number) => Num + 1 |
( arguments ) => some operations.
“nullable” is enough to make argument optional. “optional” is enough to let null be passed to a function. (num as nullable number) =>
let
step1 = if num = null then 0 else num,
step2 = step1 * 2
in
step2
|
| Type | type{ number } // list
type table [A = any, B = text] |
| Operator | Result | x = y | Equal |
| x > y | Greater than | x<>y | Not equal |
| x >= y | Greater than or equal | x or y | Conditional logical OR |
| x < y | Less than | x and y | Conditional logical AND |
| x <= y | Less than or equal | not x | Logical NOT |
PowerQuery code shortcuts
IF / THEN / ELSE
Result = if T>0 then A else B // low case if / then / else, M is case sensitive
TRY / CATCH – error handling
Result = try A/B otherwise 0 // low case “try [some action] otherwise [some action/object]”
Excel cell value (Named Range consisting of one cell)
Result = Excel.CurrentWorkbook(){[Name=”CELLNAME”]}[Content]{0}[Column1]
Rename Columns according to “Renaming Table”
Renamed_Columns = Table.RenameColumns(TARGET, Table.ToColumns(Table.Transpose(RENAMING_TABLE)), MissingField.Ignore),
where RENAMING_TABLE looks like (headers do not matter)
| Old Name | New Name |
| A | B |
| C | D |
Create a table from thin air
For example, when response is null but you want to keep structure of your PowerPivot table
= #table( {“A”, “B”}, {} ) – empty table, simple approach
Or with defined column types
= #table( type table [A = text, B = number], {} ) – empty table
= #table( type table [A = text, B = number], { {“one”, 1}, {“two”, 1} } )
ISNUMBER() analog
= Value.Is(Value.FromText( VALUE ), type number)
Or:
= “sample” is number // false, = 123 is number // true
ISTEXT() analog
= Value.Is(Value.FromText( VALUE ), type text)
Or:
= “sample” is text // true, = 123 is text // false
| Expressions
“Hello World” // a text value 123 // a number 1 + 2 // sum of two numbers {1, 2, 3} // a list of three numbers [ x = 1, y = 2 + 3 ] // a record containing two fields: x and y (x, y) => x + y // a function that computes a sum if 2 > 1 then 2 else 1 // a conditional expression let x = 1 + 1 in x * 2 // a let expression error “A” // error with message “A” |
Recursion
[ Factorial = (n) => if n <= 1 then 1 else n * @Factorial(n – 1), x = Factorial(5) ] // @ is scoping operator |
Operations with date and time in Power Query
Time
#time( hour, minute, second )
| Operator | Left Operand | Right Operand | Meaning |
| x + y | time | duration | Date offset by duration |
| x + y | duration | time | Date offset by duration |
| x – y | time | duration | Date offset by negated duration |
| x – y | time | time | Duration between dates |
| x & y | date | time | Merged datetime |
Date
#date( year, month, day)
| Operator | Left Operand | Right Operand | Meaning |
| x + y | date | duration | Date offset by duration |
| x + y | duration | date | Date offset by duration |
| x – y | date | duration | Date offset by negated duration |
| x – y | date | date | Duration between dates |
| x & y | date | time | Merged datetime |
DateTime
#datetime( year, month, day, hour, minute, second )
| Operator | Left Operand | Right Operand | Meaning |
| x + y | datetime | duration | Datetime offset by duration |
| x + y | duration | datetime | Datetime offset by duration |
| x – y | datetime | duration | Datetime offset by negated duration |
| x – y | datetime | datetime | Duration between datetimes |
Duration
#duration( days, hours, minutes, seconds )
#duration(0, 0, 0, 5.5) // 5.5 seconds
#duration(0, 0, 0, -5.5) // -5.5 seconds
#duration(0, 0, 5, 30) // 5.5 minutes
#duration(0, 0, 5, -30) // 4.5 minutes
#duration(0, 24, 0, 0) // 1 day
#duration(1, 0, 0, 0) // 1 day
| Operator | Left Operand | Right Operand | Meaning |
| x + y | datetime | duration | Datetime offset by duration |
| x + y | duration | datetime | Datetime offset by duration |
| x + y | duration | duration | Sum of durations |
| x – y | datetime | duration | Datetime offset by negated duration |
| x – y | datetime | datetime | Duration between datetimes |
| x – y | duration | duration | Difference of durations |
| x * y | duration | number | N times a duration |
| x * y | number | duration | N times a duration |
| x / y | duration | number | Fraction of a duration |
Main source of info is M language specification: https://msdn.microsoft.com/en-us/library/mt807488.aspx
Recommended blogs
http://datachant.com/ – Gil Raviv (@gilra)
https://blog.crossjoin.co.uk/ – Chris Webb (@Technitrain)
http://exceleratorbi.com.au/ – Matt Allington (@ExceleratorBI)
http://excel-inside.pro/ – Maxim Zelensky (@Hohlick)
http://www.thebiccountant.com/ – Imke Feldman (@TheBiccountant)
https://powerpivotpro.com/ – Rob Collie, Avi Singh and others (@powerpivotpro)
In Russian:
https://www.facebook.com/groups/Excelforever/
http://www.excel-vba.ru/?s=power+query
What I would add as well is a Power Query custom functions library – https://github.com/tycho01/pquery
Hi Ivan! Nice work!
Let me propose some changes:
《“nullable” argument is optional》
– not exactly, IMO. I think it means that value could be of nullable type, e.g. “text or null”, but optional argument is typed as
“optional ARG as (nullable) type”
Maxim
Hi Maxim, thanks! You are right, in general it should be “(optional A as nullable number)” – added to post and will update files soon. I usually used only “nullable” keyword, which from my understanding must be used to allow “null” be passed as argument value. And after, UI shows argument as “optional” on Invoke screen, so I decided it has “optional” meaning :).
After your comment I made a test. “optional” also allow to pass null to a function, so in the end I do not see difference in usage.
“(optional A as number)” and “(A as nullable number)” give same result for me. Maybe someone will show a sample where it is not true.
AFAIK, you can make this:
Fn = (x as number, optional y as number)=>…
A = Fn(5)
But cannot this:
Fn = (x as number, y as nullable number)=>…
A = Fn(5)
Instead you have to pass explicit null as second argument:
A = Fn(5, null)
May be I’m wrong, cannot check now.
I’ve seen that UI shows nullable or “type any” arguments as optional, but it is just confusing
Now it is clear as a day. Woke up with a clear head. “optional” allows to call function without argument, like you show in your sample. When without “optional” keyword, PQ says: Expression.Error: 0 arguments were passed to a function which expects 1.
What is surprising – “(optional num as number)=>” allows to pass null to argument even without usage of “nullable”. Interesting.
Thanks for nice introduction and for all the snippets