Annuler le pivotement dynamique de plusieurs colonnes dans LuckyTemplates

Dans ce didacticiel, nous allons revoir un sujet que j'ai abordé plus tôt sur la façon d'annuler le pivotement de plusieurs colonnes dans Power Query à l'aide de List.Zip. Un membre de LuckyTemplates a demandé sur le comment rendre cette méthode dynamique. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

J'ai étiqueté ceci avancé parce que nous allons écrire un certain nombre de fonctions M et certaines d'entre elles pourraient même être nouvelles pour vous.

Cela dit, passons à Power Query.

Table des matières

• Données au format tabulaire
• Modification du code pour détacher dynamiquement plusieurs colonnes
• Dépivotez dynamiquement plusieurs colonnes : ajout d'une logique de colonne personnalisée
• Conclusion

Données au format tabulaire

Comme vous pouvez le constater, ces données ont déjà été transformées en un format de tablette adapté à l'analyse. Ce processus, bien que très important, ne sera pas couvert ici, mais nous passerons brièvement en revue chaque étape.

Dans l'étape source, nous voyons une répétition constante des paires de valeurs, qui sont les heures et les contacts pour chacun des attributs répertoriés ci-dessus.

Dans les étapes appliquées , vous pouvez voir le reste des étapes. Nous avons supprimé certaines lignes supérieures, supprimé les colonnes vides, nettoyé nos valeurs de texte et les avons remplies. Ensuite, nous avons transposé nos données, supprimé l'enregistrement vide, fusionné les colonnes un et deux, nettoyé à nouveau nos valeurs de texte et retransposé nos données.

Nous avons créé une étape personnalisée, que nous examinerons plus en détail ultérieurement, extrait des valeurs de notre liste, puis finalement modifié le type.

Regardons le code entier. La chose qui nous intéresse ici est la fonction List.Zip . Bref récapitulatif, il prend une seule liste en paramètre puis renvoie une liste. En conséquence, nous voyons que cette liste unique dans ce cas contient trois listes distinctes.

Si nous examinons la première liste, elle contient des noms d'attribut codés en dur, uniquement des valeurs de texte qui ont été saisies. La deuxième liste contient des références de colonne codées en dur pour les valeurs d'heure. La liste finale contient des références de colonne codées en dur pour les valeurs de contact.

List.Zip prend ensuite un élément de chacune de ces trois listes et les combine en fonction de leur position dans une nouvelle liste. Par exemple, chaque premier élément de ces listes est combiné dans une nouvelle liste. La même chose se produit pour toutes les valeurs répertoriées en deuxième dans cette liste, et ainsi de suite.

Les valeurs codées en dur peuvent être concernées s'il est peu probable que les attributs changent avec le temps. Donc, si la prochaine fois, tous les éléments ne sont pas présents ou si de nouveaux éléments existent, vous pouvez avoir des ennuis.

Modification du code pour détacher dynamiquement plusieurs colonnes

Alors, comment rendons-nous cela dynamique?

La première chose que je fais est de parcourir le code pour voir s'il existe un endroit où nous pouvons récupérer ces valeurs d'attribut sans avoir à concevoir une étape distincte pour y parvenir. Nous allons donc revenir en arrière dans le code, et cette étape RemoveNull est parfaite.

Vous pouvez voir qu'il répertorie toutes les valeurs d'attribut ici dans la première colonne. Nous pouvons utiliser l'interface utilisateur pour les extraire.

Faites donc un clic droit sur cet en-tête et, en bas, sélectionnez Ajouter en tant que nouvelle requête .

Nous obtenons cette liste et c'est une bonne nouvelle car nous voulons remplacer cette liste codée en dur que nous avons vue plus tôt par la liste dynamique. Tout ce que nous devons faire est de ranger les valeurs.

Commençons par supprimer ces valeurs nulles. Donc, dans la barre de formule, nous ajouterons un List.RemoveNulls . Comme vous pouvez le voir maintenant, tous les éléments sont répétés et c'est à cause des paires de valeurs. Il est répertorié une fois pour les heures.

Et encore une fois, pour les contacts, pour supprimer ces doublons, nous pouvons ajouter un List.Distinct . Nous avons maintenant créé cette logique et tout ce que nous avons à faire est de la copier et de la coller dans notre exemple de requête.

Dans l' exemple de requête , ouvrez l'éditeur avancé et créez un nouveau nom de variable. Appelons-le, AttributeList . Ensuite, nous collons le code et mettons une virgule.

Revenons maintenant à notre étape personnalisée. Dans List.Zip, nous pouvons remplacer cette première liste par notre nom de variable (AttributeList).

Examinons maintenant de plus près la deuxième liste. Nous savons qu'il fait référence aux noms de colonnes, et chacun de ces noms de colonnes commence par le même attribut que nous avons déjà dans cette liste suivi d'un espace et d'un texte "heures".

Nous avons juste besoin d'ajouter un suffixe à chaque élément. Alors revenons en arrière et ajoutons une colonne personnalisée pour créer une logique.

Dépivotez dynamiquement plusieurs colonnes : ajout d'une logique de colonne personnalisée

C'est juste une colonne temporaire, nous n'avons donc pas besoin de lui donner un nom propre. Nous le supprimerons une fois que nous aurons terminé.

Pour modifier les valeurs d'une liste, nous pouvons utiliser la fonction List.Transform . Nous devons transformer les valeurs dans notre AttributeList , donc je vais les coller.

Ici, dans les en-têtes, nous voyons que les valeurs de texte commencent par une lettre majuscule, puis le reste est en minuscule. Notre attribut ne contient qu'un mot en majuscule, nous devons donc également transformer cette valeur de texte. Et nous pouvons utiliser le trait de soulignement pour accéder à chaque élément de la liste et nous ajouterons ce suffixe.

Avec cela, nous obtenons une valeur de liste dans notre colonne. Peu importe celui que vous sélectionnez, mais cliquez simplement sur le côté dans l'espace blanc de l'un d'entre eux ici.

Sous la page, nous voyons un aperçu de cette liste que nous avons créée. Vous voyez donc que ces en-têtes de colonne sont maintenant correctement casés, de sorte qu'ils correspondent aux en-têtes de colonne que nous voyons ici avec cette liste.

Nous avons maintenant les moyens d'identifier les champs de notre fiche auxquels nous voulons accéder. Ouvrons donc à nouveau notre boîte de dialogue de colonne personnalisée et ajoutons Record.SelectFields . Il veut un enregistrement en tant qu'enregistrement, nous pouvons donc utiliser ce trait de soulignement pour accéder à l'enregistrement actuel sur lequel nous nous trouvons. Et puis nous lui avons donné une liste avec tous ces noms de champs, alors ajoutez une parenthèse fermante et appuyez sur OK .

Nous n'avons donc plus de liste, mais nous obtenons maintenant un record. Cliquons à nouveau sur le côté dans l'espace blanc pour afficher le contenu de cet enregistrement. Ici, nous voyons qu'il répertorie les noms des champs d'enregistrement et les valeurs des champs d'enregistrement.

Mais nous ne sommes intéressés que par les valeurs des champs d'enregistrement, nous devons donc les extraire. Revenons à notre boîte de dialogue de colonne personnalisée et ajoutons Record.FieldsValues ​​.

Nous obtenons à nouveau une liste et non plus un record. Nous cliquons à nouveau sur le côté dans l'espace blanc, et vous voyez que nous avons maintenant une liste avec uniquement ces valeurs. Alors ça s'annonce bien.

Copions la logique complète que nous avons créée, à l'exception des dernières parenthèses fermantes. Et maintenant, nous pouvons supprimer notre colonne temporaire car nous n'en avons plus besoin.

Sur notre barre de formule, nous sélectionnons notre deuxième liste et la remplaçons par notre logique.

Pour créer une liste avec les valeurs des champs d'enregistrement pour les contacts, tout ce que nous devons changer est le suffixe. Nous pouvons donc également sélectionner cette dernière liste et la coller dans notre logique.

Ensuite, au lieu d'heures , changeons cela en contacts .

Aucune erreur et ça a l'air bien. Il s'agit désormais d'une solution dynamique pour annuler le pivot de plusieurs colonnes.

LuckyTemplates Unpivot Columns - Power Query Tutoriel
Comment dépivoter des colonnes dans LuckyTemplates
Unpivot and Pivot Basics In LuckyTemplates - Query Editor Review

Conclusion

C'est ainsi que vous annulez le pivot dynamique de plusieurs colonnes dans à l'aide de List.Zip.

Si un nouvel attribut arrive, il sera automatiquement récupéré par l'AttributeList. Et lorsqu'un élément n'existe plus, il ne sera pas présent sur cette liste.

J'espère que vous avez apprécié celui-ci. Regardez la vidéo complète ci-dessous pour plus de détails sur ce didacticiel et consultez les liens ci-dessous pour plus de contenu sur plusieurs colonnes non pivotées dans LuckyTemplates.

Tous mes vœux

Mélisse