Decimal to date

[pcolas]

Hi community,

I have a problem which is driving me crazy.

I have some data coming from SAP, in which one of the columns is a date in this format: 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.

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.

Result:

[v-juanli-msft]

Hi @pcolas 

Change the Regional Settings to "English(US)" and check if the datetime column shows correctly.

Or disable the option below:

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nickyvv]

Hi @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 null values, that are just values that are not present for that Header handle.

Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV

[pcolas]

Hi @nickyvv 

Thank you for your reply.

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?

[Jimmy801]

Hello @pcolas 

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 culture

let
    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

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 query

If 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 too

Have fun

Jimmy

[pcolas]

Hello @Jimmy801

Thank you for your reply.

Please see below my M- Code:

let
Source = Sql.Database("unidataedw01.database.windows.net,1433", "uni-edw"),
cph_VW_SHIPMENT_EVENTS = Source{[Schema="cph",Item="VW_SHIPMENT_EVENTS"]}[Data],
#"Removed Columns" = Table.RemoveColumns(cph_VW_SHIPMENT_EVENTS,{"Event Type", "Event Counter","Version Number", "Sort Order", "Transaction End"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Version Type] = "1")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Version Type", "Extract Date"})
in
#"Removed Columns1"

Also below a sample of my data:

[Jimmy801]

Hello @pcolas 

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  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.

Jimmy

[nickyvv]

@pcolas, please do explain further what your problem is.

[pcolas]

Hi @nickyvv 

My data looks like this:

I want to convert column transaction start to date and then make this table as pivot, so that Event description is on the headers.