find value starting position in string from a list of values in power query m lnaguage

[dealwis]

Hi

I have a list of text values. i need to find these values in a column with a description string. I want to output the value and the starting position of the value in the string. if the value is within the string it will only occur once.

ValuesToFind

hello

bye

few

dog

String

I said hello

no worries

john yelled bye

the dog ran down the street

Ideal result

Result
hello	8
bye	13
dog	5

The list of actual values and string descriptions are in the hundreds and I don't necessarily want to look them up manually. but if there is no quick process, i may have to resort to this.

I'm very new to power query and m language. i just wanted to see if there is an easier way to do this task. Any help would be much appreciated. 

Thanking you in advance

[danextian]

Hi @dealwis,

Try the following queries named as follows:

Query: ValuesToFind

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYlBCsAgDAS/suTcj/QN4sHiUi0lgSiIv6/1NMxMCHKipZpR+L4m8QiihmHulW3rY0Ux12XGNblbL0S2G550cSj+0LqTXWL8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Split each word as list" = Table.AddColumn(#"Changed Type", "Text.Split", each Text.Split([String], " ")),
    #"Replaced matching words with something else" = Table.AddColumn(#"Split each word as list", "ReplaceList", each List.ReplaceMatchingItems([Text.Split],ValuesToFind2)),
    #"Converted replaced back to text" = Table.AddColumn(#"Replaced matching words with something else", "Text.Combine", each Text.Combine([ReplaceList], " "), type text),
    #"Returned the position of found text" = Table.AddColumn(#"Converted replaced back to text", "Text.PositionOf", each Text.PositionOf([Text.Combine], "findme") + 1, Int64.Type),
    #"Found matching text" = Table.AddColumn(#"Returned the position of found text", "ValuesFound", each if Text.Length(Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]}))) = 0 then null else Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]})), type text)
in
    #"Found matching text"

Query: VaulesToFind2

let
    Source = ValuesToFind,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ValuesToFind", type text}}),
    ValuesToFind1 = #"Changed Type"[ValuesToFind],
    #"Values and Replacement" = List.Transform(ValuesToFind1, each {_, "findme"})
in
    #"Values and Replacement"

Query: String

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYlBCsAgDAS/suTcj/QN4sHiUi0lgSiIv6/1NMxMCHKipZpR+L4m8QiihmHulW3rY0Ux12XGNblbL0S2G550cSj+0LqTXWL8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Split each word as list" = Table.AddColumn(#"Changed Type", "Text.Split", each Text.Split([String], " ")),
    #"Replaced matching words with something else" = Table.AddColumn(#"Split each word as list", "ReplaceList", each List.ReplaceMatchingItems([Text.Split],ValuesToFind2)),
    #"Converted replaced back to text" = Table.AddColumn(#"Replaced matching words with something else", "Text.Combine", each Text.Combine([ReplaceList], " "), type text),
    #"Returned the position of found text" = Table.AddColumn(#"Converted replaced back to text", "Text.PositionOf", each Text.PositionOf([Text.Combine], "findme") + 1, Int64.Type),
    #"Found matching text" = Table.AddColumn(#"Returned the position of found text", "ValuesFound", each if Text.Length(Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]}))) = 0 then null else Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]})), type text)
in
    #"Found matching text"

[dealwis]

@danextian i get an Expression.Error: A cyclic reference was encountered during evaluation.

ValuesToFind query refs ValuesToFind2 and ValuesToFind2 refs ValuesToFind...

[danextian · Accepted solution]

Try this

ValuesToFind

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykjNyclXitWJVkqqTAXTaanlYDolP10pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ValuesToFind = _t])
in
    Source

ValuesToFind2

let
    Source = ValuesToFind,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ValuesToFind", type text}}),
    ValuesToFind1 = #"Changed Type"[ValuesToFind],
    #"Values and Replacement" = List.Transform(ValuesToFind1, each {_, "findme"})
in
    #"Values and Replacement"

String

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYlBCsAgDAS/suTcj/QN4sHiUi0lgSiIv6/1NMxMCHKipZpR+L4m8QiihmHulW3rY0Ux12XGNblbL0S2G550cSj+0LqTXWL8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
    #"Split each word as list" = Table.AddColumn(#"Changed Type", "Text.Split", each Text.Split([String], " ")),
    #"Replaced matching words with something else" = Table.AddColumn(#"Split each word as list", "ReplaceList", each List.ReplaceMatchingItems([Text.Split],ValuesToFind2)),
    #"Converted replaced back to text" = Table.AddColumn(#"Replaced matching words with something else", "Text.Combine", each Text.Combine([ReplaceList], " "), type text),
    #"Returned the position of found text" = Table.AddColumn(#"Converted replaced back to text", "Text.PositionOf", each Text.PositionOf([Text.Combine], "findme") + 1, Int64.Type),
    #"Found matching text" = Table.AddColumn(#"Returned the position of found text", "ValuesFound", each if Text.Length(Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]}))) = 0 then null else Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]})), type text)
in
    #"Found matching text"

[dealwis]

i still have a few string records that are not identified as have having the code as it isnt separately shown within the string. for

example:

code: dog

string:

row 1: my 2dogs are lovely

row 2: the dog ran down the street

row 3: its raining cats andogs

in the above exmaple, only row 2 would be identified as having the code word and NOT row 1 or 3 as the code word is combined with other characters. anyone know how to look for the code words even if it is not separately shown (without spaces on either side of the code word) within the string?

any help is appreciated. thank you!

[Anonymous]

@dealwis  I stuck same thing with your example2. Are you have solution?

[danextian]

I just thought that what if there were to matches found in the string so I modified the codes a bit.

fnMatchedValues

(String) => 
let
    //check if values in ValuesToFind is in String
    #"IsMatched?" = List.Transform(List.Buffer(ValuesToFind), each Text.Contains(String, _)), 

    //index position of match found
    Position = List.PositionOf(#"IsMatched?", true, Occurrence.All),
    
    out = Function.Invoke((list1 as list, list2 as list) =>List.Transform(Position, each ValuesToFind{_}), {Position, ValuesToFind})

         
in
    out

result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hYzBDQMhDARbWfHOK12kBsTDCdbhE7EljHKi+xA3kJe1M7vOOT3gJBWNe7dUbjmp4bIxhD3iaU2xtuWK5+JgUcZsjGoHBum+lwbwOZhntN4L9+0dNBjdPtxX8H87mb6dqOiBF+1A+nuTSvkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "Match in List", each fnmatch([String]), type list),
    #"Expanded Match in List" = Table.ExpandListColumn(#"Added Custom1", "Match in List"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Match in List", "String Before Match", each try( if Text.BeforeDelimiter([String], [Match in List])<> " " then 
Text.AfterDelimiter( Text.BeforeDelimiter([String], [Match in List]) , " ", {0, RelativePosition.FromEnd}) else null ) otherwise null, type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "String After Match", each try( if Text.AfterDelimiter([String], [Match in List])<> " " then 
Text.BeforeDelimiter( Text.AfterDelimiter([String], [Match in List]) , " ", {0, RelativePosition.FromStart}) else null ) otherwise null, type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Match in String", each [String Before Match]  & [Match in List] & [String After Match], type text),
    #"Added Custom" = Table.AddColumn(#"Added Custom4", "Position of Match in String", each try Text.PositionOf([String],[Match in String])+1 otherwise null, type text)
in
    #"Added Custom"

ValuesToFind is as is

[danextian]

I couldn't think of any other more elegant solution that this.

ValuesToFind

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykjNyclXitWJVkqqTAXTaanlYDolP10pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ValuesToFind = _t]),
    ValuesToFind1 = Source[ValuesToFind]
in
    ValuesToFind1

fnMatchedValues

(String) => 
let
    //check if values in ValuesToFind is in String
    #"IsMatched?" = List.Transform(List.Buffer(ValuesToFind), each Text.Contains(String, _)), 

    //index position of match found
    Position = List.PositionOf(#"IsMatched?", true),

    //return null if Position is negative
    out =  if Position < 0 then null else Position
        
in
    Position

result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jYzBDQIxDARbWeXNiy6oIcrDEOtiFGzJiTile3ypgNdqdrSbc3pgkFQ07t1SueWkhtPchcfGtzXFCssVz8W7m41R7YCTRp6KqxjTmef2n4V7+AFyRrcv9/XXTuYIJyp64EUBpNdNKuUH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [String = _t]),
    #"Added Custom1" = Table.AddColumn(Source, "Match in List", each try ValuesToFind{fnMatchedValues([String])} otherwise null, type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "String Before Match", each try( if Text.BeforeDelimiter([String], [Match in List])<> " " then 
Text.AfterDelimiter( Text.BeforeDelimiter([String], [Match in List]) , " ", {0, RelativePosition.FromEnd}) else null ) otherwise null, type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "String After Match", each try( if Text.AfterDelimiter([String], [Match in List])<> " " then 
Text.BeforeDelimiter( Text.AfterDelimiter([String], [Match in List]) , " ", {0, RelativePosition.FromStart}) else null ) otherwise null, type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Match in String", each [String Before Match]  & [Match in List] & [String After Match], type text),
    #"Added Custom" = Table.AddColumn(#"Added Custom4", "Position of Match in String", each try Text.PositionOf([String],[Match in String])+1 otherwise null, type text)
in
    #"Added Custom"

[dealwis]

Thank you @danextian!

I had to focus on some other work but just today got back into this work and when i replace my query names to this adjusted a few things, i pretty much got what i was after. the only code that didnt work was the last step in String:

#"Found matching text" = Table.AddColumn(#"Returned the position of found text", "ValuesFound", each if Text.Length(Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]}))) = 0 then null else Text.Combine(List.Intersect({[Text.Split], ValuesToFind[ValuesToFind]})), type text)

 maybe because i wasnt following your code to word for word. i just replaced this with,

#"Found matching text" = Table.AddColumn(#"Returned the position of found text", "ValuesFound", each if [Text.PositionOf] < 0 then "" else if Text.Middle([DESCRIPTION],[Text.PositionOf],2) <> "HX" then Text.Middle([DESCRIPTION],[Text.PositionOf],4) else Text.Middle([DESCRIPTION],[Text.PositionOf],5)),

Yay!

[danextian]

Oops. I pasted the wrong code for String. I will have to recreate it later.

[dealwis]

Thank you @danextian! Let me give it a go.

[Greg_Deckler]

So, is the string just a single string or is it many strings on different rows? Your output example seems to suggest the latter. That being said, what happens if you have a row that, in addition ot the examples presented also has a row like "Is it polite to say hello to a stranger?".

Or will that never happen? In other words, for each "find" word will there only ever be a single value?

[dealwis]

Thanks for replying, Greg_Deckler!

There will be hundreds of strings on different rows. In my example i made it super simple. Im actually looking for a set of codes that is in the strings.

One string per row.

A string may or may not have a code.

If a string has a code within it, there could only be one code (there will never be two codes in one string).

A code can be in many strings in different rows.

I think I see the reason why you asked the example. so i guess if the result table has the string, the code and the starting position would make it better. 

String	Word	Position
I said hello	hello	8
no worries	null	null
john yelled bye	bye	13
the dog ran down the street	dog	5
Is it polite to say hello to a stranger	hello	21

Thanks again!

[dealwis]

I used these methods List.AnyTrue and List.Tranform and Text.Contains to check for the code but this only returns the string. I want the code and the starting position. (am I allowed to post other forum links...?)

I used the code but i dont understand the reasoning behind it so i can't adjust to bring in what i want. i got so many List type mismatches.

[Greg_Deckler]

OK, and sorry I'm just trying to be 100% clear on your objective here.

You have a bunch of strings on different rows. You want each of these strings checked against all of your possible search codes. Each string will only have a single code or none at all. You want the position of the code, if it exists, in the string. 

Do you care if you get a solution for this in M (Power Query) or DAX?

[dealwis]

Completely understand Greg_Deckler 🙂

Yes you got it. I would like the String, the Code found in the string and the starting position of the code within the string.

I'd prefer in M please as i havent really used power pivot or DAX.

Thank you