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...
Need Help
  • 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

     

MathMar's avatar
MathMar
New Member
5 years ago

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,

MathMar's avatar
MathMar
New Member
5 years ago

Thanks for your suggestion related to write my last instruction in a different way: L_Keyword_Responsability{List.PositionOfAny([CHECK RES],List.Buffer(L_Keyword_Responsability))-1}

I succeed to make what I want by then using a Table.NestedJoin until I got latest version of Office to be able to use directly Table.FuzzyNestedJoin.
Until we succeed to get from our IT proper version, we would like to use what we built. However, on very large file (>200K records), now request is taking ages. We tried to use Table.Buffer and List.Buffer to speed things but still very very low.
Do you have suggestion to improve our code here to speed up things?

Here is our full code:

let
Source = Csv.Document(File.Contents("F:\all contact.csv"),[Delimiter=",", Columns=41, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"move Job Title to Lower case" = Table.TransformColumns(#"En-têtes promus",{{"Job Title", Text.Lower, type text}}),
#"Check match with Responsability Matrix" = Table.AddColumn(#"move Job Title to Lower case", "CHECK RES", each List.Generate(
() => [COUNTER = 0, MAX=List.Count(L_Keyword_Responsability), res="n.a", char=[Job Title] ] ,
each ([COUNTER] < [MAX]+1) ,
each if Text.Contains([char],L_Keyword_Responsability{[COUNTER]})
then [ inter=List.Buffer(L_Keyword_Responsability) ,res= inter{[COUNTER]}, COUNTER=[COUNTER]+1, MAX=[MAX], char=[char] ]
else [ res= "n.a", COUNTER=[COUNTER]+1, MAX=[MAX], char=[char] ],
each [res] )),
#"Get corresponding value in the table" = Table.AddColumn(#"Check match with Responsability Matrix", "Intersect", each L_Keyword_Responsability{List.PositionOfAny([CHECK RES],List.Buffer(L_Keyword_Responsability))-1}),
#"Erreurs remplacées" = Table.ReplaceErrorValues(#"Get corresponding value in the table", {{"Intersect", "vide"}}),
#"croisement matrice Responsabilité" = Table.NestedJoin(#"Erreurs remplacées",{"Intersect"},Table.Buffer(Matrix_Responsability),{"Mot clé autour de la responsabilité"},"Matrix_Responsability",JoinKind.LeftOuter),
#"Récupération responsabilité estimée" = Table.ExpandTableColumn(#"croisement matrice Responsabilité", "Matrix_Responsability", {"résponsabilité résumée"}, {"résponsabilité résumée"})
in
#"Récupération responsabilité estimée"

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    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