PowerQuery Quick Reference Guide

Command

Description

Syntax

Examples

columns

Selects, renames, reorders and/or computes new fields.

filter

Discards non-matching records. Follows DataSet search query format.

| filter filterExpression

| filter status == 502

group

Groups records to compute aggregate statistics

| group function(expression), function2(expression2), … by expression3, expression4, …

| group total = count(), errors = count(status >= 500 && status <= 599) by uriPath

join

Executes two or more subqueries and merges their corresponding results based on a matching criteria

| join name = (query), name2 = (query2), … on name.field = name2.field2, …

| join a = (status = 200 | columns tag), b = (numBytes > 1_000_000 | columns tag) on tag

let

Defines one or more new columns.

| let field=expression, field2=expression2, …

| let isError = (status >= 500 && status <= 599)

limit

Caps records displayed or subsequently processed.

| limit [nnn]

| limit 10

lookup

Retrieves values from a lookup table.

| lookup field=columnName, … from "tableName" by columnName=expression, …

| lookup name from "users" by id=requestUserId

parse

Extracts new fields from raw log message, or from a field, if specified. Similar in function to format statement, but some capabilities not available.

| parse [format] from fieldName

| parse "processed $size$KB in $time$ seconds" from summary

| parse "image conversion processed $size{regex=\d+}$"

sort

Defines record display order. Order defines precedence, ascending is default, - defines descending.

| sort [+||-]field, [+||-]field2, …

| sort -error_rate

transpose

Removes a column from the table and creates a new column for each of its values.

| transpose field

| transpose serverHost

union

Executes two or more subqueries, and merges all of their results.

| union (query), (query)

| union (status = 404 | columns tag), (severity > 3 | columns tag)

//

Comments.

// comment

// two slashes indicate a comment, extending until the end of the line

Command

Description

Syntax

Examples

count

Number of records.

number of records in group: count()
number of records for true: count(expression)

| group error_rate = count(status >= 500 status <= 599) / count()

sum

Sum of all inputs.

sum(expression)

| group sumBytes = sum(bytes)

avg

Average of all inputs.

avg(expression)

| group avgBytes = avg(bytes)

min

The smallest input.

min(expression)

| group minBytes = min(bytes)

max

The largest input.

max(expression)

| group maxBytes = max(bytes)

median

The median input.

median(expression)

| group medianBytes = median(bytes)

pct

Percentile from 0-100 of inputs.

pct(NN, expression)

| group medianBytes = pct(50, bytes)

p10,p50,p90,p95,p99,999

Percentile of inputs.

pNNN(expression)

| group medianBytes = p50(bytes)

stddev

The standard deviation of inputs.

stddev(expression)

| group sd = stddev(bytes)

estimate_distinct

Estimates number of distinct values (uses HyperLogLog algorithm configured for 1.3% median error).

estimate_distinct(expression)

| group uniqueVisitors = estimate_distinct(ip)

first

The  first input.

first(expression)

| group firstVisitor = first(ip)

last

The last input.

last(expression)

| group lastVisitor = last(ip)

oldest

Input with oldest timestamp.

oldest(expression)

| group firstVisitor = oldest(ip)

newest

Input with newest timestamp.

newest(expression)

| group lastVisitor = newest(ip)

any

Arbitrarily chosen input.

any(expression)

| group randomVisitor = any(ip)

Command

Description

Syntax

Examples

len

Number of characters.

len(x)

| let example = len(“DataSet”)

lower

All letters changed to lowercase.

lower(x)

| let example = lower(“DataSet”)

upper

All letters changed to uppercase.

upper(x)

| let example = upper(“DataSet”)

ltrim

Remove leading characters.

trim whitespace: ltrim(x)
trim characters in y: ltrim(x,y)

| let example = ltrim("DataSet", "Data")

rtrim

Remove trailing characters.

trim whitespace: rtrim(x)
trim y characters in y: rtrim(x,y)

| let example = rtrim("DataSet", "Set")

trim

Remove leading and trailing characters.

trim whitespace: trim(x)
trim characters in y: trim(x,y)

| let example = trim("_DataSet_", "_")

substr

A copy with characters removed.

remove first y characters: substr(x,y)
remove y characters, starting at z: substr(x,y,z)

| let example = substr("DataSet", 4)
| let example = substr("DataSet", 4, 3)

replace

x, with all matches for y replaced by z.

replace(x,y,z)

| let example = replace("I <3 Scalyr", "Scalyr", "DataSet")

isempty

True if null or an empty string.

isempty(x)

| let example = isempty("")

isblank

True if null, an empty string, or contains only whitespace.

isblank(x)

| let example = isblank(" ")

string

Cast as string.

string(x)

| let example = string(12345)

format

Generate a formatted string, inserting values as specified with additional options.

integer: %[width]d
numeric: %[width][.precision]f
string: %[width][.precision]s

Optional formatting flags:

pad on right: %-
show positive numbers with plus: %+
show positive numbers with space: %⎵
pad with zeros: %0
thousands separator: %,
show negatives in parentheses: %(

| let description = format("Processed %,d orders for %s in %+,10.8f seconds.", 1000, "customer", .002)

Command

Description

Syntax

Example

abs

Absolute value of x.

abs(x)

| let example = abs(-1)

ceiling

x, rounded up to an integer.

ceiling(x)

| let example = ceiling(.9)

floor

x, rounded down to an integer.

floor(x)

| let example = floor(.9)

min

The smaller of x and y.

min(x,y)

| let example = min(1,2)

max

The larger of x and y.

max(x,y)

| let example = max(1,2)

sqrt

The square root of x.

sqrt(x)

| let example = sqrt(9)

exp

Standard exponential function, e^x.

exp(x)

| let example = exp(2)

ln

Natural (base-e) logarithm of x.

ln(x)

| let example = ln(10)

log

Logarithm of x.

base-10: log(x)
base y: log(x,y)

| let example = log(10)
| let example = log(100, 10)

pow

X to the power of y, or x^y.

pow(x,y)

| let example = pow(2, 3)

Command

Description

Syntax

Example

running_sum

Sum the input expression for all rows up to and  including current.

running_sum(expression)

| let thruput = running_sum(bytes)

running_count

1 for the first row, 2 for the second row, etc.

running_count()

| let sessionCount = running_count()

overall_sum

Sum the input expression across all rows.

overall_sum(expression)

| let overallThruput = overall_sum(bytes)

overall_count

Total number of rows.

overall_count()

| let eventCount = overall_count()

overall_min

Smallest value of the input expression in any row.

overall_min(expression)

| let smallestSale = overall_min(price)

overall_max

Largest value of input expression in any row.

overall_max(expression)

| let largestSale = overall_max(price)

overall_avg

Average value of  input expression across all rows.

overall_avg(expression)

| let avgSale = overall_avg(price)

percent_of_total

The input expression, as a percentage of the sum of that expression across all rows.

percent_of_total(expression)

| let salesPercentage = percent_of_total(price)

running_percent

A running total of percent_of_total(). For instance, if the first three rows add up to 25% of the total in all rows, then in the third row this will be 25.

running_percent(expression)

| let salesPercentage = running_percent(price)

Command

Description

Syntax

Example

net_ip

True if x is a valid IP address.

net_ip(x)

| let example = net_ip("1.1.1.1")

net_ipv4

True if valid IPv4 address.

net_ipv4(x)

| let example = net_ipv4("1.1.1.1")

net_ipv6

True if valid IPv6 address.

net_ipv6(x)

| let example = net_ipv6("2001:0db8:85a3:0000:0000:8a2e:0370:7334")

net_ipsubnet

True if x is within y subnet expression.

net_ipsubnet(x,y)

| let example = net_ipsubnet("1.1.1.1", "1.0.0.0/8")

net_private

True if x is a valid IPV4 or IPV6 private address.

net_private(x)

| let example = net_private("192.168.1.1")

net_rfc1918

True if x is a valid IPV4 private address.

net_rfc1918(x)

| let example = net_rfc1918("192.168.1.1")

net_rfc4193

True if x is a valid IPV6 private address.

net_rfc4193(x)

| let example = net_rfc4193("fd00::")

Command

Description

Syntax

Example

timebucket

Use for dividing time range into multiple buckets, truncates time value to the beginning of a bucket.

auto select bucket size: timebucket()
divide into units (either quantity of buckets or size of each bucket): timebucket(unit)



specified time and unit: timebucket(timestamp, unit)

units: days, hours, minutes, seconds, or a number 1-500

timeMs=* page='home'
| group count(), mean(timeMs), p90(timeMs) by timestamp=timebucket('5minutes')

querystart

Beginning of the query time span.

epoch nanoseconds: querystart()
defined unit: querystart(unit)

| let errors.timestamp = querystart()

queryend

End of the query time span.

epoch nanoseconds: queryend()
defined unit: queryend(unit)

| let investigation.timestamp = queryend()

queryspan

The length of the time span for the current query.

nanoseconds: queryspan()
defined unit: queryspan(unit)

message contains "error"

| group eventsPerMinute = count() / queryspan("minutes") by serverHost

| sort -eventsPerMinute