Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi friends, how are you guys? I'm very new to all this DAX, Power BI, Power Query etc.
I just start using Power Query and Power Pivot before I go to Power BI.
I need help because I cant find a solution to this. I am trying to get a dynamic data for the last 6 months and trying to make Power Query to get it but I just dont know how to. Tried a couple of code I found here but none worked.
Code is (in Portuguese, sorry):
let Fonte = Access.Database(File.Contents("C:\Users\DB.accdb"), [CreateNavigationProperties=true]), _BASE_LEVES = Fonte{[Schema="",Item="BASE_LEVES"]}[Data], #"Outras Colunas Removidas" = Table.SelectColumns(_BASE_LEVES,{"NOME", "CTT", "PAGTO", "DUTIL"}), #"Linhas Filtradas" = Table.SelectRows(#"Outras Colunas Removidas", each [PAGTO] > #datetime(2017, 9, 22, 0, 0, 0)) in #"Linhas Filtradas"
So I guess I need to change that
each [PAGTO] > #datetime(2017, 9, 22, 0, 0, 0)
(PAGTO is the date table)
Thanks for reading,
Solved! Go to Solution.
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
Proud to be a Super User!
Hi @fabiomg777,
Is the 6 months based on the current date?
My approach in this case would be to dynamically generate my start date which i can use to filter my data. The script would be something like this (I don't have Power BI right now so I might have missed something causing a formula error):
=List.Min(List.FirstN( List.Distinct( List.Sort( List.Transform( List.Dates(DateTime.Date(DateTime.LocalNow), 10, #duration(28,0,0,0)), Date.StartOfMonth ), Order.Descending ) ), 6 ) )
I would name this query StartDate and would use it the way i would a date parameter. In your case, your row filter script would now change to:
each [PAGTO] >= StartDate
Or if you need it in datetime type, you may use:
each [PAGTO] >= DateTime.From(StartDate)
Proud to be a Super User!
Hi Danextian, thank you very much. I really appreciate that.
I put the code that way:
let StartDate = List.Min(List.FirstN( List.Distinct( List.Sort( List.Transform( List.Dates(DateTime.Date(DateTime.LocalNow), 10, #duration(28,0,0,0)), Date.StartOfMonth ), Order.Descending ) ), 6 ) ), Fonte = Access.Database(File.Contents("db.accdb"), [CreateNavigationProperties=true]), _BASE_LEVES = Fonte{[Schema="",Item="BASE_LEVES"]}[Data], #"Outras Colunas Removidas" = Table.SelectColumns(_BASE_LEVES,{"NOME", "CTT", "PAGTO", "DUTIL"}), #"Linhas Filtradas" = Table.SelectRows(#"Outras Colunas Removidas", each [PAGTO] >= DateTime.From(StartDate) ) in #"Linhas Filtradas"
But then I got the error
Expression.Error: The DateTime.Time function expects an input of type DateTime or DateTimeZone.
Details:
Function
PAGTO seems to be Date/time
Thanks again for your help
I missed this part ().
Instead of DateTime.LocalNow, it should have been DateTime.LocalNow() which is the Excel's Now() equivalent in M.
Proud to be a Super User!
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
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
Proud to be a Super User!
Nice! 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!
I realized while browsing the MSDN site for M that there is this Date.AddMonths function which is a more elegant wait of adding to or subtracting a number of months from a particular date.
Proud to be a Super User!
Proud to be a Super User!
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 EearliestMonth
If you need to get the actual date 6 months ago, please let me know.
Proud to be a Super User!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |