Forum Discussion
Helper IUnusual Behaviour when handling GMT time stamps
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?
7 Replies
collinsgSolution Sage
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
MattSBThanks 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 not the issues nor the focus of the question.
