How could I extract location name from a text field

[EdEvetts]

Hi 

in my transaction table I have a payee field that holds information about the organisation and location of a transaction.  

Payee

Tesco Stores Brighton B1 GB

Pret a Manger London SW1 GB

Ravello London SW1 GB

I would like to be able to extract the location only eg Brighton, London, so I can plot this information on a map. 

Is there any method for doing this in Power BI?

thanks Ed

[Greg_Deckler]

@EdEvetts - Here a much better "M" way. The trick is the ability to reverse the string in M versus not having that ability in DAX. 

Create a blank query and paste this in:

let
    fnUglyStringBeGone = (MyBigFatZombieString) =>

let

    MyBigFatZombieList = Text.ToList(MyBigFatZombieString),
    MyBigFatZombieCrossEyedList = List.Reverse(MyBigFatZombieList),
    MyBigFatZombieCrossEyedString = Text.Combine(MyBigFatZombieCrossEyedList),
    MyShorterBigFatZombieCrossEyedString = Text.End(MyBigFatZombieCrossEyedString ,Text.Length(MyBigFatZombieCrossEyedString ) - Text.PositionOf(MyBigFatZombieCrossEyedString," ") -1 ),
    MyEvenShorterBigFatZombieCrossEyedString = Text.End(MyShorterBigFatZombieCrossEyedString ,Text.Length(MyShorterBigFatZombieCrossEyedString ) - Text.PositionOf(MyShorterBigFatZombieCrossEyedString ," ") -1 ),
    MyCrossEyedLocationString = Text.Start(MyEvenShorterBigFatZombieCrossEyedString,Text.PositionOf(MyEvenShorterBigFatZombieCrossEyedString," ")),
    MyCrossEyedLocationList = Text.ToList(MyCrossEyedLocationString),
    MyLocationList = List.Reverse(MyCrossEyedLocationList),
    MyLocationString = Text.Combine(MyLocationList)
in

    MyLocationString
in
    fnUglyStringBeGone

I then added this custom column called "Location" to my "StringSadnessAndMisery" table, which only contains a single column [StringSadnessAndMisery].

=fnUglyStringBeGone([StringSadnessAndMisery])

This will work as long as your Location sits in between the 3rd delimiter from the end and the 2nd delimiter from the end. In other words, you always have 2 spaces after the actual location you are trying to identify.

[ImkeF]

Actually, in order to extract the 3rd last element in your text-string, you simply need to add a column with this formula in it:

List.First(List.LastN(Text.Split([Payee], " "), 3))

[EdEvetts]

Thanks @Greg_Deckler and @ImkeF. 

Indeed an ugly set of data and to make things worse, the name of the location isnt always in the same place in the text string, I was wondering if you could have some form of lookup list that recognised words in a text string and selected these out? I think I saw you could use dictionaries or lists in Python to do this. I then wondered about splitting as I think is contained in @ImkeF solution. 

Really appreciate your help, I shall have a go with your solutions and see what I can do

thanks Ed

[ImkeF]

Yes, this sounds good, but will be slow if we need to lookup for combinations like "Great Yarmouth" for example (as it would be split as well if using my method above - so you would need to iterate using Text.Contains instead). It is also error-prone due to misspelling.

How about using the postcode as a lookup-element instead? Or is it also international adresses?

[EdEvetts]

Its mainly UK addresses and I could try to use the postcode, but its not always shown and never complete. I think I am going to have to concede defeat on this venture, but thanks for the code it will be useful in other projects

thanks

Ed

[ImkeF]

You could give this a try (copy this code - open the advanced editor and paste everyting in - replacing the existing code):

let
    Source0 = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/List_of_postcode_districts_in_the_United_Kingdom")),
    Data1 = Source0{1}[Data],
    #"Changed Type0" = Table.TransformColumnTypes(Data1,{{"Postcode area", type text}, {"Postcode districts", type text}, {"Post town", type text}, {"Former postal county", type text}}),
    #"Added Custom0" = Table.AddColumn(#"Changed Type0", "Custom", each Text.Split([Postcode districts], ",")),
    #"Expanded Custom0" = Table.ExpandListColumn(#"Added Custom0", "Custom"),
    #"Cleaned Text0" = Table.TransformColumns(#"Expanded Custom0",{{"Custom", Text.Clean}}),
    #"Trimmed Text0" = Table.TransformColumns(#"Cleaned Text0",{{"Custom", Text.Trim}}),
    #"Added Custom1" = Table.AddColumn(#"Trimmed Text0", "First3", each Text.Start([Custom],3)),
    Postcodes = Table.Group(#"Added Custom1", {"First3"}, {{"Count", each List.Min([Post town]), type text}}),


    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkktTs5XCC7JL0otVnAqykzPKMnPU3AyVHB3UorViVYKKEotUUhU8E3MS08tUvDJz0sBSgeHw+WDEstSc3Lyscj4JRaXVIJMKgYZjlBgBFYQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Payee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Payee", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Payee], " ")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 0, 1),
    #"Trimmed Text" = Table.TransformColumns(#"Added Index1",{{"Custom", Text.Trim}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Custom", Text.Clean}}),
    #"Merged Queries" = Table.NestedJoin(#"Cleaned Text",{"Custom"},Postcodes,{"First3"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Count"}, {"Count"}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Expanded NewColumn1",{{"Index.1", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Payee"}, {{"City", each List.Max([Count]), type text}}),
    CheckIfThere = Table.AddColumn(#"Grouped Rows", "Custom", each Text.PositionOf(Text.Lower([Payee]), Text.Lower([City])))
in
    CheckIfThere

[SJ]

Hi I am trying something similar but at a smaller scale.

I am looking to just extract the first letters of the post code

E6 9BZ = E

LE17 5ND = LE

Is there an easy way to do this in Power Query?

[ImkeF]

Hi SJ,

you can split your column by space " " and then add a column that references the first column and removes all numbers:

Text.Remove([Colum1], {"0".."9"}) 

Where "Column" has to be replaced with that name of your first column.

If you want to transform the existing column instead, you use a code line like this:

= Table.TransformColumns(#"NameOfYourPreviousStep",{{"Column1", each Text.Remove(_, {"1".."9"})}})

[Greg_Deckler]

There are lots of strategies for doing these kinds of things in Power BI in both DAX and M code. The problem is that the format of your data is extremely poor and does not lend itself well to being parsed. I'll rack my brain on this one a little more, but I currently don't see it with varying numbers of delimiters and no real flag to key off of. Ugly.

[Greg_Deckler]

Here is a wonderfully hacky and fragile DAX approach. Will work on M approach next and see if I can get something better.

Start with column [BigUglyString] and create new columns:

SmallerBigUglyString = LEFT([BigUglyString],FIND(" GB",[BigUglyString]))

EvenSmallerBigUglyString = LEFT([SmallerBigUglyString],(FIND(" ",[SmallerBigUglyString],LEN([SmallerBigUglyString])-4)))

Location = MID([EvenSmallerBigUglyString],FIND(" ",[EvenSmallerBigUglyString],LEN([EvenSmallerBigUglyString]) - 11),LEN([EvenSmallerBigUglyString]) - FIND(" ",[EvenSmallerBigUglyString],LEN([EvenSmallerBigUglyString]) - 11))

This works with your sample data, the odds of it working with real data are probably slim to none.