Home > Power Query > New Power Query Feature; Small Detail

New Power Query Feature; Small Detail

There a few enhancements to Power Query that were released this month. See this video from Microsoft Server and Cloud for an overview.

I tested out #3, the additional DateTime filters and noticed some behavior that isn’t explicitly stated nor very intuitive (at least for me anyway). Let’s assume today is 6/28/2014; the following M functions will return the following time ranges:

= Table.SelectRows(ChangedType1, each Date.IsInPreviousNDays([DATE], 250))    returns 10/21/2013 to 6/28/2014
= Table.SelectRows(ChangedType1, each Date.IsInPreviousNWeeks([DATE], 40))    returns 09/15/2013 to 6/28/2014
= Table.SelectRows(ChangedType1, each Date.IsInPreviousNMonths([DATE], 7))    returns 11/01/2013 to 6/28/2014
= Table.SelectRows(ChangedType1, each Date.IsInPreviousNQuarters([DATE], 3))  returns 07/01/2013 to 6/28/2014
= Table.SelectRows(ChangedType1, each Date.IsInPreviousNYears([DATE], 1))        returns 01/01/2013 to 6/28/2014

I would have assumed that these functions would have returned the dates filtered at the date level. But that is not these case, each function returns the first day of that level. For example, if you want the last 3 weeks, you’re going to get all the dates starting from the first day of the week, 3 weeks back, not 21 days back.

Also note that the sub functions in the queries above (like Date.IsInPreviousNWeeks) return only true or false; which is then iterated for “each” row in the ChangedType1 set.

Give it a shot for yourself and Happy Power Querying!

Advertisements
Categories: Power Query Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: