Please note javascript is required for full website functionality.


Power Query: All Dates Must Change

2 December 2020

Welcome to our Power Query blog. This week, I look at how to dynamically set the Data Type on all my datetime columns to date.

I have some employee data for my imaginary salespeople:

I extract my data to Power Query by using the ‘From Table’ option on the ‘Get & Transform’ section of the Data tab.

As usual, I accept the defaults.

I can see that all my date columns have defaulted to Data Type ‘Date/Time’.  I want to change them all to Data Type ‘Date’.

I start by getting a list of columns with my target Data Type ‘Date/Time’.  The M function I will use is Table.ColumnsOfType():

Table.ColumnsOfType(table as table, listOfTypes as list) as list

This returns a list with the names of the columns from table that match the types specified in listOfTypes

I have entered a new step with the M code:

= Table.ColumnsOfType( #"Changed Type", {type nullable datetime})

When I enter my step, I should get a list of the columns with Data Type ‘Date/Time’.

I have my list, now I need to change the datatype for all the columns in my list. To do this I will combine two M functions, Table.TransformColumnTypes() and List.Transform().

Table.TransformColumnTypes(table as table, typeTransformations as list, optional culture as nullable text) as table

This parameter returns a table from the input table by applying the transform operation to the columns specified in the parameter typeTransformations (where format is { column name, type name}), using the specified culture in the optional parameter culture (for example, "en-US").  If the column doesn't exist, an exception is thrown.

List.Transform(list as list, transform as function) as list

This returns a new list of values by applying the transform function transform to the list.

I create a new step:

The M code I have used is:

= Table.TransformColumnTypes

(#"Changed Type", List.Transform(#"Custom1",each {_, type date}))

I have transformed my list to specify the Data Type to change to, creating a list of pairs of data (column name, Data Type).  This list can then be used to transform the columns that the list refers to.  When I enter my step, the datatype should change on all the columns in the list created at step ‘Custom1’.

I can see that all the columns in my list have been updated to Data Type ‘Date’.  Since I have done this by creating a list, adding or removing date columns will not break my code.

Come back next time for more ways to use Power Query!