Table de dimension dans un fichier texte : solution Power Query

Dans cet article de blog, je vais passer en revue le problème n° 2 du problème de la semaine en cours sur le , où nous avons discuté de la façon de trier des données désordonnées dans une table de dimension propre. Il s'agit de la deuxième itération de cette nouvelle initiative que nous hébergeons chez LuckyTemplates. Je suis particulièrement passionné par cette série car elle offre à chacun la chance de s'entraîner régulièrement. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

Le premier mercredi de chaque mois, il y a un défi DAX et le troisième mercredi, il y a un défi PowerQuery .

C'est une excellente occasion d'explorer, de découvrir et d'apprendre de nouvelles choses sur ces langages que vous devez exploiter dans LuckyTemplates.

Vous trouverez une catégorie sur le problème de la semaine sur le forum LuckyTemplates.

L'accent est mis sur le processus et non sur le résultat.

Si vous réussissez à décomposer un problème en plus petits morceaux, vous serez en mesure de résoudre n'importe quel problème auquel vous êtes confronté.

Mélissa de Korté

Brian a précédemment parlé d'une technique appelée débogage du canard en caoutchouc . Si vous avez manqué cela, assurez-vous de regarder sa vidéo. Cela peut vous aider lorsque vous êtes bloqué sur ces défis.

Tout d'abord, examinons la tâche à accomplir. Nous avons un fichier texte désordonné que nous devons transformer en une table de dimension appropriée.

Maintenant, quand je regarde des données comme celle-ci, pour la plupart, il s'agit de nettoyer le texte, de supprimer les caractères indésirables, de couper, de mettre des mots en majuscule, etc.

Mais nous devons également récupérer toutes les lignes de ces données de pile et les transformer en une seule ligne pour chaque pays. Je l'appelle pile de données car tous les noms de champs sont répétés dans une seule colonne pour chaque pays.

J'ai une certaine expérience d'Excel, et l'une de ses fonctionnalités les plus puissantes à mon avis est les tableaux croisés dynamiques .

Table des matières

• Tableaux croisés dynamiques dans Excel
• Utilisation de Power Query pour créer une table de dimension
• Fonction de nettoyage de texte personnalisé
• Faire pivoter les données
• Conclusion

Tableaux croisés dynamiques dans Excel

Les tableaux croisés dynamiques me permettent de visualiser les données segment par segment. En fonction de ce que vous placez sur la section de ligne, un tableau croisé dynamique condense chaque occurrence de ce segment en une seule ligne.

Vous pouvez ensuite diviser cela encore plus en faisant glisser des champs dans la section de colonne.

J'ai créé un exemple de ce à quoi cela ressemble. Ici, nous avons à peu près les mêmes données que nous avions dans le fichier texte.

Et sur la page suivante, j'ai créé un tableau croisé dynamique vide.

Maintenant, ce que je ne vous ai pas montré, c'est que j'ai ici trois colonnes au lieu des deux que je vous ai déjà montrées.

Je vais faire glisser mon segment vers les lignes.

Étant donné que la colonne 1 contenait les noms de champ, je vais le faire glisser dans la section des colonnes.

La colonne 2 contenait toutes les valeurs de champ, je vais donc la faire glisser dans la section des valeurs.

Nous pouvons voir ici que les tableaux croisés dynamiques ne peuvent pas gérer les chaînes de texte. Il les compte, mais il montre que nous avons une seule valeur pour chaque champ.

Examinons maintenant ce segment que j'ai créé. Je vais donc revenir aux données et je vais afficher ma colonne. Vous pouvez voir qu'il ne s'agit que d'un numéro d'index, identifiant chacun des blocs de données séparés qui sont encore empilés les uns sur les autres.

Donc pour moi, la transformation clé sera un pivot des données pour les remettre dans ce format tabulaire.

Dans le forum, j'ai vu d'autres façons de gérer cela. Il existe d'autres moyens d'accéder à un format tabulaire en dehors de l'utilisation d'une action de pivot et ils fonctionnent tout aussi bien. Si cela vous intéresse, rendez-vous sur le forum et commencez à explorer.

Utilisation de Power Query pour créer une table de dimension

Passons en revue la requête de puissance et examinons ma solution.

Personnellement, je pense que l'interface utilisateur fait un excellent travail en écrivant la majeure partie du code M pour nous. J'ai donc tendance à concevoir mes requêtes en utilisant autant que possible l'interface utilisateur.

Une fois que la requête fait ce dont j'avais besoin, j'irai dans l'éditeur avancé et examinerai le code M pour voir si je peux le modifier. Voyons à quoi ça ressemble.

Ceci est mon groupe de base qui est construit à l'aide de l'interface utilisateur.

Vous pouvez voir qu'il y a beaucoup d'étapes dans les étapes appliquées sur le côté droit.

Maintenant, ce n'est pas un problème en soi, mais rien qu'en regardant ces étapes, vous pouvez voir qu'il y a beaucoup de transformations qui peuvent être regroupées.

Ouvrons l'éditeur avancé.

On voit que cette requête comporte 31 étapes.

J'ai également ajouté quelques commentaires à cette même requête contenant les 31 étapes, mais je l'ai divisée en sections.

La première chose que j'ai faite a été de supprimer l'étape de changement de type. Je suggère de créer une fonction personnalisée pour effectuer toutes ces étapes de transformation de texte.

Il y a beaucoup de commentaires ici, mais il n'y a que deux choses que je veux souligner dans ce billet de blog. La première est la fonction personnalisée à des fins de nettoyage de texte .

La seconde est l'étape pivot pour transformer ce type de données dans un format tabulaire approprié .

Fonction de nettoyage de texte personnalisé

Revenons aux premières étapes de la construction de ma requête, où j'avais toutes ces étapes groupées pour nettoyer ces textes : la colonne 1 et la colonne fusionnée.

J'ai également ajouté une colonne personnalisée supplémentaire. Son seul but est de créer ma fonction de nettoyage de texte personnalisée . J'ai invoqué cela sur la colonne fusionnée.

De cette façon, je n'ai pas besoin d'écrire la fonction en une seule fois, mais de la construire progressivement une étape à la fois, en ajoutant une nouvelle transformation après avoir examiné les résultats de l'étape précédente.

Regardons le code M pour la fonction de nettoyage de texte.

Comme vous pouvez le voir, j'ai plusieurs étapes. Lorsque je construisais cette fonction de texte, j'allais et venais entre les requêtes pour regarder les résultats pour voir ce qu'il fallait construire et ce qu'il fallait corriger ensuite.

Avec ce résultat, j'ai effectué toutes les transformations dont j'avais besoin. Certaines des fonctions M que j'ai utilisées ici ont été fournies par l'interface utilisateur lorsque j'ai construit ma requête initiale, comme le texte. Fonction de coupe. Cependant, les autres fonctions utilisées ne l'étaient pas.

Si vous ne les connaissez pas, vous pouvez rechercher toutes les fonctions M dans le guide des formules M en ligne. C'est le lien où vous devez aller.

Vous trouverez une section dédiée à la requête de puissance et aux fonctions.

Si vous faites défiler vers le bas, vous trouverez la section sur la fonction de texte et chaque section commence par un aperçu. Il y a une liste de toutes les fonctions de texte à l'intérieur de la requête de puissance et du langage de formule M.

Si vous recherchez une transformation spécifique, vous pouvez la rechercher ici.

Faire pivoter les données

La deuxième partie que je voulais souligner lors de la création de cette table de dimension est le pivotement des données elles-mêmes. Examinons également cela de plus près.

J'ai commencé par ajouter un index. J'ai mis à jour cet index pour segmenter correctement les blocs de données. Je l'ai fait en retournant le numéro d'index pour chaque ligne où il y avait le pays de texte dans la colonne 1, puis j'ai rempli cette valeur.

Tout ce que nous avons à faire est de faire pivoter les données elles-mêmes. Dans l'onglet de transformation, vous trouverez la colonne Pivot. Avec la colonne 1 sélectionnée, cliquez sur Pivot Column.

Il va utiliser les valeurs de la colonne 1 comme nouveau nom de colonne. Il souhaite également savoir où se trouvent les valeurs de ces noms de champs. Ceux-ci sont dans notre colonne fusionnée.

Si Excel peut gérer des valeurs de texte, Power Query peut également en raison de son paramètre d'options avancées. Tout ce que nous avons à faire est de sélectionner Ne pas agréger pour qu'il puisse gérer les valeurs de texte.

Une fois que nous cliquons sur OK, nous pouvons voir que nos données ont été pivotées.

Conclusion

L'image ci-dessus est la requête de résultat final. J'espère que vous avez apprécié la façon dont nous avons remodelé les données désordonnées dans le fichier texte fourni et les avons transformées en un tableau de dimensions propre et adapté à l'analyse.

Si vous avez apprécié cet article de blog, veuillez vous abonner à la chaîne LuckyTemplates afin de ne manquer aucun nouveau contenu.

J'espère tous vous voir dans les prochains défis du problème de la semaine.

Mélisse