cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pcolas
Frequent Visitor

Decimal to date

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.

 

EVENTS.JPG

Result:

 

Result.JPG

 
 
8 REPLIES 8
Super User I
Super User I

Re: Decimal to date

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.

image.png

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


Blog: nickyvv.com | @NickyvV


pcolas
Frequent Visitor

Re: Decimal to date

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?

 

 

 

Super User I
Super User I

Re: Decimal to date

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


Blog: nickyvv.com | @NickyvV


Super User I
Super User I

Re: Decimal to date

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
Frequent Visitor

Re: Decimal to date

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:

 

Events.JPG

 

pcolas
Frequent Visitor

Re: Decimal to date

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.

 

Events.JPG

Super User I
Super User I

Re: Decimal to date

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

Community Support
Community Support

Re: Decimal to date

Hi @pcolas 

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

Or disable the option below:

Capture2.JPG

 

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.

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

Top Solution Authors
Top Kudoed Authors