Forum Discussion

MathMar's avatar
MathMar
New Member
5 years ago
Solved

Power Query case : a clever Vlookup like feature

Hello. I'm new in using Power Query and I'm trying to make a query but I'm strugling to make it work. As logic differs from usual language (ie: Python), I have difficulty ti make it work properly. I...
  • Jimmy801's avatar
    Jimmy801
    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