cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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

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
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

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

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

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

You are welcome.


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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors