Supprimer les doublons avec Power Query

Vous avez probablement déjà utilisé la fonction « Supprimer les doublons » de Power Query. Cette fonction peut être appliquée à une colonne ou à une table entière.

Par exemple, si on travaille avec les données suivantes, on peut constater que le produit A apparaît deux fois mais qu’il a été catégorisé dans deux types de produits différents.

Si on supprime les doublons de la colonne A, on obtient le résultat suivant :

Remarquez ici que la première occurrence du produit A a été conservée alors que la 2^e occurrence a été supprimée. Par contre, ce n’est pas toujours le cas! Le comportement du « Supprimer les doublons » dans Power Query est impossible à prévoir. Il aurait pu être facile de croire le contraire puisque, dans Excel, lorsqu’on supprime les doublons, la première occurence est toujours conservée alors que les occurences subséquentes sont supprimées. Nous allons voir plus loin comment gérer quelles lignes sont préservées dans Power Query.

Si on désire plutôt supprimer les doublons de la table, Power Query analyse toutes les colonnes pour déterminer s’il y a un doublon. Dans ce cas-ci, aucune ligne ne sera supprimée puisqu’aucune ligne ne contient des données identiques pour toutes les colonnes.

Supprimer des doublons avec conditions

Dans un contexte plus complexe où on veut avoir le contrôle sur les lignes qui sont supprimées et conservées, on doit user de créativité! En effet, on a vu dans le premier exemple que Power Query ne conserve pas nécessairement la première occurrence et supprime les doublons subséquents. Il s’agit d’un « problème » connu par Microsoft pour lequel il n’y a pas encore de solution satisfaisante (à mon humble avis 😉)

Je vais vous présenter une façon de contourner le problème en utilisant des fonctionnalités M plus avancées. Jetons un coup d’œil aux données avec lesquelles nous allons travailler. Il s’agit d’une table qui contient l’historique de variations des prix par produit. Elle contient donc le code du produit, le prix, la date à laquelle ce prix a été mis à jour et l’agent qui a fait le changement.

Si on voulait supprimer les doublons de cette liste (dans Power Query) pour conserver seulement le prix le plus récent, Microsoft ne nous donne aucune certitude par rapport aux lignes qui seront conservées ou rejetées.

Je l’ai testé ici en prenant soin de bien trier les données avant de supprimer les doublons. Peu importe de quelle façon j’effectue le tri, le comportement de la suppression de ligne est impossible à prévoir et le résultat n’est pas celui qui est attendu.

1. Déterminer la date la plus récente de mise à jour

Nous devons donc encadrer notre requête pour obtenir exactement le résultat qui nous intéresse. On doit tout d’abord déterminer la date la plus récente de mise à jour du prix pour chaque produit. Pour y arriver, je vais utiliser la fonctionnalité de « Regrouper par ».

Je vais ensuite regrouper la colonne « Produit » pour obtenir la valeur maximale de la colonne « Date de mise à jour ».

Le résultat devrait ressembler à ceci :

On a maintenant une table qui nous permet d’identifier la date de mise à jour la plus récente de chaque produit.

2. Fusionner avec la table initiale

On va maintenant chercher à fusionner cette table avec la table initiale. Pour y arriver, je vais ajouter une nouvelle étape à ma requête qui réfère à l’étape « Changed Type » de la requête actuelle en cliquant sur le fx à la gauche de la barre de formule. Je saisis ensuite le nom de l’étape à laquelle je veux retourner.

Remarquez que la requête retourne à l’état où elle était à l’étape « Changed type » mais qu’elle a conservé les valeurs maximales de date de mises à jour dans l’étape « Grouped rows ».

On peut maintenant fusionner la table avec la table des dates de mises à jour maximales en utilisant le code M suivant :

Je fusionne ainsi la table dans son état actuel (à l’étape « Custom1 ») avec la même table à l’état où elle était lors de l’étape « Grouped Rows ».

Je peux ensuite exploser les colonnes de « GroupedRows » pour obtenir la date maximale de mise à jour de chaque produit.

3. Ajouter une colonne conditionnelle

Finalement, je vais ajouter une colonne conditionnelle pour identifier les lignes où la date de mise à jour est égale à la date de mise à jour maximale (et ainsi identifier les données à conserver).

Et finalement, filtrer sur la valeur «  Oui » de la colonne « À conserver ».

Le tour est joué !

J’ai réussi à obtenir le prix le plus récent pour chaque produit !

Cette méthode peut être utilisée dans toutes sortes de contextes. Dans tous les cas, il s’agit de :

• Bien identifier la condition qui détermine quelles lignes doivent être conservées (ici c’était la date maximale pour chaque produit)
• Générer une table qui contient uniquement la valeur à conserver par item
• Fusionner la table de valeur unique avec la table initiale
• Conserver seulement les cas où la valeur unique est égale à la valeur sur la ligne de la table initiale

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’explorer les principales fonctions et fonctionnalités de Power Query, qui permet d’importer, de transformer et de fusionner des données de diverses sources et de pouvoir les analyser efficacement, suivez la 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é.