Remove End of Text if it is a Number

[KamKam]

Hi,

I have a column that has addresses. Some of the text items end with the zip codes and some do not. I would like to remove this zip codes.

The rows look something like this:

1. Address1, Address2, Address3,12345

2. Address1, Address2, Address3,1234

3. Address1, Address2, Address3

I would like to remove the numbers at the end. So, only 1 and 2 should have the zip codes removed.

Any ideas?

[parry2k]

@KamKam try this M code

if (try Number.FromText(Text.AfterDelimiter([Column1], ",", {0, RelativePosition.FromEnd}))  otherwise null) = null then [Column1] else Text.BeforeDelimiter([Column1], ",", {0, RelativePosition.FromEnd})

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mahoneypat]

You can add a custom column with this formula to do that.

= Text.Combine(List.Select(Text.Split([Addresses], ","), each (try Number.FromText(_) otherwise 0)=0), ",")

Pat

[KamKam]

Thanks @mahoneypat,

I am getting a Expression.Error: We cannot convert the value to type List.

I suspect that the cause may be that some of the addresses have a space in them.

What do you think?

[mahoneypat]

I added a blank row and it still worked.  Can you provide example row(s) that generate the error?  If you don't need values from the rows that generate errors, you can right click on the column header and choose "Replace Errors".

Pat