Unusual Behaviour when handling GMT time stamps

[MattSB]

When trying to generate a table for controlling views in a powerbi report I came across some unusal behaviour. And not sure if its a bug.

You can see the table displays a different value to the record value at the bottom. Additionally the conversion to type date doesn't work as you would normally expect but if added as a seperate step it all works fine 

You will see the error happening in the second to last step. Im not sure if its related to the time offsett? 

//https://gorilla.bi/power-query/last-refresh-datetime/
//Modified for UK

let
  UTC_DateTimeZone = DateTimeZone.FixedUtcNow(), 
  UTC_Date         = Date.From(UTC_DateTimeZone), 
  StartSummerTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday), 
  StartWinterTime  = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday), 
  UTC_Offset       = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 1 else 0, 
  GMT_Timezone     =  DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset),
  ConvertToTable = #table({"ReportLastRefreshed"}, {{ GMT_Timezone}}),
  ShowingIncorrect = Table.AddColumn(ConvertToTable, "CurrentMonth", each Date.EndOfMonth(  [ReportLastRefreshed]  ) , type date),
  ShowingCorrect = Table.TransformColumnTypes(ShowingIncorrect,{{"CurrentMonth", type date}})
in
    ShowingCorrect

Any thoughts?  

[watkinnc]

Yes, it actually is, MattSB.

--Nate

[collinsg]

Good day MattSB,

Just to add some background to what you are seeing...the smallest interval of time in Power Query is 100ns. The day is divided into blocks, the last block starting 100ns before the end of the day. The "EndOf..." family of functions return the datetime of the start of the last block of time. This is what you see in the record value at the bottom (with seven "9"s after the decimal). In the "ShowingIncorrect" step Power Query has rounded to a datetime it can display, while retaining the true value "under the bonnet". Once you convert to type date the true value is used and you see the date you expected.

Hope this helps

[MattSB]

Thanks Collinsg 

That makes a lot of sense and I appreciate the background for why I get the two values showing differently between the two views. Howewver it shoudl not be showing as a datetime at all as I had declared it a date in the column. 

Do you happen to know why the explicit assignment of date type in the column. Does not actually convert the value?  You can see the column datatype is date but showing a datetime format?

I verified it by using Table.Schema

[collinsg]

Good day MattSB,

I haven't got an answer "from first principles" but perhaps a clue. If I add a column to a table,

= Table.AddColumn(tbl, "Custom", each 100, type text)

what I notice is the column header shows ABC but the values are right-aligned. The right-alignment suggests they are stored as numbers despite the "type text" (I verified they are stored as numbers by then adding a custom column which added 1 to my column - no error was thrown by the math operation, an error would have been thrown if the 100s had been stored as text).

This is similar to what you are seeing.

When I then explicitly change the column type,

= Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}})

the values become left aligned. This suggests they are now stored as text (again I was able to verify this by adding a column, this time with a text operation).

It seems, then, as if ", type x" is not as strong as TransformColumnTypes - it's as if it only changes the icon at the top of the column. Maybe the ", type x" is taken as a suggestion but Power Query looks at the column values and makes its own assessment based on the values, overriding ", type x" if it makes a different assessment.

It would be very interesting to know the answer.

[MattSB]

Thats very interesting and got me thinking , as I know that Gen 2 Dataflows allows you explicitly declare in column data types in the "Custom columns" GUI.

Making the same query in fabric reveals two interesting differnences 

The appended ", type x" pattern is not used instead its 

Table.TransformColumnTypes(Table.AddColumn(Source, "ColumnName", each "DO SOMETHING HERE" ), {{"ColumnName", type x }})

 Additonally the Record and the visual preview table shows the same value so it looks like the rounding for preview is different in the new dataflows. 

The key takeaway is your insight that appending ", type x" is clearly not best practice and we should really wrap with TransformColumnTypes() instead. 

[watkinnc]

Try making ReportLastRefreshed a proper DateTime value--it's typ any before you try to convert it.

--Nate

[MattSB]

Thats not the issues nor the focus of the question.