date data type

[Anonymous]

There is a text type Data (date) in Date data type how to convert it to date data type

[ronrsnfld]

The item that is not being "converted" does NOT represent a valid date. There is an error in the data source that should be changed. If you do not have access to the data source, you will need to first convert that text string to a valid date (or a null).

[AntrikshSharma · Accepted solution]

@Anonymous Ideally, I would ensure the source is correct but you can try to add a new column in PQ or you can transform the column in place, create a new query and paste this code in the advanced editor and observe the last 2 steps

let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText ( "i45WMjTSNzbUNzIwMlKK1YlWArKBIhBuLAA=", BinaryEncoding.Base64 ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Date = _t ]
        ),
    ChangedType = Table.TransformColumnTypes ( Source, { { "Date", type text } } ),
    AddColumn = 
        Table.AddColumn (
            ChangedType,
            "Dates Fixed",
            each 
                try Date.FromText ( [Date], "en-IN" ) 
                otherwise Date.FromText ( [Date], "en-US" ),
            type date
        ),
    TransformColumn = 
        Table.TransformColumns (
            ChangedType,
            {
                "Date",
                each 
                    try Date.FromText ( _, "en-IN" ) 
                    otherwise Date.FromText ( _, "en-US" ),
                type date
            },
            null,
            MissingField.Ignore
        )
in
    TransformColumn