Problem on create date column

[alvin199]

Hi, my raw data does not have a date date type column. It only label the excel sheets with quarter-year. After imported all 4 sheets of excel I created 2 new calculated column to fill up all row with quarter and year (each info into a column). For example, Q12021 sheet, the new calculated column is Quarter column is 1 and Year is 2021. Both columns are in Text format. When I change the data type into Date format, it came out a error message stated that "We can't automatically convert the column to date type."

My question is any methods that I can create a Date data type column?

[Anonymous]

Hi @alvin199 ,

You cannot convert a text column to a date format column.

Would you like to accept the above response as your workaround?

Also，we find a similar thread, hope it helps.

We can't automatically convert the column to Date type 

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Aburar_123]

you can not convert the quarter value or an year value to a date. if you want to have a date value then you can do like below,

Assume you have already having two calculated columns/Custom columns created in Power query as like Qtr and Year. So your date column would be like below,

Date = Date(Year,Qtr,1)

[ebeery]

@alvin199 I'm confused by what you're trying to achieve.  In order for a field to be in Date data type, the value needs to be an actual date.  Not a quarter, not a year, a date.

So what is the actual date that you woiuld like represented in your "Date" column?  Perhaps the first day of the Quarter?  In which case you could do something like below:

Start of Quarter Date = 
DATE(RIGHT('Table'[QuarterYear],4), MAX(((RIGHT(LEFT('Table'[QuarterYear],2),1))*3)-3,1),1)

[alvin199]

@ebeery 

Thanks for the reply. 

The Quarter and Year are the columns that I added in Power Query. The original data do not have these 2 columns. It is a label on the original data. Here is how it looks like.

[ebeery]

@alvin199 again, what is the date you are actually trying to represent? The start of the quarter?  The end of the quarter?  Some random date in the middle of the quarter?  

[alvin199]

@ebeery 

The dataset has labelled Quarter 1 2021 until Quarter 4 2021 but it does not have a column putting Quarter and year and date type data. So, I created two calculated columns, namely Quarter (for 1 to 4) and Year (2021). The data type is text. If I change to date or date/time data type, it will give out Error in Power Query.