PowerQuery cheat sheet

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

http://needfordata.ru/blog/

What I would add as well is a Power Query custom functions library – https://github.com/tycho01/pquery

Download cheat sheet in docx or PDF.

Advertisements

5 thoughts on “PowerQuery cheat sheet

  1. Maxim Zelensky 2017-04-03 / 21:45

    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

    • Ivan Bondarenko 2017-04-03 / 23:40

      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.

      • Maxim Zelensky 2017-04-04 / 00:39

        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

        • Ivan Bondarenko 2017-04-04 / 06:52

          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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s