Forum Discussion
Advocate IIconvert 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:
Before
After
As you can see the date move to 1905. Please advise.
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.
HI, Bbrown44
After my test, the category field doesn't need a date variable.
for example:
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
Also If you want to convert numeric value to date.
you do it like this:
change the column type into Text first.
for example
then convert to date
Note:
When converting text to date and this window appears
Click Add new step
Otherwise it will not success.
Best Regards,
Lin
5 Replies
v-lili6-msftCommunity Support
HI, Bbrown44
After my test, the category field doesn't need a date variable.
for example:
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
Also If you want to convert numeric value to date.
you do it like this:
change the column type into Text first.
for example
then convert to date
Note:
When converting text to date and this window appears
Click Add new step
Otherwise it will not success.
Best Regards,
LinBy the way, you can find full documentation for this and our other Power BI visuals here:
https://www.3agsystems.com/blog/bar-chart-with-relative-variance
https://www.3agsystems.com/blog/column-chart-with-small-multiples
https://www.3agsystems.com/blog/bar-chart-with-absolute-variance
https://www.3agsystems.com/blog/column-chart-with-relative-variance
https://www.3agsystems.com/blog/column-chart-with-absolute-variance
jthomsonSolution Sage
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.
JulianaMacedoHelper II
Hi I'm facing a similar problem but couldn't find my solution with the suggested steps, I tried it but did not work.
I mean, it would be easier to add 2000+ to all numbers if I new all of them are from the year 2000 and ahead... but if I have any year bellow that then this does not work.
Is there a smart way to do this?
I have a column with 2-digit numbers that I need to transform into a new columsn with 4 digitis as the Year.
Column 1 Desired result 20 2020 21 2021 20 2020 19 2019 21 2021 19 2019 Add number of days from 1900 to 2000
