Decimal to date

Question

Hi community,&nbsp;I have a problem which is driving me crazy.&nbsp;I have some data coming from SAP, in which one of the columns is a date in this format:&nbsp;16/08/2015 4:00:00 AM. However, when I see the data in power query, it shows as decimal number like this: 2.01902E+13.&nbsp;I have tried everything to changed this to date, because I need to pivot column "Event type" so that I can see the data from this column as headings, but I get all sorts or errors.&nbsp;Result:&nbsp;&nbsp;&nbsp;

jimmy801 · Answer

Hello&nbsp;pcolas&nbsp;&nbsp;the screenshot does not represent the same data source as in your first post. In your first post was an Excelfile, whereas in your last post you are quering SQL and the data in the&nbsp; Transaction start-column does not represent a datetiem value, but I suppose only a YYYY/MM-format, meaning the frist 4 digits is the month and the next two the month. But I can't see any day or time-part.&nbsp;Jimmy

nickyvv · Answer

Hi&nbsp;pcolas,are you sure you are getting errors? Because they don't show up on your screenshot.The columns are also a valid DateTime according to the symbol in front of it.If you mean the&nbsp;null values, that are just values that are not present for that Header handle.&nbsp;Did this help you or did I answer your question?Then please give kudos or mark my post as a solution!My blog: nickyvv.comTwitter: @NickyvV

pcolas · Answer

Hi&nbsp;nickyvv&nbsp;&nbsp;Thank you for your reply.&nbsp;This is just to show the desireable result, but this is not the actual outcome. Do you know what I mean or should I try to explain this in a different way?&nbsp;&nbsp;&nbsp;

nickyvv · Answer

pcolas, please do explain further what your problem is.

jimmy801 · Answer

Hello pcolas&nbsp;&nbsp;please share you M-code. This error is maybe caused from a wrongly applied column transformation or pivoting function (function that doesn't work with data type. Here an approach how you can force a data transformation by culturelet
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLjFU0lGKik8uLS7JzwUyDc30DSz0jQwMTRVMrAwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Header handle" = _t, #"Event type" = _t, #"UTC start" = _t]),
    ToDateTime = Table.TransformColumns
    (
        Quelle,
        {
            {
                "UTC start",
                each DateTime.FromText(_,"it-IT"),
                type datetime
            }
        }
    ),
    Pivot = Table.Pivot(ToDateTime, List.Distinct(ToDateTime[#"Event type"]), "Event type", "UTC start", List.Max)
in
    Pivot&nbsp;Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your queryIf this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)Kudoes are nice tooHave funJimmy&nbsp;&nbsp;

Forum Discussion

Decimal to date

8 Replies

Related content

Decimal Precision

remove decimals

Trouble with decimal commas and decimal dots

Decimal level parameter not working

Decimal Parameters in User Defined functions (UDF)