Determine If A String Ends With A Number to Write a Conditional Statement.

[Anonymous]

Hi everyone,

What's the formula to determine whether a text ends with a number, any number from 0 - 9, so that can be used to write a conditional statement to create a new column.

For example:

Date                        Year

=====                   12-31-1999

==12-31                 -2000==

12-31-2001=          =====

So the formula would need to look at the Date column to determine if it ends in a number to then combine Date and Year to create a new column with a complete date.

Please let me know if it isn't clear and I can clarify further.

Any help would be greatly appreciated.

Thanks.

[PhilipTreacy]

@Anonymous 

No worries

[Anonymous]

@PhilipTreacy Thank you. Appreciate the solution, you're the best!

Thanks.

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

No worries, here's a solution that gives exactly as you've shown in the Updated Date column.  Here's a sample PBIX - check the Table2 query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYyxDQAwCMN+YS4SgYmBSyr+f6Oo0GZ0nOxNEVA2MNw9glaBCeW6NdikOKuIPNybIviT0ftufO0iDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Addition Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Addition Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Updated Date", each try if (Number.From(Text.End([Date],1)) * 0 = 0) then [Date]&[Addition Date] else [Date] otherwise [Date])
in
    #"Added Custom"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[PhilipTreacy]

Hi @Anonymous 

Try this as proof of concept.  It returns 1 if a number is the last character.  

try if (Number.From(Text.End([Date],1)) * 0 = 0) then 1 else 0 otherwise null)

Based on the data you typed in above, a full example would be something like

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsgUBJR0lQyNdY0NdQ0tLS6VYHZAwWAAooWtkYGAAVAIShSgCChiCtEC0xsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Year = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try if (Number.From(Text.End([Date],1)) * 0 = 0) then 1 else 0 otherwise null)
in
    #"Added Custom"

which gives

Given that in your example above both Date and Year columns contain years I wasn't quite sure how you expected to join the columns.

Regards

Phil

[Anonymous]

Hi @PhilipTreacy 

Thanks for providing a PoC, the solution gives errors for rows that do not end in a digit. I assume you were thinking of using this newly created column for logic to create the updated date column.

The solution am looking for is below, hopefully this time with a better example.

Date                                      Addition Date                               Updated Date (result)

==12-31-1999==                 =========                               ==12-31-1999==

==11-30                               -2000===                                     ==11-30-2000===

12-31-2001=                         =====                                         12-31-2001=

===12-31                             -2002====                                  ===12-31-2002====

Basically looking to the Date column if it ends in a number, if it does, combine with the Additional Date column to create the last column (the result am looking for), else, the last column value will just be the Date column. I can then use replace function to remove the = sign.

Please let me know if that's clearer. Thanks.