LuckyTemplates Power Query : suppression des lignes en double

Dans ce blog, je vais vous montrer comment supprimer les lignes en double dans LuckyTemplates Power Query en fonction des conditions. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

Cet exemple provient d'une question qui a été soulevée sur le . Le membre dispose de données transactionnelles et souhaite supprimer les lignes qui s'annulent.

Il existe deux types de transaction distincts, un pour les entrées et un pour les sorties. Elle a également ajouté le nombre de mouvements. Et sur cette base, elle a formulé trois scénarios.

Le premier scénario est basé sur deux lignes. S'ils s'annulent, les deux lignes doivent être supprimées. Si ce n'est pas le cas, les deux lignes doivent être conservées.

Les scénarios deux et trois sont basés sur une combinaison de rangées, qui s'annulent en partie. Ces lignes doivent être supprimées selon le principe du premier entré, premier sorti, afin que seules les dernières transactions entrantes soient conservées.

Fondamentalement, nous répétons la même logique encore et encore. Nous devrions être en mesure de concevoir une approche unique pour s'adapter à tous ces scénarios.

Certaines des choses que nous allons faire sont d'ajouter une colonne avec des quantités absolues et de grouper par "ajouter une colonne de solde de quantité" et un tableau imbriqué en utilisant l'option "toutes les lignes". Nous allons également transformer ces tableaux imbriqués, trier le type de mouvement et la date de publication, créer un total cumulé et ne conserver que les lignes basées sur une condition.

J'ai noté cela à un niveau intermédiaire car la plupart des transformations que nous pouvons effectuer ici se font en utilisant l'interface utilisateur. Cela dit, passons à la LuckyTemplates Power Query.

Table des matières

• Ajout d'une colonne avec des quantités absolues dans Power Query
• Regrouper par ID Index & Quantité absolue
• Transformer des tables imbriquées dans LuckyTemplates Power Query
  • Type de mouvement de tri et date comptable
  • Création d'un total cumulé
  • Filtrage des lignes en fonction d'une condition
• Conclusion

Ajout d'une colonne avec des quantités absolues dans Power Query

Nous allons commencer par ajouter une colonne avec des quantités absolues. Sélectionnez la colonne Quantité , puis sur le ruban ou l'onglet Ajouter une colonne , allez dans Scientifique , puis cliquez sur Valeur absolue .

Je vais renommer cette colonne dans la barre de formule.

Regrouper par ID Index & Quantité absolue

Nous pouvons maintenant regrouper par ID Index et Quantité absolue. Sélectionnez ID Index , puis maintenez la touche Maj ou Ctrl enfoncée pour effectuer une sélection multiple, puis cliquez sur Abs qty (quantité absolue).

Vous trouverez Grouper par dans l'onglet Transformer.

Mais vous pouvez également le trouver dans l'onglet Accueil.

Cliquez dessus et maintenant, nous allons ajouter une nouvelle colonne. Ce n'est pas un « compte », mais ce sera la « somme » de la quantité. Alors, renommez-le en Qty (quantity) Balance . Ce sera une somme de la colonne de quantité. Et puis, nous ajouterons une autre agrégation, mais ce sera une agrégation spéciale, donc nous sélectionnerons All Rows . Donnons également un nom à cette colonne ( AllRows ). Ensuite, appuyez sur OK.

Si nous cliquons dans l'espace blanc du tableau, nous voyons un aperçu du tableau imbriqué ci-dessous. Nous voyons que les quantités de cette table imbriquée s'annulent et que le solde de quantité est nul.

Si nous regardons le tableau imbriqué suivant (donc cliquez sur le côté dans l'espace blanc de la ligne suivante), nous voyons que la colonne de quantité ne s'annule pas et qu'un solde de quantité est supérieur à zéro.

Ajoutons une colonne personnalisée afin de pouvoir l'utiliser ultérieurement pour nos transformations de table imbriquées. Alors, sélectionnez Ajouter une colonne personnalisée .

Renommez cette colonne en Transformations et nous ajouterons zéro (0) comme espace réservé. Ensuite, cliquez sur OK.

Transformer des tables imbriquées dans LuckyTemplates Power Query

Nous sommes maintenant prêts à créer la logique de transformation de ces tables imbriquées. Mais nous ne voulons pas en écrire un tas, et nous n'en avons pas besoin.

Nous pouvons utiliser l'interface utilisateur pour la plupart. Si nous construisons la logique de ces transformations dans une requête distincte, le scénario le plus élaboré que nous ayons eu était celui avec trois ou quatre lignes.

Maintenant, si je clique sur le côté droit de la dernière ligne, je vois que ce tableau comporte quatre lignes. Nous pouvons donc l'utiliser pour construire nos transformations.

Cliquez avec le bouton droit sur le côté dans l'espace blanc et sélectionnez Ajouter en tant que nouvelle requête .

Nous voyons que la table imbriquée a maintenant été développée et ajoutée en tant que nouvelle requête et nous pouvons l'utiliser pour créer la logique dont nous avons besoin.

Type de mouvement de tri et date comptable

Maintenant, la première chose que nous devons faire est de trier sur Type de mouvement . Choisissons décroissant . De cette façon, les transactions sortantes seront toujours au top.

Ensuite, nous allons trier la Date de publication par ordre croissant , en nous assurant que si nous omettons des lignes, cela se fera toujours selon le principe FIFO (premier entré, premier sorti).

Création d'un total cumulé

Pour identifier les lignes que nous allons supprimer, je vais ajouter un total cumulé à désactiver, et nous pouvons utiliser un List.FirstN pour cette tâche. Cette fonction crée une liste basée sur une autre liste, où les éléments supérieurs sont conservés en fonction d'un nombre spécifique ou d'un critère.

Revenons à notre requête. Nous avons besoin de deux choses pour List.FirstN. Tout d'abord, nous avons besoin de cette liste, et cette liste est notre colonne Quantité . Si je clique avec le bouton droit de la souris sur l'en-tête de la colonne Quantité, nous obtenons l'option Ajouter en tant que NewQuery .

Et ici, nous voyons le code dont nous avons besoin pour générer cette liste. Dans l'étape appliquée, vous pouvez voir qu'elle pointe vers la dernière étape, puis identifie la colonne entre ces crochets.

La deuxième partie dont nous avons besoin pour List.FirstN est un nombre pour identifier le nombre de numéros à conserver dans cette liste. Nous pouvons utiliser l'index pour cela, donc je vais ajouter une colonne d'index à partir d'une (1) pour cette ligne.

Ainsi, pour le numéro 1, il conservera le nombre sur la première ligne de la colonne Quantité, et ainsi de suite. Et nous pouvons maintenant résumer cela.

Ajoutons une colonne personnalisée et appelons-la Running Total . Nous utilisons List.FirstN ici, et nous pouvons voir que le premier paramètre était cette liste, la colonne Quantité. Nous devons pointer vers la dernière étape, et cette dernière étape est le Added Index .

Nous avons identifié la colonne que nous voulons, et c'est notre colonne Quantité. Notre compte est dans la colonne Index.

Maintenant, il renvoie une liste. Si nous cliquons sur le côté dans l'espace blanc, nous pouvons voir le contenu de cette liste. Pour le premier enregistrement, il n'a conservé que la ligne du haut de cette liste. Pour le deuxième record, il a conservé les deux premiers rangs.

Maintenant, tout ce que nous devons faire est de résumer ces quantités et nous pouvons utiliser Lists.Sum pour le faire. Donc, je vais ajouter cela dans la barre de formule. Et je vais aussi mettre Type .

Filtrage des lignes en fonction d'une condition

Avec ce total cumulé, nous pouvons identifier les lignes que nous voulons conserver. Nous ne voulons conserver que les lignes supérieures à zéro, ajoutons donc une condition de filtre.

Nous pouvons maintenant supprimer nos colonnes d'assistance. Sélectionnez Index et Total cumulé, puis cliquez sur Supprimer les colonnes.

Dans l'éditeur avancé, nous pouvons maintenant copier le code que nous avons créé. Je vais ouvrir l' éditeur avancé et nous pouvons voir qu'il s'agit de notre étape fractionnée. Ainsi, nous pouvons sélectionner et copier tout ce qui se trouve en dessous.

Revenons maintenant à notre requête initiale. Encore une fois, ouvrez l'éditeur avancé. Et au lieu de l'espace réservé, passons à une nouvelle ligne. Je vais utiliser l'expression « let » car elle peut capturer des valeurs à partir de calculs intermédiaires dans des variables. Alors, "laissez" puis passez à une nouvelle ligne et collez mes transformations. Aussi, je déclarerai Type .

Le code en surbrillance ci-dessus pointe vers notre tableau imbriqué développé, qui est placé dans la colonne Toutes les lignes. Donc, je vais souligner cela en remplaçant ce code en surbrillance par AllRows .

Ainsi, notre colonne de transformation a maintenant des tables imbriquées. Nous construisons notre requête en fonction de la dernière ligne, n'est-ce pas ? Et cela contenait quatre lignes lorsque nous avons commencé et n'en contenait que deux. Nous avons aussi ces quantités équilibrées à zéro, n'est-ce pas ? Et maintenant, il a une table vide.

Nous pouvons les supprimer en filtrant sur la colonne Solde de quantité. Nous ne voulons pas que le bilan quantitatif soit égal à zéro.

Et puis, je vais sélectionner ma colonne de transformation et supprimer d'autres colonnes. Je peux maintenant développer mes tables imbriquées de transformation. N'utilisez pas le nom de la colonne d'origine comme préfixe et appuyez sur OK.

Dans l'onglet Transformer, sélectionnez Type de données et nous avons terminé. Et c'est ainsi que vous nettoyez les données transactionnelles.

Unpivot and Pivot Basics In LuckyTemplates - Query Editor Review
Mes conseils pratiques lors de l'utilisation de l'éditeur avancé de LuckyTemplates
Transformations avancées dans LuckyTemplates

Conclusion

Dans ce didacticiel, je vous ai montré comment nettoyer les données transactionnelles, en supprimant spécifiquement les lignes en double. Il s'agit d'une excellente technique que vous pouvez utiliser lorsque vous utilisez LuckyTemplates Power Query.

J'espère que vous avez apprécié celui-ci. Regardez le didacticiel vidéo complet ci-dessous pour plus de détails. Consultez également les liens ci-dessous pour plus de contenu connexe autour de LuckyTemplates Power Query Editor.

Acclamations!

Mélisse