Convert DateTimeOffset to DateTime

[AverageAsker]

Hi, my data source is SQL table containing DateTimeOffset column, I need the date-time stored there to be displayed in the report as a date-time converted by using the time zone's offset stored in the column of another table, how do I do that? Or, to make the question simpler - how do I apply any date time conversion to the date time stored in a table's column while it is displayed in the report?

I am complete novice in Power BI, please give me a direction.

[ImkeF]

Have a look at DateTime.AddZone.

Or the other Date.Time functions in M.

If this doesn't help, please post pictures/examples of your data as it is not so clear to me what your exact requirement is.

[AverageAsker]

Thanks, what/where do I do to insert that code? I am currently using Power BI Desktop, I clicked Get Data and then specified SQL Server, then provided connection info and imported the table I was interested in. My table User consists of few columns:

• CreatedDate (the data in this column stored in UTC.)
• InternalIdentifier (string - unique value per row)
• Type (contains few possible values of type TINYINT)

I want to see a chart(s) showing users' distribution in time (taking Type into accoun) where the Time will be converted to a certain offset.

In my Power BI Desktop I saw the table (User) I imported and its fields, so I did right click over CreatedDate but could not find there anything enabling me to put any code/script which would change the formatting.

Right click over CreatedDate column

Can you briefly explain and/or point me to the documentation which would show where is that "connection" point enabling me to add code/script so it is associated with the DB columns displayed in the report?

One more connected question - am I supposed to use Power Query only or it is possible to use .NET languages as well?

[ImkeF]

So we only have one table: user. Right?

Where does that offset-value come from?

Another column or is it a fixed value or some conditional values?

Pictures of data or data model would help!

Before: ...

After: ...

[AverageAsker]

The table User contains multiple rows which need to be displayed in a chart.

The offset is stored in another table [Settings] in the column TimeZoneOffset

What I would like to know is the place where I could insert Power Query to transform my data, can you help with that?

[ImkeF]

In the query editor.

Edit your first table and "lookup" the data from your settings table by merging it.

Then add a custom column which add/subtracts the values.

[AverageAsker]

Thank you, what is Power Query equivalent to .NET TimeZoneInfo.FindSystemTimeZoneById ? My Settings table stores Time Zone info by using standard IDs, like Pacific Standard Time and I need to convert values in my DateTimeOffset column to the time zone stored in the Settings table, this is how I do that in C#:

TimeSpan timeZoneOffsetSpan = TimeZoneInfo.FindSystemTimeZoneById(settings.TimeZone).GetUtcOffset(UserCreatedDateTime);

This gives me a difference between the time in the time zone stored in my Settings table and UTC for UserCreatedDateTime. And I am executing that line of the code just once for all rows in my User table because the purpose of the code above is to find out current offset taking into account such timezone's features like DayLightSaving.

And now I can simply add that offset (which could be positive or negative) against every value in my User.CreateDateTime column, in C# I am doing that by using DateTimeOffset.ToOffset:

DateTimeOffset convertedOffset = UserCreatedDateTime.ToOffset(timeZoneOffsetSpan)

So I need to be able to do the same conversions in my Power BI report but I couldnot find any Power Query function which can do the same what TimeZoneInfo.FindSystemTimeZoneById does, if that function existed it would cover my first line of C# code and then I would need to find an equivalent to TimeZoneInfo.FindSystemTimeZoneById. DateTimeZone.SwitchZone is probably what I need but I first need to know the offset corresponding to my time zone and then I would be able to supply that offset as 2nd and 3rd parameters to that function.

So, to finalize, I need Power BI analog for TimeZoneInfo.FindSystemTimeZoneById

[ImkeF]

Yes, DateTime.SwitchZone does that offset.

Also cannot spot anything similar to the other function. It looks as if it takes in a text that represents the timezone and then converts it to a timezone value. But that’s actually not what you need at the end: As that’s only the offset-value.

So how about creating a lookuptable instead where the offset-values for all your possible text-values (time zones) are stored? As this wouldn’t change, you just fetch your offset-value from there then.

[AverageAsker]

As the very worst case I would likely do something similar but I would not want to do that because it will not be just key-value pairs, it has to be something smarter which will take into account Day Light Savings Time for specific timezone which eventually means that there are multiple offsets per any time zone, so, that sounds like not just lookup but lookup + logic and if I don't implement that logic my reports would be wrong at least 2 times per year.

I could not beleive that such basic functionality is not available in Power BI, these are just fundamentals for any live report (which Power BI claims to be) to have true support for time zones (Offset can be determined by knowing a time zone but but time zone can not be determined if just offset is known because there are multiple offsets per any time zone).

Is that possible to use .NET language while shaping a data for Power BI ?

[ImkeF]

Not to my knowledge, but you can use R in Power BI.

But I definitely recommend that you post this question at stackoverflow. There the MS guys are answering the PQ questions. They might know more about it.

[AverageAsker]

I posted in that forum but there are no replies.

So, to close on this: I ended up doing my conversations in SQL due to "Power" BI / "Power" Query limitations

[ImkeF]

That's a pity.

But as Microsoft is adding new features month by month with often stem from users requests: How about posting the missing functions in the Ideas section here: https://ideas.powerbi.com/forums/265200-power-bi?