UTC Time Format from API call

%3CLINGO-SUB%20id%3D%22lingo-sub-1878246%22%20slang%3D%22en-US%22%3EUTC%20Time%20Format%20from%20API%20call%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1878246%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20configured%20a%20source%20in%20Power%20Query%20Editor%20to%20import%20data%20in%20which%20the%20times%20are%20in%20UTC%20milliseconds.%20See%20sample%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22catremor_0-1605128702401.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232993i579C40F7B287C8A9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22catremor_0-1605128702401.png%22%20alt%3D%22catremor_0-1605128702401.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20results%20in%20Power%20Query%20converting%20this%20to%20scientific%20notation.%20See%20sample%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22catremor_1-1605128906285.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F232995i2DF7E59639DCD326%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22catremor_1-1605128906285.png%22%20alt%3D%22catremor_1-1605128906285.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20attempted%20creating%20a%20Custom%20Column%20using%20various%20formulas%20that%20I%20have%20found%20but%20they%20have%20only%20resulted%20in%20'Error'%20messages.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20prevent%20this%20conversion%20to%20scientific%20notation%20during%20import%3F%20Or%2C%20once%20imported%2C%20a%20formula%20that%20can%20be%20used%20to%20convert%20this%20to%20a%20readable%20date%20format%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1878246%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1882601%22%20slang%3D%22en-US%22%3ERe%3A%20UTC%20Time%20Format%20from%20API%20call%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1882601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F614675%22%20target%3D%22_blank%22%3E%40catremor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20convert%20from%20Unix%20time%2C%20which%20is%20number%20of%20milliseconds%20(in%20your%20case%20%2C%20could%20be%20seconds)%20from%20start%20of%2001%20Jan%201700%20UTC%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20366px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233279iFDA22BA691DAE902%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eto%20common%20one%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20369px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233280i1278899D4ED0B7FF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20use%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20ReplaceStart%20%3D%20Table.ReplaceValue(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20each%20%5BStart%5D%2C%0A%20%20%20%20%20%20%20%20each%20%23datetime(1970%2C%201%2C%201%2C%200%2C%200%2C%200)%20%2B%20%23duration(0%2C%200%2C%200%2C%20%5BStart%5D%2F1000)%2C%0A%20%20%20%20%20%20%20%20Replacer.ReplaceValue%2C%7B%22Start%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20ReplaceEnd%20%3D%20Table.ReplaceValue(%0A%20%20%20%20%20%20%20%20ReplaceStart%2C%0A%20%20%20%20%20%20%20%20each%20%5BEnd%5D%2C%0A%20%20%20%20%20%20%20%20each%20%23datetime(1970%2C%201%2C%201%2C%200%2C%200%2C%200)%20%2B%20%23duration(0%2C%200%2C%200%2C%20%5BEnd%5D%2F1000)%2C%0A%20%20%20%20%20%20%20%20Replacer.ReplaceValue%2C%7B%22End%22%7D%0A%20%20%20%20)%0A%0Ain%0A%20%20%20%20ReplaceEnd%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3BTo%20adjust%20time%20zone%20use%20second%20parameter%20in%20%23duration()%2C%20e.g.%20-3%20for%20UTC-3%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Senior Member

I have configured a source in Power Query Editor to import data in which the times are in UTC milliseconds. See sample below.

catremor_0-1605128702401.png

This results in Power Query converting this to scientific notation. See sample below.

catremor_1-1605128906285.png

I have attempted creating a Custom Column using various formulas that I have found but they have only resulted in 'Error' messages. 

 

Is there a way to prevent this conversion to scientific notation during import? Or, once imported, a formula that can be used to convert this to a readable date format?

1 Reply
Highlighted

@catremor 

To convert from Unix time, which is number of milliseconds (in your case , could be seconds) from start of 01 Jan 1700 UTC

image.png

to common one

image.png

you may use something like

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplaceStart = Table.ReplaceValue(
        Source,
        each [Start],
        each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Start]/1000),
        Replacer.ReplaceValue,{"Start"}
    ),
    ReplaceEnd = Table.ReplaceValue(
        ReplaceStart,
        each [End],
        each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [End]/1000),
        Replacer.ReplaceValue,{"End"}
    )

in
    ReplaceEnd

 To adjust time zone use second parameter in #duration(), e.g. -3 for UTC-3