How to fix the Power query code error for splitting a text column based on a criteria

Question

Split the text values in a column based on the data type of the first character in each record.

I need to have the new (custom) column return the text before the first " " delimiter if the first character of the text is a number, otherwise return "0,".

If Value.Is(Text.Start([ConsumerAddress],1), type number) Then 
Text.BeforeDelimiter([ConsumerAddress]," ") else "0,"

I need to have the new (custom) column return the text before the first " " delimiter if the first character of the text is a number, otherwise return "0,".

Answer 1

I don't think Value.Is is quite what you want. I would recommend a try otherwise construction along with Number.FromText like this:

= Table.AddColumn(#"Previous Step", "Custom",
      each try Number.FromText(Text.BeforeDelimiter([ConsumerAddress], " "))
           otherwise 0
  )

If the text before the first space can be converted to a number, then that's what you get. If it can't the Number.FromText throws an error and you get the 0 from the otherwise specification.

---

Edit: If you want the criterion for the first character only, try this:

= Table.AddColumn(#"Previous Step", "Custom",
      each if (try Number.FromText(Text.Start([ConsumerAddress], 1)) otherwise 0) <> 0
           then Text.BeforeDelimiter([ConsumerAddress], " ")
           else "0"
  )

This will return "12b" from "12b Maple St" whereas the first version would return 0 since "12b" can't be converted into a number.