Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Performing calculations on text

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.FromTex...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Anonymous,

     

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

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