Hello all.
The problem arises whenever you load datetime or datetimezone types from a dataflow into a Power BI datamart.
For datetimes you can just
- convert them to text before reusing them in a datamart
However, for datetimezones to work correctly it is more complicated because the SQL server behind the datamart does not interprete them correctly even if they are text type.
Reason is the "+/-[HH:MMDelaytoGMT]" and possibly "T" letter to indicate the timezone format.
For datetimezones, you need to change the format into datetime format while keeping text type before a successful import into the PBI datamart.
Just converting the type from DTZ to DT will not do (in many cases) because it will add/substract the +/- timezonedelay and thus adapting the timestamp wrongly.
I wrote a function covering that conversion sufficiently for german timezone format.
It assumes a valid table and a string column list as parameters and assumes the columns are of type text.
Feel free to use and adapt it (e.g.making it more generic for different TZs).
Function TransformSQLDTZ () :
let
Quelle = (Tab as table, cols as list) => let
Quelle1 = Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Tab,"+01:00","",Replacer.ReplaceText,cols),"T"," ",Replacer.ReplaceText,cols),"+02:00","",Replacer.ReplaceText,cols)
in
Quelle1
in
Quelle
Implementation (query code snippet, copy to query code, adapt "StepBeforeDateConversion" to your previous step name) :
ChangeDatestoText = Table.TransformColumnTypes(StepBeforeDateConversion,{{"AnalyticsUpdatedDate", type text}, {"CompletedDate", type text}, {"StartedDate", type text}}),
TransformSQLDTZ = TransformSQLDTZ(ChangeDatestoText, {"AnalyticsUpdatedDate","CompletedDate", "StartedDate"}),
After those amendments you should be able to import datetimes/datetimezones successfully into datamart (as text values).
Please mark this as a solution if applicable.