Forum Discussion
Power Query to get data from the last 6 months
Here's the query if you want to get the actual date six months ago from today:
let //returns the current time Source = DateTime.LocalNow(), //extracts the date component of the current time CurrentDate = DateTime.Date( Source ), /*generates a list of dates starting at current date of 10 rows with an increment of negative 28 days, returns earlier dates starting current date sinc interval is negative #duration(d, h, m, s) */ GenerateDates = List.Dates( CurrentDate, 10, - #duration( 28, 0, 0, 0 ) ), //extracts start of month from the list of dates GetMonthStart = List.Transform( GenerateDates, Date.StartOfMonth ), //removes duplicate date values DistinctDates = List.Distinct( GetMonthStart ), //returns just the first 6 rows Last6Months = List.FirstN( DistinctDates, 6 ), //returns the earliest month from the list EearliestMonthDay = List.Min( Last6Months ), //returns the day of the current month CurrentMonthDay = Date.Day( CurrentDate ), //returns the month number 6 months ago..1 for jan, 2 for feb... MonthNumber6MonthsAgo = Date.Month( EearliestMonthDay ), //returns the yer 6 months ago Year6MonthsAgo = Date.Year( EearliestMonthDay ), //returns the end of month day 6 months ago EndOfMonthDay6MonthsAgo = Date.Day( Date.EndOfMonth( EearliestMonthDay ) ), /*finally, returns the date 6 months ago if your requirement is to start the filter one day after the the date 6 months ago just add #duration(1, 0, 0, 0 ) after the closing parenthesis of this variable or you just can just > instead of >= in your date filter */ //also checks if the current month day is greater than the 6 months ago day and returns whichever is lesser Date6MonthsAgo = #date(Year6MonthsAgo , MonthNumber6MonthsAgo, if CurrentMonthDay > EndOfMonthDay6MonthsAgo then EndOfMonthDay6MonthsAgo else CurrentMonthDay ) in Date6MonthsAgo
Super UserI missed this part ().
Instead of DateTime.LocalNow, it should have been DateTime.LocalNow() which is the Excel's Now() equivalent in M.
Thanks Dane!
I got no errors now, but no data is loading:
I went to explore a little bit and clicked on view native query, it is showing the following:
select [_].[NOME],
[_].[CTT],
[_].[PAGTO],
[_].[DUTIL]
from
(
select [NOME],
[CTT],
[PAGTO],
[DUTIL]
from [BASE_LEVES] as [$Table]
) as [_]
where [_].[PAGTO] >= #2018-06-01 00:00:00#I kinda know what is going on but dont know how to fix it xD
But I am happy that I am learning
Thanks again
- danextianI'll go check later when i have my power bi desktop. That query should be returning a past date, not a future one.
- danextian
Hi fabiomg777,
To easily understand, I split the script into several steps instead of nesting them all in one. Please enter this script in a separate query and name StartDate. This will return the start of month 6 months ago starting today. Since we are in March, the script will return Oct 1, 2017.
let //returns the current time Source = DateTime.LocalNow(), //extracts the date component of the current time CurrentDate = DateTime.Date( Source ), /*generates a list of dates starting at current date of 10 rows with an increment of negative 28 days, returns earlier dates starting current date sinc interval is negative #duration(d, h, m, s) */ GenerateDates = List.Dates( CurrentDate, 10, - #duration( 28, 0, 0, 0 ) ), //extracts start of month from the list of dates GetMonthStart = List.Transform( GenerateDates, Date.StartOfMonth ), //removes duplicate date values DistinctDates = List.Distinct( GetMonthStart ), //returns just the first 6 rows Last6Months = List.FirstN( DistinctDates, 6 ), //returns the earliest month from the list EearliestMonth = List.Min( Last6Months ) in EearliestMonthIf you need to get the actual date 6 months ago, please let me know.
- danextian
Here's the query if you want to get the actual date six months ago from today:
let //returns the current time Source = DateTime.LocalNow(), //extracts the date component of the current time CurrentDate = DateTime.Date( Source ), /*generates a list of dates starting at current date of 10 rows with an increment of negative 28 days, returns earlier dates starting current date sinc interval is negative #duration(d, h, m, s) */ GenerateDates = List.Dates( CurrentDate, 10, - #duration( 28, 0, 0, 0 ) ), //extracts start of month from the list of dates GetMonthStart = List.Transform( GenerateDates, Date.StartOfMonth ), //removes duplicate date values DistinctDates = List.Distinct( GetMonthStart ), //returns just the first 6 rows Last6Months = List.FirstN( DistinctDates, 6 ), //returns the earliest month from the list EearliestMonthDay = List.Min( Last6Months ), //returns the day of the current month CurrentMonthDay = Date.Day( CurrentDate ), //returns the month number 6 months ago..1 for jan, 2 for feb... MonthNumber6MonthsAgo = Date.Month( EearliestMonthDay ), //returns the yer 6 months ago Year6MonthsAgo = Date.Year( EearliestMonthDay ), //returns the end of month day 6 months ago EndOfMonthDay6MonthsAgo = Date.Day( Date.EndOfMonth( EearliestMonthDay ) ), /*finally, returns the date 6 months ago if your requirement is to start the filter one day after the the date 6 months ago just add #duration(1, 0, 0, 0 ) after the closing parenthesis of this variable or you just can just > instead of >= in your date filter */ //also checks if the current month day is greater than the 6 months ago day and returns whichever is lesser Date6MonthsAgo = #date(Year6MonthsAgo , MonthNumber6MonthsAgo, if CurrentMonthDay > EndOfMonthDay6MonthsAgo then EndOfMonthDay6MonthsAgo else CurrentMonthDay ) in Date6MonthsAgoNice! It worked very well!
I really feel like myself trying to learn VLOOKUP back in 2008 with Power Query, I will study it deeply this year!
You kept me motivated, thanks!
(I will probably post more because it was just the first part of what I am doing xd, I made what I needed in VBA, really long code to write and the possibility of doing it very quickly via Power Query is cool)
Thanks again,
bye!
- danextianThanks for the feedback. I've realized though that there is a minor error in my query. It returns the date 5 months ago as feb is one month ago from mar. The second parameter in FirstN should have been 7.