Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

PROCV

Good afternoon!


I am having a hard time making a query as I would using procv in excel.

I need to analyze the column "product code" and "document" throughout the table and for each occurrence I have the return value of 1.

I do not know how to do the power query please if you can help me.


Thanks

 

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello

     

    to simulate PROVC in Power Query, use this function

    (SearchList as list, SearchTerm as any, ResultList as list) as any =>

let
    Quelle = ResultList{List.PositionOf(SearchList, SearchTerm)}
in
    Quelle

     

    Have fun

     

    Jimmy

  • Anonymous's avatar
    Anonymous
    6 years ago

    Power query has the concept of merge which is more similar to SQL then PROCV. Learning it will lead to much faster solutions then any attempt to replicate vlookup within PQ.

     

    Pick merger from the ribbon

    Pick the two tables that you want to "vlookup" and select the columns that you wish to lookup.

     

    The query will not look similar to the picture below.

     

    Press the expand button on Table2 to pick the column that you want

     

    You are then left with the result.

     

    I have one warning about this approach. If the table that you are looking up has multiple values in it, then your result set will duplicate. You will have to determine how to handle this issue on a case by case basis.

     

2 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello

     

    to simulate PROVC in Power Query, use this function

    (SearchList as list, SearchTerm as any, ResultList as list) as any =>

let
    Quelle = ResultList{List.PositionOf(SearchList, SearchTerm)}
in
    Quelle

     

    Have fun

     

    Jimmy

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Power query has the concept of merge which is more similar to SQL then PROCV. Learning it will lead to much faster solutions then any attempt to replicate vlookup within PQ.

     

    Pick merger from the ribbon

    Pick the two tables that you want to "vlookup" and select the columns that you wish to lookup.

     

    The query will not look similar to the picture below.

     

    Press the expand button on Table2 to pick the column that you want

     

    You are then left with the result.

     

    I have one warning about this approach. If the table that you are looking up has multiple values in it, then your result set will duplicate. You will have to determine how to handle this issue on a case by case basis.