Can't convert text to date

[Anonymous]

Hi guys, I am newbie in powerbi 

I have this column with dates like '20180101' which is supposed to be 2018-01-01. 

I tried converting it in the modelling but received this error message.

"We can't automatically convert the column to Date type."

[Ktoce]

I had exact same issue as OP. I was able to change the type from number to text in one step and then change type from text to date in next step. Makes no sense at all but it worked.

[Qmars007]

Hi JJ,

I have a similar issue (Can't convert text to date) but my date format is like Q1 13/14, Q2 13/14, Q3 13/14 and Q4 13/14.

The time span starts at Q3 09/10 and ends in Q4 30/31.

Any idea?

Thanks in advance

Q

[Anonymous]

@Anonymous - If it's an integer, you could create a new Custom Column in Power Query:

#date(
    Number.RoundDown([DateKey]/10000),
    (Number.Mod([DateKey],10000) - Number.Mod([DateKey],100))/100, 
    Number.Mod([DateKey],100)
)

[DoubleJ · Accepted solution]

That's interesting because I was able to transform the exact same string to a date using the Power BI Transform functionality.

However, you could add a calculated column that does the transformation

DateColumn = 
DATE(
    LEFT('Table'[DateString],4,
    MID('Table'[DateString],5,2),
    RIGHT('Table'[DateString],2))

You have to make sure that the string always has the same format YYYYMMDD.

Hope this helps

JJ

[peterwak]

I had the same problem, date/time data in text format, getting the same error when trying to change the format. My mistake was to try to change the format in the Home panel. Once I entered the Power Query Editor, I was able to 'Change type', by right-clicking the column header. Worked perfectly, no need to write a measure or create a new column.

[Anonymous]

Thanks, that was exactly what I needed.