cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Error: Cannot convert value '0/01/' of type Text to type Date

I am new to PBI and I am trying to combine two columns into one.  I created a calculated column PERIOD which is based on the two original columns: YEAR and MONTH.  I am getting this error: Cannot convert value '0/01/' of type Text to type Date.  Any ideas?

 

 
 

PBI Error.png

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

A couple of things:
1) The error is coming because you are getting to the 0 at the bottom of your IF statement - that means some of your months don't match. Have you done a Trim on this column in Power Query to remove any spaces?
2) You could use DATE() for this instead of DATEVALUE since your date parts are already separated. It means you won't have to use the & to concatenate and is a bit simpler to work with.
3) Use SWITCH instead of so many nested IF statements:
Period = DATE(Table[Year],
SWITCH( Table[Month]
, "Jan", 1
, "Feb", 2
, "Mar", 3
, "Apr", 4
, "May", 5
, "Jun", 6
...
),
1)


4) Since this is part of the data model and a calculated column, I would personally do this in Power QUERY, NOT in DAX.
https://radacad.com/m-or-dax-that-is-the-question/?ref=818
https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Super User III
Super User III

Hi,

Try this calculated column formula

=1*("1/"&[Month]&"/"&[Year])

Format this column as Date.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Highlighted
Super User II
Super User II

A couple of things:
1) The error is coming because you are getting to the 0 at the bottom of your IF statement - that means some of your months don't match. Have you done a Trim on this column in Power Query to remove any spaces?
2) You could use DATE() for this instead of DATEVALUE since your date parts are already separated. It means you won't have to use the & to concatenate and is a bit simpler to work with.
3) Use SWITCH instead of so many nested IF statements:
Period = DATE(Table[Year],
SWITCH( Table[Month]
, "Jan", 1
, "Feb", 2
, "Mar", 3
, "Apr", 4
, "May", 5
, "Jun", 6
...
),
1)


4) Since this is part of the data model and a calculated column, I would personally do this in Power QUERY, NOT in DAX.
https://radacad.com/m-or-dax-that-is-the-question/?ref=818
https://www.sqlbi.com/articles/comparing-dax-calculated-columns-with-power-query-computed-columns/

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

View solution in original post

Highlighted
Super User III
Super User III

Hi,

Try this calculated column formula

=1*("1/"&[Month]&"/"&[Year])

Format this column as Date.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Highlighted

The DATE + SWITCH fix worked beautifully.  Thanks for the quick response and for sending a couple solutions.  I am sending you Virtual Kudos

Highlighted

Thanks, Ashish.  This worked as well.  It took me a minute to realize I needed to add the column name in the formula "Period =1*("1/"&[Month]&"/"&[Year])".  Pretty slick

Highlighted

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors