How to Split Timestamp Field with PowerQuery

If you ever try to split the month, date, hour, minute, and sec directly from the timestamp field using PowerQuery, you may find yourself having no luck doing that. The reason is that the timestamp is a special reserved field in DataSet and it isn't stored as a "string" type.

Fortunately, there are a couple of ways for users to break the timestamp field into smaller chunks:

• Use timebucket to group query results

PowerQuery supports timebucket syntax that converts the timestamp into a more generalized interval such as day, hour, minute, etc. Here is an example query that groups the count of total access log requests and errors by minutes.

dataset = "accesslog"
| group requests = count(), errors = count(status == 404) by timestamp = timebucket("1m") 

This is a very simple solution if you just want to get an overall trend.

• Extract parts of the timestamp from the message field

While the first solution is easy and clean, it does have its limitation. For instance, the timebucket doesn't work in searching for an extended time period (> 500 timebuckets), so if your query needs to cover a longer time period or extract a specific part of the timestamp (ex. date), the parse command should be used instead.

Here is an example of using regex to get the date portion (ex. Jun/1) out of the timestamp from the message field.

$dataset = "accesslog"
| parse ".* \\[$date$\/[\\d:]+ \\+0000\\] .*" from message 
| group requests = count(), errors = count(status == 404) by date
| filter date != null