Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Can't convert text to date

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.   "...
Need Help
Show and Tell
Tips and Tricks
  • DoubleJ's avatar
    DoubleJ
    7 years ago

    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

     

DoubleJ's avatar
DoubleJ
Icon for Solution Supplier rankSolution Supplier
7 years ago

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's avatar
peterwak
Frequent Visitor
4 years ago

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.