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

[NiugeS]

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,

[witbi]

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]

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

[darentengmfs · Accepted solution]

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"