Performing calculations on text

[mterry]

I have a column containing a period, which represents a month and year, i.e. 201807 is July of 2018. The data is formatted as a text, and I have a step to create a date from that data: 

Date.FromText(Text.Range([Period],0,4) & "-" & Text.Range([Period],4,2) & "-01")

The issue I have is that for some lines, I need the date to actually be for the next month - so if a certain criteria is met, then 201807 should actually come through as August 2018. I'm not sure hwo to modify the above code so that I can add a "1" to the text value and perform the conversion. 

Below is a sample of the data and code (red portion doesn't work, gives you and idea of what I want to happen) that I'm trying to update. Because of other transformations that take place my preference would be to leave the period as text. 

Period	Sequence	Date
201807	1804	7/1/2017
201807	1804	7/1/2017
201807	1805	8/1/2017
201807	1804	7/1/2017

if [Period] = "201807" and ([Sequence] = "1805" or [Sequence] = "1806") then Date.FromText(Text.Range([Period],0,4) & "-" & Text.Range([Period]+1,4,2) & "-01") else Date.FromText(Text.Range([Period],0,4) & "-" & Text.Range([Period],4,2) & "-01")

[v-yulgu-msft · Accepted solution]

Hi @mterry,

You can convert the text to numeric before caculating, then, change its data type back to Text.

if [Period] = "201807" and ([Sequence] = "1805" or [Sequence] = "1806") then Date.FromText(Text.From((Number.FromText(Text.Range([Period],0,4))-1)) & "-" & Text.Range([Period],4,2) & "-01") else Date.FromText(Text.Range([Period],0,4) & "-" & Text.Range([Period],4,2) & "-01")

Best regards,

Yuliana Gu