Converting yyyyMMdd from whole number to date

[robama]

Hello!

I am having some issues with converting a column formatted as a whole number into a date format.

I have a column named "myDate", formatted as a whole number with zero decimal points.
I can't simply change the data type to "Date" so I'm trying to create a whole new column using the DAX date function.

I'm using this function:
NewDate = DATE(LEFT(Table[myDate];4);LEFT(RIGHT(Table[myDate];4);2);RIGHT(Table[myDate];2))

and it throws me this error:
An argument of function DATE has the wrong data type or the result is too large or too small (translated from swedish)

however, if I use the same function but replace the last argument (day) with a number it seems to work, allthough this obviously won't give me my desired result.

I manually created a similar CSV-file and loaded it into Power BI with the column formatted as a whole number with the exact same name (as far as I can tell) just to see if it would generate the same error but to my surprise it actually worked......

I also tried the same function on another column in my original table (also date, but formatted as a whole number) and it worked there too..

And the same function in Excel works perfectly fine..

What could be the issue with the data from my original column (Table[myDate])? I'm running out of ideas and any help would be greatly appreciated. Please note that I'm a newbie when it comes to Power BI.

Much appreciated,
Robert.

[drewlewis15]

Use the query editor, make sure that your myDate field is formatted as text, and add a custom column with the following statement:

=Date.FromText([myDate])

Your end result should look like this: