Forum Discussion

BradTennent's avatar
BradTennent
Frequent Visitor
3 years ago
Solved

How change Coumns to date type but keep existing text values

Hi,

Hoping for some help,

I have a Training data set that has several columns that combine dates (As date serial) and text.

son in a column representing a course, we can have the following values

Pending, In progress or a date (In serial number format).

 

I would like to be able to display (or Convert) the dates, to short date format and keep the text values as they are.

I can do this in Excel, shorly there must be a way that is eluding me.

Currently showing as

 

But I want it to look like this,   

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi BradTennent ,

     

    You can add a new step like this.

    = Table.TransformColumns(PreviousStepName,{"Course 1",each try Date.From(Number.From(_)) otherwise _})

    Table.TransformColumns

    all steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwUdJRMjE1sjRVitWBCpmChIxMjZCEzIBCAal5KZl56QhBc4hWM0uEkAVQyDNPIaAoP70otbgYIWEJUWthpBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Course 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Course 1", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"Course 1",each try Date.From(Number.From(_)) otherwise _})
in
    Custom1

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

4 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    You can use this expression in a custom column (or use it in a transform step) to get your desired result.

     

    = try Date.From(Number.From([Course 1])) otherwise [Course 1]

     

     

    Pat

    • BradTennent's avatar
      BradTennent
      Frequent Visitor
      3 years ago

      Thank you Pat,

      Is this something I could apply to the existing columns?

      not sure how to add it to a transform step.

       

      Thank you again.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi BradTennent ,

         

        You can add a new step like this.

        = Table.TransformColumns(PreviousStepName,{"Course 1",each try Date.From(Number.From(_)) otherwise _})

        Table.TransformColumns

        all steps.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVUwUdJRMjE1sjRVitWBCpmChIxMjZCEzIBCAal5KZl56QhBc4hWM0uEkAVQyDNPIaAoP70otbgYIWEJUWthpBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Course 1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Course 1", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type",{"Course 1",each try Date.From(Number.From(_)) otherwise _})
in
    Custom1

         

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum