Extracting more that 1 suburb word

[adsa]

Hi All,

I’ve been provided with some data where the address is in a single line. I need to extract the Suburb in a separate column.

I’ve used the text between delimiter function to extract this and in most instances it works, however if the suburb name contains more than 1 word it only shows last word. Would greatly appreciate if there is a solution on being able to extract the full suburb.

In the highlighted row below I need to get New Alexport instead of just Alexport but i’ve hit a roadblock.

Ideally I’m looking to create a function where i can invoke it to just extract the suburb.

I also have a list of all street types with their abbreviations as the suburb always follows the abbreviated street type.

Sample.pbix (27.2 KB)

Thanks

[Melissa]

Hi @adsa,

Give this a go:

1. Create a new variable in your Address query:
   Abrev = List.Buffer( List.Transform( #“Street Type”[ABBREVIATION], each _ & " ")),
2. And add a custom column with this logic for the suburb:
   List.Last( Splitter.SplitTextByAnyDelimiter( Abrev )([Address]))

Here’s the result

.

I could identify one issue with your data though…

@adsa

I also have a list of all street types with their abbreviations as the suburb always follows the abbreviated street type.

In that case the street type abbreviation for this address: MCCULLOUGH PD SOUTH ALEXIS
is missing in the Street Type dim table.

I hope this is helpful.

[adsa]

@Melissa

Thank you so much for the assitance. I’ve implemented your solution into my code and it works a treat!. In regards to the missing items in the list, I have added in some additional M code to handle these.