cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcflurry
Helper I
Helper I

Convert string to date

Hello guys,

 

I have a string column with this:

 

20220307-06:30:06-GMT+0100

 

I'm trying to convert it to a date column without success.

How could it be done?

2 ACCEPTED SOLUTIONS
vojtechsima
Super User
Super User

Hi, @mcflurry 
In Power Query with TimeZone:

vojtechsima_0-1646660276179.png

 

 

 

let
Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),
Time = Time.FromText(Text.BetweenDelimiters([String], "-", "-")),
getTimeZone = Text.AfterDelimiter([String], "T"),
getPlusOrMinusZone = Text.Start(getTimeZone, 1),
GetHourOfZone = Number.FromText( Text.Middle(getTimeZone,1,2)),
GetMinuteOfZone = Number.FromText(Text.End(getTimeZone,2)),
DateTime = DateTime.From(Date & Time),
DateTimeZone = DateTime.AddZone(DateTime,GetHourOfZone,GetMinuteOfZone),
CompleteWithCheck = if getPlusOrMinusZone = "+" then DateTimeZone else DateTime.AddZone(DateTime,-GetHourOfZone,GetMinuteOfZone)

in CompleteWithCheck

 

 

Add New Column like this. "String" is your column with the provided date. Then just Change Type of the new column to "date" or "datetime" or "datetimezone".

Edit:
I enhanced the code to check whether it's "+" or "-".

View solution in original post

Regional Settings is already to an European country.

 

Could you please help me on how to fix it with your code?

I guess it's this line, but i don't have the knowledge to achieve it... 

 

Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),

 

I'm transforming the the original column with unicode format... and maybe it will work.

Yep, fixed that way, thanks.

View solution in original post

4 REPLIES 4
vojtechsima
Super User
Super User

Hi, @mcflurry 
In Power Query with TimeZone:

vojtechsima_0-1646660276179.png

 

 

 

let
Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),
Time = Time.FromText(Text.BetweenDelimiters([String], "-", "-")),
getTimeZone = Text.AfterDelimiter([String], "T"),
getPlusOrMinusZone = Text.Start(getTimeZone, 1),
GetHourOfZone = Number.FromText( Text.Middle(getTimeZone,1,2)),
GetMinuteOfZone = Number.FromText(Text.End(getTimeZone,2)),
DateTime = DateTime.From(Date & Time),
DateTimeZone = DateTime.AddZone(DateTime,GetHourOfZone,GetMinuteOfZone),
CompleteWithCheck = if getPlusOrMinusZone = "+" then DateTimeZone else DateTime.AddZone(DateTime,-GetHourOfZone,GetMinuteOfZone)

in CompleteWithCheck

 

 

Add New Column like this. "String" is your column with the provided date. Then just Change Type of the new column to "date" or "datetime" or "datetimezone".

Edit:
I enhanced the code to check whether it's "+" or "-".

Hello @vojtechsima 

I have a problem with your code.

It looks like it's taking the day as month and the month as day, and imported data from yesterday fails, i've tried with 02/28 as an example:

 

mcflurry_0-1647348385236.png

 

Hi, @mcflurry 
Go to Settings - Regional Settings (current file) and change it to some European country, in my case Czech Republic, or rewrite the script that it first writes month and then day.

Regional Settings is already to an European country.

 

Could you please help me on how to fix it with your code?

I guess it's this line, but i don't have the knowledge to achieve it... 

 

Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),

 

I'm transforming the the original column with unicode format... and maybe it will work.

Yep, fixed that way, thanks.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.