cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NiugeS
Helper V
Helper V

Convert text dd-mm-yyyy-hh-mm to Date/Time

Hi,

 

A bit new to BI still and running into what is likely an easy fix.


I'm trying to convert a text that is in dd-mm-yyyy-hh-mm into a date / time.  I have tried to split columns by delimiter and characters etc.  I am able to get the date out but can't seem to get date & time.

i.e. 01-01-2021-07-00  to 01/01/2021 07:00

 

Any guidance appreciated.

Thank you,

1 ACCEPTED SOLUTION
darentengmfs
Super User I
Super User I

Hi @NiugeS 

 

Please try using the M code below, where Source would be your source, and replace the Column names to your column name. If you are unable to change them, please use this code in a new query and refer the steps I took to get from dd-mm-yyyy-hh-mm to what you want.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjAyNDXUNjXUNTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 10}, false), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type date}, {"Column1.2", type text}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1.2", each Text.AfterDelimiter(_, "-"), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Text After Delimiter","-",":",Replacer.ReplaceText,{"Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1.2", type time}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.1", type text}, {"Column1.2", type text}}, "en-US"),{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
in
#"Changed Type2"

View solution in original post

3 REPLIES 3
witbi
Helper I
Helper I

Hi @NiugeS 

For an initial format of 01-01-2021-07-00 it needs to be transformed to  01-01-2021 07:00 then you can change type to date/time. In power query there's probably an efficient way to do this by replacing the 3rd "-" with a " " and 4th with a ":"

I'm still learning text functions to do within one column, but you can use below  to separate into two columns and format one as date and the other as time.

Hope it might help.

let
    Source = Excel.Workbook(File.Contents("C:\table.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Initial", type text}, {"Datetime", type datetime}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Initial", Splitter.SplitTextByRepeatedLengths(11), {"Initial.1", "Initial.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Initial.1", type text}, {"Initial.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","-",":",Replacer.ReplaceText,{"Initial.2"}),
    #"Extracted First Characters" = Table.TransformColumns(#"Replaced Value", {{"Initial.1", each Text.Start(_, 10), type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Initial.1", type date}})
in
    #"Changed Type2"

 

aj1973
Community Champion
Community Champion

Hi @NiugeS 

You don't need to split columns. you either covert it in the Desktop of Power bi or transform it in Power Query

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

darentengmfs
Super User I
Super User I

Hi @NiugeS 

 

Please try using the M code below, where Source would be your source, and replace the Column names to your column name. If you are unable to change them, please use this code in a new query and refer the steps I took to get from dd-mm-yyyy-hh-mm to what you want.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUBSIjAyNDXUNjXUNTpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 10}, false), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type date}, {"Column1.2", type text}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1.2", each Text.AfterDelimiter(_, "-"), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Text After Delimiter","-",":",Replacer.ReplaceText,{"Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1.2", type time}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.1", type text}, {"Column1.2", type text}}, "en-US"),{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type datetime}})
in
#"Changed Type2"

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors