Forum Discussion
Solved
Difference between two date values that are in text format.
Hello,
I have two rows having year values named as START YEAR and END YEAR. But these values are in text format. Can anybody please help me in calculating difference between these two values.
| startYear | endYear |
| 2017 | 2020 |
| 2001 | 2010 |
| 2018 | \N |
| 2013 | 2020 |
| 2001 | 2006 |
| 2011 | \N |
| 2017 | 2019 |
| 2009 | 2009 |
| 2003 | 2019 |
| 2012 | 2020 |
| 2021 | \N |
| 2016 | \N |
you can create a column.
Column = if('Table (2)'[endYear]="\N",blank(),int('Table (2)'[endYear])-'Table (2)'[startYear])
6 Replies
NidhiBhusari , In power query you have the option to clean the data, use that that will remove \N (return),
Then change the data type to text and try.
Under data tranform -(power query) -> Tranform tab -> clean
you can create a column.
Column = if('Table (2)'[endYear]="\N",blank(),int('Table (2)'[endYear])-'Table (2)'[startYear])ryan_mayu can you share the sample file in which you have implemented the scenario?
pls see the attachment below
