4 façons de reproduire la fonction RECHERCHEV d’Excel dans Power Query

Le premier réflexe d’un débutant en Power Query, c’est de chercher à reproduire ce qu’il sait déjà faire avec Excel, mais en l’automatisant par le biais d’une requête. Bien qu’à terme, nous croyons qu’il faudra que ces usagers développent une façon d’aborder les données qui soit différente de celle dans Excel, nous comprenons ce genre de comportement. C’est pourquoi nous cherchons souvent sur ce blogue, à fournir des équivalences de fonction entre Excel et Power Query. Ceci permet d’apprivoiser tranquillement la solution, étape par étape. Aujourd’hui, nous nous penchons sur la fonction RECHERCHEV dans Power Query, avec des paramètres de recherche exacte et de recherche approximative.

Reproduire la fonction RECHERCHEV avec correspondance exacte dans Power Query

Avec les fonctionnalités de fusion de tables

Situation 1

Nous chercherons ici à retourner les quantités par item.

Ces quantités se trouvent dans une table séparée, d’où le besoin de reproduire un RECHERCHEV dans Power Query.

L’option la plus simple, dans ce cas-ci, serait d’utiliser la fonctionnalité qui permet de fusionner des tables côte-à-côte, en sélectionnant la colonne Item comme colonne commune.

Une fois les champs de la deuxième table fusionnés à la première table, nous pouvons développer le champ Quantité (le champ Item fait déjà partie des données de la première table).

Nous obtenons ainsi les quantités par item.

Situation 2

Il arrive parfois que la fusion doive s’effectuer à partir de plus d’une colonne.

Par exemple, ici, nous voulons fusionner la table précédente avec la table suivante pour les colonnes Produits et Groupe et non seulement à partir de la colonne Produits ou Groupe uniquement, pour éviter d’aboutir avec une duplication des informations..

Pour se faire, il faut simpement sélectionner les deux colonnes de fusion dans chaque table.

Ceci nous permet d’obtenir la table suivante, résultat équivalent à une fonction RECHERCHEV qui ferait une recherche sur les colonnes fusionnées, dans Power Query.

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

Avec l’utilisation de listes

Il est intéressant de noter qu’il est également possible de reproduire une fonction RECHERCHEV dans Power Query à l’aide de fonctions de liste, afin d’obtenir encore plus de performance. Pour illustrer le tout, nous allons réutiliser les 2 premières tables de cet article, soit notre table d’items avec coûts et notre table d’items avec quantités. Dans notre table d’items avec coûts, nous allons insérer une fonction List.PositionOf afin d’obtenir la position des items dans notre table avec quantités, i.e. le numéro de ligne de chaque item.

Pour bien illustrer le concept, nous avons trié les quantités en ordre ascendant, dans notre table de quantités.

Le résultat est le suivant. Ne pas oublier que dans Power Query, 0 est réellement 1, 1 est réellement 2, etc. Donc, ici, par exemple, on comprend que les pommes sont à la première ligne de la table de quantités et que les oranges, sont à la sixième ligne.

Une fois qu’on sait sur quelle ligne aller récupérer l’information des quantités, dans la table quantité, on peut simplement ajouter la colonne personnalisée suivante, qui va lire dans la table de quantités, dans la colonne Quantité et sur la ligne déterminée par notre fonction précédente.

Nous aboutissons ainsi avec le même résultat que dans notre premier exemple mais pour de très grosses tables, cette approche est plus optimale en termes de performance. On pourrait même améliorer davantage l’élégance et la performance de cette requête en imbriquant notre deuxième fonction dans notre première fonction et éviter ainsi une étape de transformation.

Reproduire la fonction RECHERCHEV avec correspondance approximative dans Power Query

Pour reproduire une fonction RECHERCHEV avec des correspondances approximatives dans Power Query, on peut à nouveau utiliser les options de fusion de table. Par exemple, supposons que nous cherchions à obtenir les catégories (de la deuxième table ci-dessous) équivalentes aux quantités (de la première table ci-dessous).

Autrement dit, toute valeur entre 100 et 200 devrait tomber dans “Très peu en demande”, entre 200 et 300, dans la catégorie “Peu en demande”, etc.

Il s’agirait alors de fusionner nos tables mais cette fois-ci, en utilisant la colonne numérique de chaque table (Quantité et Valeur) comme colonne de fusion.

Voici le résultat d’une telle fusion.

Nous pourrions ensuite ajouter la colonne conditionnelle suivante.

Et puis trier en ordre croissant les valeurs de cette nouvelle colonne.

Il s’agirait ensuite de remplir les informations vers le bas de la colonne Catégorie.

Pour obtenir le résultat intermédiaire suivant :

Puis supprimer les colonnes non essentielles…

Et les lignes avec des valeurs null dans la colonne Item.

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

Afin d’approfondir vos connaissances avec Power Query, nous vous recommandons de débuter avec notre formation Excel – Introduction à Power Query et au langage M.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.