Archive for June, 2014

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!

Categories: Power Query Tags: , , ,