Search code examples
exceldatepowerbitransformpowerquery

Power Query transform text to datetime

I'm pretty new to PowerBI and trying to load a huge excel file that contains a datetime column in the following format:

2023-05-26-11.04.58.019027

However, Power Query does not recognize this as a datetime. It looks like I would have to do the transformation by hand. Miliseconds are not of interest here.

The extended editors looks as follows:

let
    Quelle = Excel.Workbook(File.Contents("\\...\Desktop\Juni.xlsx"), null, true),
    #"Zeiten 1Task Juni 2023_Sheet" = Quelle{[Item="Zeiten 1Task Juni 2023",Kind="Sheet"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(#"Zeiten 1Task Juni 2023_Sheet", [PromoteAllScalars=true])
in
    #"Höher gestufte Header"

I did some research and thought about using DateTime.FromText, but I'm not sure how to implement it.

This might be easy, but do you have any tips on this?

Solution

  • In powerquery, Add column ... custom column... with formula

    = DateTime.FromText(Text.Start([MyTextColumnNameGoesHere],19), [Format="yyyy-MM-dd-HH.mm.ss"])

    enter image description here

    Alternate

    = DateTime.FromText(Text.BeforeDelimiter([MyTextColumnNameGoesHere], ".", {0, RelativePosition.FromEnd}), [Format="yyyy-MM-dd-HH.mm.ss"])