"Scan" string from field with M

[radu92]

I'm new to Power BI and the M (or power query) language, so any help is really appreciated! 

I have the following data: 

Case	Description
1	from City1 to City2
1	from City2 to City1
2	from City1 to City3
2	between City1 and City3
2	from City3 to City1

What I would like to have is this: 

Case	Description	From	To
1	from City1 to City2	City1	City2
1	from City2 to City1	City2	City1
2	from City1 to City3	City1	City3
2	between City1 and City3	City1	City3
2	from City3 to City1	City3	City1

I have a VBA code that does this, but I'd like to see if it's possible to do it directly in Power BI. The code basically does this: 

1. separate the Description elements by blanks and put them into an array (list)

2. search the array for the keywords "to" or "and" 

3. set  N = index of "to" or "and"

4. set From column = array[N-1] and To column = array[N+1]

Any ideas how I could do the same in Power BI with the advanced query editor? 

[MattAllington]

This is pretty easy using the UI

Load the data to the editor

on the description column, split the column based on delimiter, space, every instance

Delete the columns that you don't need

Rename the columns you keep.

[radu92]

Hi Matt, 

thanks for your reply. Actually my data looks much messier than this - sometimes there are more elements on the left, sometimes more on the right, so splitting the columns and deleting unwanted ones is not an option, unless I can specifically (through a script) identify the column between "from" and "to". 

Do you have any suggestions how I could do that with a function? 

[ImkeF]

You can split your column on your key-terms like this:

= Table.SplitColumn(#"Changed Type","Case Description ",Splitter.SplitTextByAnyDelimiter({" to ", " and "}, QuoteStyle.Csv),{"Case Description .1", "Case Description .2"})

The "Splitter.SplitTextByAnyDelimiter"-function takes a list-argument where you can put in as many splitting-values as you need.

For the further cleanup it would be necessary to know more details of your data.

[radu92]

I tried using the Splitter function, though I'm not sure I understand how I can get exactlly the element I want. 

 This is one string I'm trying to scan: 

Traffic annoucement updated: E45 Sønderjyske Motorvej, from Frøslev to Haderslev between <70> Aabernraa N and Øster Løgum Øst. East road is passable. 

Now I would ideally like to have Frøslev in a "From" column and Haderslev  in a "To" column.  It's already enough if I only get the from column. 

So far I've written this: 

if ( (Text.Contains([Activity description], "from")  and (Text.Contains([Activity description], "to")) ))

           then (List.Last(Text.Split(List.First(Text.Split([Activity description], "to")),"from")))

else null 

This works most of the times, but when there is for example a second "from" or "to" in the string, the function doesn't get it right. If I could set a condition to only take the string between "from" and "to" it would be almost perfect. 

[ImkeF · Accepted solution]

This would be one possibility:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69DoJAEIRfZWNtCD8aEztjNBZqo51SLNxiQLgjeweGN/FR6Hkx8ZTCn2p38s1M5nQaeZCwKmCZmsYDo+zjwygcfyB/QN4L+X9SwYAiMjci+aYoxRv/RIOv1iNjkqRxH5GqiqkgaaAqBRoSc1hNpnDoWimIs0ZfCXbKKK4pG78K19y1Oqf62bnB3mXFsOVcuW4Qz1x7CRYYEUtGhL0d2N21IYZt116qwioHVqgNsEIBqYYStcYoJ6dfGj4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Description " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Description ", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Case Description ",Splitter.SplitTextByAnyDelimiter({" from ", " between", " to ", " and "}, QuoteStyle.Csv),{"Case Description .1", "Case Description .2", "Case Description .3"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Case Description .2", Text.Trim}, {"Case Description .3", Text.Trim}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "From", each List.First(Text.Split([Case Description .2], " "))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "To", each List.First(Text.Split([Case Description .3], " "))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"From", "To"})
in
    #"Removed Other Columns"

[radu92]

Thank you!