Forum Discussion
VLOOKUP in power query
Hello,
in some of the last posts
So, I tried to sketch a function that would accomplish this comparison.
It was not easy for me to find a solution that was suitable for all the cases that came to mind from time to time and I am still not sure that it is very "robust".
I'd like to, if someone wants to try it out and point out situations where it doesn't do what it was meant for.
The specifications are these: simulate vlookup excel function:
in some of the last posts
and
I've followed, the need to make comparisons between a text string and a pattern, as does the VLOOKUP function, has emerged.
So, I tried to sketch a function that would accomplish this comparison.
It was not easy for me to find a solution that was suitable for all the cases that came to mind from time to time and I am still not sure that it is very "robust".
I'd like to, if someone wants to try it out and point out situations where it doesn't do what it was meant for.
The specifications are these: simulate vlookup excel function:
which should translated to:
the pattern contains strings together with asterisks and the asterisks match any string (even empty).
If all the substrings of the pattern are in the same order in the sample string, the comparison is successful.
If the pattern starts or ends with a substring (not asterisk) the comparison is successful if the sample string begins and ends the same way.
If all the substrings of the pattern are in the same order in the sample string, the comparison is successful.
If the pattern starts or ends with a substring (not asterisk) the comparison is successful if the sample string begins and ends the same way.
let
matchPatt = (schema, parola) => match(Text.Split(schema, "*"), parola),
match
= (pattern, word) => if List.Count(pattern) = 1 and
(pattern{0} = "" or (List.Count(pattern) = 1 and pattern{0} <> "" and Text.StartsWith(Text.Reverse(word), Text.Reverse(pattern{0}))
)
) then
"match"
else if List.Count(pattern) = 1 and (pattern{0} <> "" and pattern{0} <> word) then
"not match"
else if Text.StartsWith(word, pattern{0}) then
let
tailPattern = List.Skip(pattern),
tailWord =
if (List.IsEmpty(tailPattern) or tailPattern{0} = "" or Text.PositionOf(word, tailPattern{0}) = - 1 ) then
Text.Range(word, Text.Length(pattern{0}))
else
Text.Range(
Text.Range(word, Text.Length(pattern{0})),
Text.PositionOf(Text.Range(word, Text.Length(pattern{0})), tailPattern{0})
)
in
match(tailPattern, tailWord)
else
"not match"
in
matchPatt
3 Replies
this fixes a bug and does some toilet
let matchPatt = (schema, parola) => match(Text.Split(schema, "*"), parola), match = (pattern, word) => if List.Count(pattern) = 1 then if (pattern{0} = "" or Text.EndsWith(word, pattern{0}) ) then "match" else "not match" else if Text.StartsWith(word, pattern{0}) then let tailPattern = List.Skip(pattern), tailWord = [tr=Text.Range(word, Text.Length(pattern{0})), trw= try Text.Range(tr, Text.PositionOf(tr, tailPattern{0})) otherwise tr] [trw] in (tailPattern, tailWord) else "not match" in matchPattlet matchPatt = (schema, parola) => match(Text.Split(schema, "*"), parola), match = (pattern, word) => if List.Count(pattern) = 1 then if (pattern{0} = "" or Text.EndsWith(word, pattern{0}) ) then "match" else "not match" else if Text.StartsWith(word, pattern{0}) then let tailPattern = List.Skip(pattern), tr=Text.Range(word, Text.Length(pattern{0})), pos= Text.PositionOf(tr, tailPattern{0}), tailWord = try Text.Range(tr, pos) otherwise tr in match(tailPattern, tailWord) else "not match" in matchPattHi Anonymous ,
So, what is your purpose for posting this post? 🤔
Best Regards,
Icey
Hi Icey
the purpose of the post is what I wrote in the message and which I report here.
<<It was not easy for me to find a solution that was suitable for all the cases that came to mind from time to time and I am still not sure that it is very "robust".
I'd like to, if someone wants to try it out and point out situations where it doesn't do what it was meant for.>>
