Forum Discussion

MBZA's avatar
MBZA
Helper I
2 years ago
Solved

Efficiently iterate over and modify a list

Hi, I am trying to calculate the Levenshtein distance between two text values. There's no built in way to do this that I know of, so I rolled my own function but it is very slow. It requires iterating through a list (really a matrix) and changing the elements one at a time while referencing previous elements at each step.

 

Is there a way I can make this more efficient? The code I'm using is below, with both List.Generate and List.Accumulate versions (the latter is the version commented out).

 

let
levendist = (a as text, b as text) as number =>
let
lena = Text.Length(a),
lenb = Text.Length(b),
olist = List.Buffer(List.Generate(
() => [i=0,j=0,v=0],
each ((lena+1)*[j] + [i]) < ((lena+1) * (lenb+1)),
each [ i = if [i]+1 = (lena+1) then 0 else [i]+1,
j = if [i]+1 = (lena+1) then [j]+1 else [j],
v = if [j] = 0 and [i]+1 <= lena then [i]+1
else if [i] = lena then [j]+1
else 0 ])),
ulist = List.Generate(
() => [i=0, l=olist],
each [i] <= List.Count(olist),
(x) =>
let
curlist = x[l],
current = curlist{x[i]},
next = List.ReplaceRange(
curlist,
x[i],
1,
{
if current[i] = 0 or current[j] = 0 then current[v]
else List.Min({
curlist{current[i] - 1 + (current[j] * (lena+1))} + 1,
curlist{current[i] + ((current[j] - 1) * (lena+1))} + 1,
if Text.Upper(Text.At(a,current[i] - 1)) = Text.Upper(Text.At(b,current[j] - 1))
then curlist{current[i] - 1 + ((current[j] - 1) * (lena+1))}
else curlist{current[i] - 1 + ((current[j] - 1) * (lena+1))} + 1
})
}
)
in
[i=x[i]+1, l=next],
each [l]
)
/*ulist = List.Accumulate(olist, {},
(state, current) =>
List.Combine({
state,
{
if current[i] = 0 or current[j] = 0 then current[v]
else List.Min({
state{current[i] - 1 + (current[j] * (lena+1))} + 1,
state{current[i] + ((current[j] - 1) * (lena+1))} + 1,
if Text.Upper(Text.At(a,current[i] - 1)) = Text.Upper(Text.At(b,current[j] - 1))
then state{current[i] - 1 + ((current[j] - 1) * (lena+1))}
else state{current[i] - 1 + ((current[j] - 1) * (lena+1))} + 1
})
}
})
)*/
in
List.Last(List.Last(ulist))
// List.Last(ulist)
in
levendist

(Is there a way to paste code here without the alignment having to be manually fixed?)

 

Edited to add: here are some results of the function

Text AText BExpected Result
KittenSitting3
SaturdaySunday3
MoopMeep2
HelloGoodbye7
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s4sKUnNU9JRCgYyMvPSgSxjpVidaKXgxJLSopTESpBUaR6EAZHxzc8vAHJ8U1NBlBFYzCM1JycfyHPPz09JqkwFssyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Text A" = _t, #"Text B" = _t, #"Expected Result" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text A", type text}, {"Text B", type text}, {"Expected Result", Int64.Type}})
in
#"Changed Type"
  • I've found this answer here:

     

    "I would strongly recommend you to not use PowerQuery or VBA for this. There are much much much better libraries in both R and Python for implementing this methodology."

9 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion

    Hi MBZA, could you provide sample data and expected result please?

    • MBZA's avatar
      MBZA
      Helper I
      Text AText BResult
      KittenSitting3
      SaturdaySunday3
      MoopMeep2
      HelloGoodbye7

       

      Results of the current version are fine - it's just speed that I'm not happy with.

      • dufoq3's avatar
        dufoq3
        Community Champion

        What about sample data? 🙂