Year and Month to combine as Date

[M_SBS_6]

Hi, 

I have a Year column which is formated as 01/01/2023 so I have changed this to just show "yyyy" which is a Date Data type. 

I have another column which is Month. This shows January to December and is formatted as a text.

I need to combine both of these and create a Date column that would output as 01/01/2023

01/02/2023 etc

Any idea how to do this please? 

[Anonymous · Accepted solution]

@M_SBS_6  It looks like what you're trying to accomplish can be done in the query editor.

Split the Year column by delimiter

Within the Transform tab, select the Split Column drop down. Select "/" as the delimiter, at each occurrence of the delimiter.

Add a custom column with this code:

if [Month] = "January" then 1 else if [Month] = "February" then 2 else if [Month] = "March" then 3 else if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else if [Month] = "July" then 7 else if [Month] = "August" then 8 else if [Month] = "September" then 9 else if [Month] = "October" then 10 else if [Month] = "November" then 11 else 12

ctrl-click the following column headers to select them (in this order - important!): Custom, Year.2, Year.3

In the Transform tab, click Merge Columns

Choose a custom separator, and enter a forward slash (/)

Rename the Merged column to Date (or whatever you'd like it named)

Change the new column's data type to Date

Click Close & Apply on the Home tab