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

convert numeric value to year (DATE field)

I am trying to use one of the custom visuals from the Power BI Market place called 3AG Systems - Column Chart with Variance

 

It requires that the your category field be a date variable.  I have a date variable that Power BI only view as a numeric value.  I assume that is because is only the year (i.e., 2018).  How do I covert this numberic value to a date value.  The edit query field conversion does not change the value.  See example below:

 

Beforenumeric to year format.JPG

 

 

Afterafter.JPG

 

 

As you can see the date move to 1905. Please advise.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: convert numeric value to year (DATE field)

Well that's what 2016 as a numeric date value ought to convert to... assuming it doesn't really matter what date in a given year you need it to be, make a custom column saying "01/01/" and then merge that with your year column. It should then give you a format that you can convert to date type.

Community Support Team
Community Support Team

Re: convert numeric value to year (DATE field)

HI, @Bbrown44

After my test, the category field doesn't need a date variable.

for example:

11.JPG

I have three type of category: text, number, date

then I separate drag them into the category field of bar chart with variance.

They work well

12.JPG

 

Also If you want to convert numeric value to date.

you do it like this:

change the column type into Text first.

for example

13.JPG

then convert to date

14.JPG

Note:

When converting text to date and this window appears

Click Add new step

15.JPG

Otherwise it will not success.

 

 

Best Regards,
Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: convert numeric value to year (DATE field)

Well that's what 2016 as a numeric date value ought to convert to... assuming it doesn't really matter what date in a given year you need it to be, make a custom column saying "01/01/" and then merge that with your year column. It should then give you a format that you can convert to date type.

Community Support Team
Community Support Team

Re: convert numeric value to year (DATE field)

HI, @Bbrown44

After my test, the category field doesn't need a date variable.

for example:

11.JPG

I have three type of category: text, number, date

then I separate drag them into the category field of bar chart with variance.

They work well

12.JPG

 

Also If you want to convert numeric value to date.

you do it like this:

change the column type into Text first.

for example

13.JPG

then convert to date

14.JPG

Note:

When converting text to date and this window appears

Click Add new step

15.JPG

Otherwise it will not success.

 

 

Best Regards,
Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.