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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors