Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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."
Solved! Go to 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
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.
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 - 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)
)
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
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.
Thanks, that was exactly what I needed.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |