Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bartus1
Helper I
Helper I

Can I convert this: [Fri May 24 13:58:31 2019] to date/time format?

I have this from external source:

 

Fri May 24 13:58:31 2019

 

Can I convert this to 'normal' datetime in Power BI?

 

Thanks

1 ACCEPTED SOLUTION

Hi,

I used Column by examples in the Query Editor so solve the problem.  Here is the M code generated

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine({Text.Middle([Date], 8, 2), Text.Middle([Date], 3, 5), Text.Middle([Date], 20)}), type text),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom Column", {{"Custom", type date}}, "en-IN"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type with Locale", "Text Between Delimiters", each Text.BetweenDelimiters([Date], " ", " ", 2, 0), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Between Delimiters", "Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}})
in
    #"Renamed Columns1"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

The time stamp is between the date and the year.  Are you sure????


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yep.  I'd nave not asked about it if it were different.

Hi,

I used Column by examples in the Query Editor so solve the problem.  Here is the M code generated

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Added Custom Column" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine({Text.Middle([Date], 8, 2), Text.Middle([Date], 3, 5), Text.Middle([Date], 20)}), type text),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom Column", {{"Custom", type date}}, "en-IN"),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type with Locale", "Text Between Delimiters", each Text.BetweenDelimiters([Date], " ", " ", 2, 0), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text Between Delimiters",{"Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Text Between Delimiters", "Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}})
in
    #"Renamed Columns1"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.