Forum Discussion

krobinson's avatar
krobinson
New Member
2 years ago

Date conversion

I've imported a csv file into Power Query and have a field predominantly for dates in yyyymmdd format, but where there isn't a date, it's showing as blank.

 

how do I create a normal date format dd/mm/yyyy from yyyymmdd in power query and make null values blank?

Thanks

Need Help
Tips and Tricks

4 Replies

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Hello! In Power Query, click on your date column so that it is highlighted, then click the transform ribbon, then click the dropdown for Data Type and select Date.

     

    Here is an example I created:

    After changed the type to Date it now appears like this:

    M Code created by changing the type: #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}})

     

    • krobinson's avatar
      krobinson
      New Member
      2 years ago

      That gives me the date in the format I want it. But in the original import, blanks became null. So I want to get rid of the null and leave those as blank

       

      Thanks

      • audreygerred's avatar
        audreygerred
        Super User
        2 years ago

        I don't believe you can change a record to blank when the field is formatted as Date. A common practice I see is replacing the nulls with a date that is obvioulsy not correct (such as 1/1/1900). Also, if you leave as null, it will still appear as a blank in the viz (see top row of below viz).