Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
robama
New Member

Converting yyyyMMdd from whole number to date

Hello!

capture20170916110259354.pngcapture20170916110318993.png

 

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.

1 REPLY 1
drewlewis15
Solution Specialist
Solution Specialist

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:

date.jpg

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors