Difference between two date values that are in text format.

[NidhiBhusari]

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

[ryan_mayu · Accepted solution]

@NidhiBhusari 

you can create a column.

Column = if('Table (2)'[endYear]="\N",blank(),int('Table (2)'[endYear])-'Table (2)'[startYear])

[NidhiBhusari]

@ryan_mayu can you share the sample file in which you have implemented the scenario?

[ryan_mayu]

@NidhiBhusari 

pls see the attachment below

[NidhiBhusari]

Hey @ryan_mayu Thanks for the solution...its working.

[ryan_mayu]

you are welcome

[amitchandak]

@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