Reply
Frequent Visitor
Posts: 9
Registered: ‎07-11-2018

A bug in Date.EndOfMonth resulting the start of next month

Hello, anyone experiencing a bug using Date.EndOfMonth in PowerQuery M. 

I was working on a report for some reason I'm not getting the results I needed, after few hours of peeling each part of the report I tested the accuracy of EndOfMonth

 

Here is the sample code I use to test:

 

    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom1", each DateTimeZone.FromText("2011-02-21T12:30:00-08:00")),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each Date.EndOfMonth([Custom1]))

Here is the Actual Result:

2011-03-01 12:00:00 AM -08:00

Here is the Expected Result:

2011-02-28T23:59:59-08:00  

This example is from: https://docs.microsoft.com/en-us/powerquery-m/date-endofmonth

 

Community Support Team
Posts: 2,034
Registered: ‎06-24-2018

Re: A bug in Date.EndOfMonth resulting the start of next month

Hi @justin777,

Based on my test, it could work on my side with below code in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA30jc0UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfMonth([Date]))
in
    #"Added Custom"

1.PNG

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 9
Registered: ‎07-11-2018

Re: A bug in Date.EndOfMonth resulting the start of next month

Hi v-danghe-msft, 

Looks like having it decompress from Binary works just fine but on my usecase of using the method on a DateTime would lead to a wrong output. I incorporated your code to mine: 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA30jc0UYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfMonth([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each DateTimeZone.FromText("2011-02-21T12:30:00-08:00")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom2", each Date.EndOfMonth([Custom1]))
in
    #"Added Custom2"

Still the same outcome: 

 

I realized I'm getting the wrong outcome because I am using the DateTime and the method Date.EndOfMonth doesn't support the data type properly. 

 

So when I change the data type to Date, it now gives the correct End Date.

 

I suggest changing the example in: https://docs.microsoft.com/en-us/powerquery-m/date-endofmonth

Since the example is, which would lead to a wrong outcome as seen in the screenshot above:

dateTime = DateTimeZone.FromText("2011-02-21T12:30:00-08:00");   
Date.EndOfMonth(dateTime) equals 2011-02-28T23:59:59-08:00  

 

Community Support Team
Posts: 2,034
Registered: ‎06-24-2018

Re: A bug in Date.EndOfMonth resulting the start of next month

Hi @justin777 ,

I have asked the PG team for this porblem and I will reply you as soon as I receive any useful information.

 

Regards,

Daniel He

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.