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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Sunnie
Helper I
Helper I

Dates showing as year: 1899

I need some help. I have created a conditional column where the output data is a date.  Yet power bi changed the year to 1899.  I 've tried multiple ways to change the date in the column and unable.  

 

IF startmonth = January then #date(2021,1,1)

 

Thanks in advance

1 ACCEPTED SOLUTION

This is what I see. StartMonth is a text datatype (it should be a datetime).  It contains 5 possible values (in the visible data) , one of which is blank.

The code in the added column is looking for text values of December, January etc  which it will never find.  The code always falls through to the "else" clause which is 0.  A 0 date represents 1/12/1899.

----------------------

It looks like you want to get the 1st of the month for a non-blank date and put that in a new column. That's straightforward to do.

In Power Query, select the StartMonth column. Change the datatype to Datetime.

Go to the Add Column menu.

From the ribbon, select Date->Month->start of month.

That will get the 1st of the month and put null in for empty dates.

How does that sound?

View solution in original post

9 REPLIES 9
HotChilli
Super User
Super User

OK, i see the code (and the result column). I also need to see the sample data from the table otherwise I can't debug it. 

HotChilli
Super User
Super User

Please show us the code for the conditional column and some sample data please

that data is confidential, the data pulls from a sharepoint page

How can anyone debug if we can't see the StartMonth column?

sorry,  does this help? 

Sunnie_0-1616623972726.png

Sunnie_0-1616624139699.png

 

This is what I see. StartMonth is a text datatype (it should be a datetime).  It contains 5 possible values (in the visible data) , one of which is blank.

The code in the added column is looking for text values of December, January etc  which it will never find.  The code always falls through to the "else" clause which is 0.  A 0 date represents 1/12/1899.

----------------------

It looks like you want to get the 1st of the month for a non-blank date and put that in a new column. That's straightforward to do.

In Power Query, select the StartMonth column. Change the datatype to Datetime.

Go to the Add Column menu.

From the ribbon, select Date->Month->start of month.

That will get the 1st of the month and put null in for empty dates.

How does that sound?

@HotChilli , your solution worked! 

I will try out your solution by tomorrow.  and post outcome 🙂

= Table.AddColumn(#"Duplicated Column", "StartMonthDate", each if [StartMonth] = "January" then #date(2021, 1, 1) else if [StartMonth] = "February" then #date(2021, 2, 1) else if [StartMonth] = "March" then #date(2021, 3, 1) else if [StartMonth] = "April" then #date(2021, 4, 1) else if [StartMonth] = "May" then #date(2021, 5, 1) else if [StartMonth] = "June" then #date(2021, 6, 1) else if [StartMonth] = "July" then #date(2021, 7, 1) else if [StartMonth] = "August" then #date(2021, 8, 1) else if [StartMonth] = "September" then #date(2021, 9, 1) else if [StartMonth] = "October" then #date(2021, 10, 1) else if [StartMonth] = "November" then #date(2021, 11, 1) else if [StartMonth] = "December" then #date(2021, 12, 1) else 0)

 

Sunnie_0-1616622682771.png

Sunnie_1-1616622747041.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors