Forum Discussion
Power Query case : a clever Vlookup like feature
- 5 years ago
Hello MathMar
I think List.Generate here is an overkill. I tried to use a approach with Table.SelectRows. Here an example
let JobTitle = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVFLTsMwEL3KKCuQIDlDVGjVHSLdRV0Y5xGM4nEYO0i9DUtyjlyMOCEprdh5Pu83Lsukhg/GMVWgVtw7QnK8K5ONs21QLw2mKtdvCOhkKk5p7r3xQXG4we3UejCSPcO3tOdXJ1YF89Fh4bEQbVSzYLsgoK0ONJKqkBWQz5R24PSSKa+s4ZQeA20NK9YGs/pu6GVUzjaOfp9XuMi6tKCj67jIcbe57GeF6/z9Wl6BajD+QGb24uTD8G1xDup4toXVFVZTwxcJWhl6bTzk8gCO9XT2MSdhCTmtPAm8qXAOZmpE7v/iOvbxk+gw9M0o4+w03R/IKlZ11Dz+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Job Title" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job Title", type text}}) in #"Changed Type", Dictionary = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZDBDcMgDEVXQTlH6kBRDi4Y4igxESZDdY4uVlpo1UPiXBDwvr/tPwzdluKMueu7gJIpsnFo2t/YN2w1Pp8yG1n2JQOfl4MISVOAW4nLK0Em3wzWLcN9wUMKdsKMezqEnhjYHhcmlK2M1oz/XuZmVmAImD46R2/vcpYASp9C672s8fO5Mqilqk14PlJN4FzzdVW7CakeFqPO/QWf0KsCF/S45GLACcGpGwrrCQgyRUUyvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t, Classification = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keyword", type text}, {"Classification", type text}}) in #"Changed Type", BufferDictionary = Table.Buffer(Dictionary), AddDictionaryToJobTitle = Table.AddColumn ( JobTitle, "Dictionary", (add)=> Table.SelectRows ( BufferDictionary, each Text.Contains(Text.Lower(add[Job Title]),Text.Lower([Keyword])) ) ), #"Added Custom" = Table.AddColumn(AddDictionaryToJobTitle, "Classification", each try [Dictionary][Classification]{0} otherwise null) in #"Added Custom"Be aware that the last AddColumn is not needed, as you could do this job already in the step before. This is only to show you what table you are getting with the Table.SelectRows. There you can see that in some cases more then one row is found (the selectrows is checking if the keyword is somehow included in the Job Title)
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello MathMar
the correct spelling is this
Table.FuzzyNestedJoin
I don't know when exactly was added. So try to update to the latest version and it should be available
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Thanks again.
I correctly writted it. But it is not recognized.
Expression.Error : Le nom « Table.FuzzyNestedJoin » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.
I also saw in some screenshot some Fuzzy option when trying to Nest table via the menu. I don’t got these options on my version. So it looks like depending on version of Power Query, some instructions are missing.
Regards,