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.

 

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

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

     

6 Replies

  • 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, that was exactly what I needed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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)
    )

  • Qmars007's avatar
    Qmars007
    Frequent Visitor
    4 years ago

    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

     

  • Ktoce's avatar
    Ktoce
    Frequent Visitor
    3 years ago

    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.