Transformer la disposition du type de calendrier en format tabulaire dans LuckyTemplates à laide de léditeur de requête

L'analyse des données dont nous disposons peut être difficile si elles ne sont pas correctement organisées. Dans ce didacticiel, je vais vous expliquer comment transformer des données répétées verticales et horizontales en un format tabulaire. Nous faisons cela afin de pouvoir l'analyser plus facilement à l'aide de LuckyTemplates. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

Ce tutoriel a été conçu pour répondre à la question d'un  membre  . Le membre a demandé un moyen de transformer ses données d'une disposition de type calendrier en un format tabulaire.

Avant de commencer, examinons d'abord les données dont nous disposons.

Ici, nous avons une mise en page des données dans laquelle les jours sont configurés horizontalement et les semaines sont configurées verticalement. Ce type d'arrangement est familier à la plupart d'entre nous car c'est ainsi que nous pouvons facilement comprendre les données.

Cependant, ce type de mise en page est plus difficile à analyser car il y a beaucoup de colonnes et de lignes à prendre en considération.

Cela étant dit, transformons les données dans un format de tableau approprié où nous nous retrouvons avec une colonne pour les dates, une colonne pour les mouvements et une autre colonne pour les noms.

Table des matières

• Nettoyage des données pour un format tabulaire approprié
• Casser les tables
• Identification de la liste des nouveaux tableaux pour un format tabulaire approprié
• Détermination des valeurs qui appartiennent au tableau
• Regroupement des données pour un format tabulaire approprié
• Ajout des tables séparées
• Conclusion

Nettoyage des données pour un format tabulaire approprié

Pour faciliter les choses, transformons nos données dans l' éditeur Power Query . Mais avant cela, assurons-nous que la barre de formule est visible sur votre écran. Si ce n'est pas le cas, accédez à l'onglet Affichage, puis cochez la case à côté de la barre de formule.

La première étape consiste à supprimer les valeurs inutiles de nos données. Supprimons la première colonne et les deux premières lignes de nos données puisque nous n'en avons pas vraiment besoin. Pour ce faire, cliquez sur l'onglet Supprimer les lignes, cliquez sur Supprimer les lignes supérieures, entrez 2, puis appuyez sur OK.

Et maintenant, pour supprimer la première colonne, accédez à l'onglet Choisir les colonnes, puis désélectionnez la colonne 1 en décochant la case à côté.

Supprimons également les lignes d'index 14 à 16 puisqu'elles sont complètement remplies avec juste des valeurs nulles. Encore une fois, allez dans l'onglet Supprimer les lignes puis cliquez sur Supprimer les lignes vides .

Un autre problème que nous avons est que les valeurs de date sautent une colonne à chaque fois. Les dates sont toujours suivies d'une valeur nulle, comme indiqué ci-dessous. Cela se produit non seulement sur la rangée supérieure, mais également sur les rangées inférieures.

Pour combler ces lacunes, transposons d'abord notre tableau de données en allant dans l'  onglet Transformer puis en cliquant sur le bouton Transposer.

La table devrait maintenant avoir l'air d'avoir été retournée sur le côté après avoir effectué l'action.

Ensuite, sélectionnez toutes les colonnes contenant les dates, cliquez avec le bouton droit sur l'en-tête de colonne, puis sélectionnez Remplir vers le bas.

Cela devrait dupliquer les dates et combler les lacunes dans les colonnes sélectionnées.

Casser les tables

En regardant les données que nous avons maintenant, nous voyons qu'il y a quatre tables adjacentes les unes aux autres. Les colonnes 1 à 7 forment un tableau, les colonnes 8 à 13 forment un autre tableau, et ainsi de suite.

Pour les ajouter dans un seul tableau, nous devons effectuer une opération de base sans pivotement pour le transformer en un format tabulaire approprié. Mais puisque les tables sont adjacentes les unes aux autres, nous ne pouvons pas simplement faire cela. Nous devons séparer cette table et les recoudre en tables plus petites.

Pour ce faire, nous allons créer la première formule de ce didacticiel. Cliquons sur le bouton fx puis ajoutons la formule que nous allons utiliser. Nous allons utiliser l' opération Table.ToColumns . Cela crée une liste de listes imbriquées de valeurs de colonne à partir d'une table. Chaque colonne sera transformée en liste.

C'est ainsi que j'ai mis en place la formule.

La formule crée une liste des colonnes que nous avions précédemment. La première valeur de la liste contient la colonne 1, la deuxième valeur contient la colonne 2, et ainsi de suite.

Identification de la liste des nouveaux tableaux pour un format tabulaire approprié

Maintenant, nous devons identifier quelles valeurs de la liste contiennent des valeurs de date. Une fois que nous savons où se trouvent ces dates, nous pouvons déterminer où commence ou se termine chacun de nos tableaux séparés.

Pour ce faire, reconvertissons d'abord la liste en tableau en cliquant sur le bouton Vers le tableau dans le coin supérieur gauche.

Ajoutez ensuite une colonne d'index à côté.

La prochaine chose que nous allons faire est d'ajouter une autre colonne à côté de la colonne d'index. Cette colonne devrait nous aider à déterminer quelles valeurs de Column1 contiennent les valeurs de date. Appelons cette colonne Index2.

Pour ce faire, nous allons incorporer une formule dans Index2, qui renvoie la valeur d'index de Column1 si elle contient une date.

Nous allons utiliser la , puis référencer Column1. Les supports serviront d'opérateur ; ajouter 0 entre ces crochets signifie que nous obtenons la première valeur de la liste ; puis nous déterminons s'il s'agit d'une date. Si c'est le cas, je veux que la formule renvoie le numéro d'index ; si ce n'est pas le cas, je ne veux pas qu'il renvoie de valeur du tout.

Étant donné que la première valeur de Column1 contient une date, la première valeur de Index2 doit contenir son numéro d'index 0. De plus, puisque la deuxième valeur de Column1 ne contient pas de date, la deuxième valeur de Index2 ne doit contenir qu'une valeur nulle.

C'est ainsi que j'ai mis en place la formule pour Index2. Assurez-vous simplement qu'il n'y a pas d'erreurs de syntaxe dans votre formule.

Après cela, notre colonne Index2 devrait maintenant avoir l'index des valeurs de Column1 qui contient la date. Il inclura également des valeurs nulles en raison de la formule que nous avons configurée.

Détermination des valeurs qui appartiennent au tableau

Maintenant, ces valeurs d'index à l'intérieur d'Index2 indiquent le début et la fin des tables séparées que nous avons. Autrement dit, 0 marque le début du premier tableau, 7 marque la fin du premier tableau et le début du deuxième tableau, et ainsi de suite.

Pour nous aider à identifier les valeurs de la colonne 1 qui appartiennent au même tableau, appliquons une action de remplissage dans notre nouvelle colonne.

Après avoir effectué l'action, les valeurs nulles doivent maintenant être remplacées par des zéros, des sept, des treize et des vingt. Ces numéros indiquent qu'ils appartiennent à la même table. Autrement dit, les zéros forment une table, les sept forment une autre table, et ainsi de suite.

Regroupement des données pour un format tabulaire approprié

Puisque nous savons déjà quelles valeurs de Column1 appartiennent au même tableau, regroupons-les maintenant. Pour ce faire, nous utiliserons l' opération Grouper par.

En faisant cela, nous nous retrouvons maintenant avec seulement 4 valeurs dans notre colonne Index2. Notez qu'une autre colonne nommée Count se trouve à côté de notre colonne Index2. Cette colonne compte le nombre de lignes dans chacune de nos valeurs dans la colonne Index2.

Cependant, ce n'est pas vraiment ce que nous voulons faire. Ce que nous devons faire est de former une nouvelle table en utilisant les colonnes que nous avons.

Pour ce faire, nous allons utiliser l' opération Table.FromColumns , référencer la colonne qui contient nos valeurs de liste qui est Column1, puis changer le type de retour en table.

Il est important de s'assurer que nous avons référencé la bonne colonne dans notre formule. Vérifiez si Column1 contient vraiment les valeurs des listes.

Cela assemble toutes les valeurs de Column1 avec une valeur Index2 de 0 dans une table, celles avec la valeur Index2 de 7 dans une autre table, et ainsi de suite.

Nous avons vérifié chaque enregistrement à la valeur 0, puis récupéré la valeur de liste de Column1. Enfin, nous avons assemblé ces listes pour former un seul tableau en utilisant ce code M.

Nous avons saisi les 7 premières colonnes et les avons cousues en une seule table, saisi les quelques colonnes suivantes, les avons cousues à une autre table, et ainsi de suite.

Ajout des tables séparées

Maintenant que nous les avons déjà regroupés, essayons d'ajouter tous les tableaux en ajoutant une autre étape. Encore une fois, appuyons sur le bouton fx pour ajouter une nouvelle formule.

L' opération Table.Combine fusionne une liste de tables, puis en crée une seule. Ici, nous allons référencer la colonne qui contient notre liste de tables, qui est la colonne Count.

Nous avons maintenant une table où toutes les tables imbriquées sont ajoutées dans une grande table unique.

Après avoir ajouté tous les tableaux, nous pouvons maintenant effectuer les dernières étapes de transformation de nos données dans un format tabulaire approprié. Sélectionnez les deux premières colonnes de votre tableau, puis cliquez sur le bouton Unpivot Other Columns dans l'onglet Transform.

Après cela, supprimez la colonne d'attribut car nous n'en avons pas vraiment besoin. Accédez à Choisir des colonnes , puis désélectionnez la colonne d'attribut.

Enfin, définissez les types corrects de toutes les colonnes et étiquetez-les correctement.

Double-cliquez sur l'en-tête de colonne de chaque colonne pour modifier leur nom en conséquence.

Pour la première colonne, changez son type en date car elle contient nos valeurs de date.

Ensuite, sélectionnez la deuxième et la troisième colonne et définissez leurs types sur texte.

Terminé! Voici à quoi devrait ressembler notre format tabulaire approprié.

Conclusion

Dans ce didacticiel, nous avons transformé certaines données, qui étaient présentées dans une disposition de type calendrier, en un format tabulaire approprié. Nous avons d'abord nettoyé nos données, les avons divisées en tables plus petites, regroupées les tables qui vont ensemble, puis les avons toutes ajoutées à la fin.

Ce didacticiel vous offre de nombreuses connaissances sur la façon de modéliser correctement vos données pour une analyse plus facile.

Il est très important de comprendre les concepts qui ont été utilisés ici, car ils peuvent également être appliqués de nombreuses autres manières.

Si les concepts ne sont toujours pas clairs pour vous, consulter les liens ci-dessous peut vous aider.

Profitez de travailler à travers celui-ci!

Mélisse