This blog post I will demonstrate how to change a DateTime column data type to Date dynamically.

You might be wondering what I mean by dynamic, this means that I will not specify any actual column names. The code will find the column names based on their data type.

For me this has been a challenge to get it working, and not only that when there is a table with multiple datetime columns I do not want to have to manually go and change each column.

As shown below I have a sample of data, when once imported you can see that the OrderDate is set to DateTime

Below I will show you how I got to my final result.

As my good friend Matt Allington always suggests in his blog post, break down the problem into bite size chucks. I am pretty certain I taught him those words of wisdom!

Getting a list of columns with the DateTime data type

My first requirement is to get a list of columns which have the data type of DateTime

The reason for this is twofold.

  1. It will allow my list of column names to be dynamic, meaning that each and every time it will find the columns with the data type of DateTime.
  2. It will allow me to use this list in a later step to pass through to another Power Query function

Below is the code that I used with an explanation afterwards

#"List of Columns with DateTime" = Table.ColumnsOfType( #"Changed Type", {type nullable datetime})
  • The part called is my Step name #”List of Columns with DateTime”
    • I like to rename my steps where I am doing something from a default, so at a later point in time I can find it easily
  • Next, I am using the Power Query function called Table.ColumnsOfType
    • This function will then return a list of columns where the data type is specified
  • I then have my previous step called #”Changed Type”
  • I then finally specify the data type that I am searching for from my table of columns which is datetime
    • {type nullable datetime}

When I click on my step in my Power Query Editor I can now see that it returns the 2 DateTime columns from above

NOTE: If I had to change the type from datetime to number, I would then get the column called “Value”

What this means is you can change the above line of code to search for any data type from your columns in your table.

Converting the DateTime columns to Date

The final step is to now convert those columns which are DateTime to Date

It did take me a while to get this right and after some searching I found this wonderful blog post by Imke at the BIAccountant, where she talks about “Dynamic & bulk type transformation in Power Query, Power BI and M”

This was my AH HA moment, where I now knew how to convert it from a DateTime column to a Date column.

Once again below is the code that I used with an explanation afterwards

#"Convert Date" = 
      Table.TransformColumnTypes
       (
          #"Changed Type", 
          List.Transform
              (
                  #"List of Columns with DateTime",each {_, type date}
              )
      )

NOTE: I have modified the Power Query Language so that it is easier for me to explain what I did.

  • Line 1 – The part called is my Step name #”Convert Date”
    • I like to rename my steps where I am doing something from a default, so at a later point in time I can find it easily
  • Line 2 – This is where I am using the Power Query function Table.TransformColumnTypes
  • Line 4 – This is where I am referring to my last step where I had the actual table of data #”Changed Type”
  • Line 5 – I am not using the Power Query Function List.Transform which allows me to transform the list
  • Line 7 – Because the Power Query Function List.Transform is expecting a List of Values the first requirement for the List.Transform is my List of column names which I created in the previous step with the step name called #”List of Columns with DateTime”
    • The second requirement for List.Transform is the datatype which I set as ,each {_, type date}
    • NOTE: If I wanted to change this to text I would change it from type date to type text
  • The final lines are where I was closing everything off.

NOTE: If I wanted to, I could put it all into one step as shown below (But as I have previously suggested breaking the problem up into bite size chunks makes it easier to solve and troubleshoot)

#”Convert Date” = Table.TransformColumnTypes(#”Changed Type”, List.Transform(Table.ColumnsOfType( #”Changed Type”, {type nullable datetime}),each {_, type date}))

After applying the final step, I then got my table returned where the columns that had the data type of DateTime where now Date as shown below.

Conclusion

As I have shown above how I created 2 steps in the Power Query Editor where I could change all my columns with data types of DateTime to Date

I also learnt a lot during this process and now I can easily change the code if I needed to look for another column data type such as Date or Number

As always thanks for reading and any suggestions are welcome

Here is a link to where you can find the file with this blog post: Transforming DateTime Columns to Date.pbix

References

List of all the available data types in Power Query, thanks to Miguel Escobar from Powered Solutions for letting me use part of his page