Forum Discussion
Problem on create date column
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?
6 Replies
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)Anonymous
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.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?
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)
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 _ CaitlynIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
