Forum Discussion

pcolas's avatar
pcolas
Frequent Visitor
6 years ago

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 th...
Need Help
pcolas's avatar
pcolas
Frequent Visitor
6 years ago

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's avatar
Jimmy801
Community Champion
6 years ago

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's avatar
    pcolas
    Frequent Visitor
    6 years ago

    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's avatar
      Jimmy801
      Community Champion
      6 years ago

      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