PROCV

[Anonymous]

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

[Jimmy801 · Accepted solution]

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 · Accepted solution]

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.

[Jimmy801 · Accepted solution]

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