Tutoriel Microsoft Power Query sur la résolution des problèmes de largeur de colonne fixe mixte

Mudassir : Pour aujourd'hui, nous avons un problème très intéressant à résoudre. Le problème avec ce fichier est qu'il est délimité par des colonnes et je ne sais pas comment résoudre ce problème à l'aide de Microsoft Power Query. Vous pouvez regarder la vidéo complète de ce tutoriel au bas de ce blog.

Tout d'abord, il ne m'a pas été facile de supprimer dynamiquement les colonnes. Deuxièmement, dans ce rapport, nous avons un tableau avec une largeur de colonne différente, puis un autre tableau avec une largeur de colonne différente.

Donc, si j'applique dynamiquement un délimiteur en haut, je ne pourrai pas obtenir les données proprement. Je veux obtenir toutes ces données de la deuxième table et mes numéros de produits de la première table. Je veux aussi le numéro de travail dans chaque ligne de toutes les tables.

J'ai essayé de le résoudre par moi-même, mais comme cela a quelque chose à voir avec la requête de puissance, j'avais besoin de l'aide de Melissa. Je pensais que cela lui prendrait au moins deux jours, mais elle a tout de suite réussi à trouver une solution.

Melissa nous montrera comment elle a résolu ce problème compliqué. Je pense que la plupart des gens seront confrontés à ce genre de problèmes et chercheront des moyens de les résoudre.

Melissa : Le premier conseil est que si vous regardez un fichier de longueur fixe, vous pouvez accéder à l' onglet Affichage et activer l' option Monospaced .

Nous pouvons voir qu'il s'agit d'une police de longueur fixe. Nous pouvons également voir les en-têtes, les tableaux initiaux et les sous-tableaux. Ce sont les parties qui nous intéressent et dont nous voulons extraire.

Assurez-vous également que votre barre de formule est activée. C'est toujours une bonne chose de l'avoir visible sur votre écran car nous l'utiliserons fréquemment pour apporter de légères modifications à l'entrée.

J'ai créé un paramètre pour l'emplacement du fichier où j'ai stocké le fichier CSV. Je l'ai apporté en tant que fichier intermédiaire et l'ai sous-titré dans mon paramètre d'emplacement de fichier. Ensuite, j'ai créé une référence et je vais travailler à partir de cette référence. C'est donc ce que nous examinons en ce moment dans Microsoft power query.

Table des matières

• Ajout d'une colonne d'index
• Création d'une liste de tampons dans Microsoft Power Query
• Séparer les en-têtes des lignes
• Suppression des valeurs nulles et des blancs dans Microsoft Power Query
• Diviser les sections
• Couper les chaînes de texte dans Microsoft Power Query
• Conclusion

Ajout d'une colonne d'index

Habituellement, lorsque je commence à travailler sur un dossier comme celui-ci, j'ai besoin de connaître les exigences du client. Je demande ce dont le client a besoin et ce qu'il faut rechercher.

Dans ce cas, nous voulons le numéro d'article et le numéro de travail des en-têtes, puis nous voulons tous les détails qui appartiennent à cet en-tête spécifique.

Nous aurons besoin d'une clé pour remettre ces choses ensemble. Mais s'il n'y a pas de clé présente, alors mon truc est d'ajouter un index column . Je clique sur l'icône de la mini table, sélectionne Add Index Column , puis ajoute From 0 .

J'utiliserai une logique plus tard avec des listes, qui ont l'index basé sur 0. Le fait que votre index commence à zéro signifie en fait que vous pouvez référencer la même ligne. Sinon, vous devrez soustraire 1 pour arriver à cette position basée sur 0.

Ensuite, nous devons trouver la position où se trouvent nos en-têtes, ce que nous pouvons faire assez facilement car ces en-têtes sont répétés constamment dans tout le fichier.

Pour commencer, copions cette valeur :

Ajoutez une nouvelle requête vide, collez-la et appelez-la HeaderID .

Je ferai le même processus pour les sous-tableaux. Je vais copier cette chaîne de texte, créer une autre requête vide et coller cette valeur. Ce sera la chaîne que nous utiliserons lors de la recherche de lignes détaillées.

Si ce processus modifie d'une manière ou d'une autre l'en-tête de l'une de ces tables, tout ce que j'ai à faire est de modifier l'une des chaînes de texte et le fichier fonctionnera à nouveau.

Je n'ai pas vraiment besoin de plonger dans le code M pour rechercher cette chaîne que nous recherchons. Nous pouvons simplement l'utiliser comme paramètre.

Activons la charge pour ces deux requêtes.

Création d'une liste de tampons dans Microsoft Power Query

La première chose que je vais faire est de transformer Column1 en une liste en la référençant et en la chargeant une fois en mémoire. De cette façon, je n'ai pas à faire des appels répétés au fichier.

Je vais ouvrir l'éditeur avancé et le placer tout en haut. Lorsque vous utilisez l'interface utilisateur pour créer votre code, il fait référence à l'étape précédente.

Lorsque vous placez une étape tampon n'importe où ailleurs dans votre code et que vous souhaitez apporter une modification, cela vous aidera ultérieurement à apporter les modifications à l'étape que vous créez manuellement.

Je vais appeler cette BufferList et faire référence à Column1. Pour le charger en mémoire, je vais ajouter une étape List.Buffer .

Voici ma variable tout en haut. Je peux le référencer encore et encore.

La première chose que je veux déterminer est le début de mes en-têtes, car j'ai besoin d'une clé pour conserver ces sections d'en-tête et obtenir une valeur unique pour toutes ces lignes. Pour ce faire, je vais ajouter une colonne personnalisée et l'appeler Header .

J'écrirai que si Column1 est égal à notre ID d'en-tête, alors je veux que mon numéro d'index soit nul.

En conséquence, il a trouvé le texte et a renvoyé 5 et 23.

J'ai besoin de cette valeur sur toutes les lignes, donc je dois la remplir. Vous pouvez simplement cliquer avec le bouton droit pour remplir, mais vous pouvez également utiliser une syntaxe très simple et l'ajouter dans la barre de formule.

Dans ce cas, j'ai ajouté Table.FillDown et dans la chaîne de texte, j'ai indiqué quelle colonne nous voulons remplir (Header).

Maintenant, nous avons rempli cela pour toutes les lignes. Nous avons une clé pour toutes les sections d'en-tête et toutes les sections de ligne car elles partagent toutes cette valeur.

Séparer les en-têtes des lignes

L'étape suivante consiste à séparer les en-têtes des lignes. Je vais ajouter une autre colonne personnalisée et l'appeler Temp . Cette fois, nous allons faire quelque chose de plus élaboré et tirer parti de cette BufferList que j'ai créée plus tôt.

Nous utiliserons quelques fonctions de listes pour examiner chacune des positions et déterminer s'il existe une correspondance avec l'index.

Je vais commencer par une instruction if et utiliser List.Contains pour rechercher une position spécifique dans la BufferList et référencer la requête HeaderID .

Nous voulons le trouver sur toute la longueur du fichier, puis renvoyer la position de l'élément dans la liste. S'il correspond à l'index, nous avons une correspondance pour cette ligne spécifique.

Ensuite, je veux retourner une valeur pour identifier l'en-tête. Dans ce cas, je ne fais que renvoyer un H. Je vais copier la syntaxe pour ne pas avoir à tout réécrire.

Nous devons également identifier la section de ligne. Si la liste ne contient pas le HeaderID , mais le DetailID , alors nous sommes sur une section de ligne.

Si Column1 est une chaîne de texte vide, je veux qu'elle reste vide . Si ce n'est pas le cas, je veux qu'il soit null .

Cette opposition a obtenu la ligne d'en-tête et a renvoyé un H, puis elle a trouvé une ligne détaillée et a renvoyé un R. Ensuite, elle a renvoyé des 0 pour tous les éléments partagés dans cette section de ligne.

Ces blancs ou nuls sont importants car ils vous permettent de remplir. Le remplissage ne se déplacera pas sur ces cellules vides afin que nous puissions les éliminer plus tard.

Nous allons le faire dans la barre de formule et utiliser à nouveau Table.FillDown . Il veut une liste avec le nom de la colonne, qui est notre colonne Temp .

Nous avons maintenant les valeurs H et R répétées dans toute cette colonne, ce qui signifie que nous pouvons réellement séparer les en-têtes des sections détaillées.

Vous pouvez également le remplir à partir de l'interface utilisateur si vous ne souhaitez pas écrire le code. Vous pouvez simplement cliquer avec le bouton droit de la souris et sélectionner Remplir , puis Bas .

Suppression des valeurs nulles et des blancs dans Microsoft Power Query

Maintenant que nous avons ce droit, nous pouvons éliminer les choses dont nous n'avons pas besoin. Tout ce qui est nul ou contient un blanc correspond aux lignes dont nous n'avons pas besoin et qui doivent être supprimées. Nous pouvons les éliminer par filtrage.

Diviser les sections

Une fois que nous supprimons ces blancs et ces nuls, il nous reste tout ce dont nous avons besoin. À ce stade, nous pouvons simplement diviser les sections. Nous pouvons nous concentrer sur les lignes d'en-tête et les sélectionner car elles ont un espacement séparé de toutes les lignes de détail (qui ont également un espacement séparé).

Je vais ajouter une nouvelle étape dans la barre de formule qui me permet de créer un autre filtre sur cette même colonne. Dans ce cas, je vais juste garder toutes les sections d'en-tête.

Maintenant, j'ai toutes ces lignes d'en-tête ici.

Je peux sélectionner Column1 , accéder à la barre de formule, sélectionner Split column , puis diviser par positions.

Ensuite, faites en sorte que la requête de puissance elle-même comprenne celle-ci. Il proposera quelques positions. Cliquez sur OK pour accepter ces positions.

Les seules choses qui nous intéressent dans les en-têtes sont l' article et le numéro de travail .

Dans la barre de formule ici, je peux renommer ceux avec Item et Job # . Cela m'évitera une autre étape de renommage de la colonne.

Après cette étape, tout ce que j'ai à faire est de sélectionner l' élément , de sélectionner le numéro de travail et, bien sûr, de sélectionner notre clé d'en-tête . Ensuite, je supprimerai toutes les autres colonnes car je n'en ai plus besoin.

Ce sera le résultat. Nous devons encore nettoyer les valeurs et supprimer l'élément de texte et les tirets. Tout ce que nous voulons, ce sont ces valeurs intermédiaires.

Nous ouvrons donc cela et désélectionnons les tirets et les éléments.

Maintenant, tous les en-têtes sont terminés.

Nous devons également effectuer le même processus pour DetailID . Je vais devoir renommer ces étapes pour qu'il me soit plus facile d'y revenir un peu plus tard.

Nous allons revenir à la requête initiale que nous avons commencée. Nous avons commencé avec les lignes filtrées dans le volet Étapes appliquées.

Je vais copier ceci et l'ajouter à mon filtre. Cette fois, je ne sélectionne pas le H mais je sélectionne le R.

Ensuite, je sélectionnerai Column1, j'irai dans la colonne Split, je diviserai par positions, puis je demanderai à power query de le comprendre.

C'est ce que suggère PowerQuery. Essayons.

Cela a l'air plutôt bien. Même le nombre total de rangées se sépare parfaitement. Bien sûr, il y a beaucoup d'espaces parce que nous avions cette indentation.

Couper les chaînes de texte dans Microsoft Power Query

Je vais sélectionner cette première colonne, puis appuyer sur Bas + Maj pour sélectionner jusqu'à la colonne 1.10. Accédez à Transformer , sélectionnez Format , puis Rogner . Le rognage ne supprimera que les espaces en excès devant ou à la fin de la chaîne, pas entre les deux.

Ensuite, nous pouvons simplement promouvoir les en-têtes, donc je n'ai pas à taper tous les en-têtes ou les titres de ces colonnes. Dans l'étape fractionnée, j'ai renommé deux colonnes. Maintenant, bien sûr, avec 10 colonnes, c'est un peu gênant.

Nous devons également nous débarrasser de ces valeurs excessives. Parce que nous avons des totaux, je dois utiliser l'une de ces trois dernières colonnes car ce sont les seules lignes qui ont des valeurs supplémentaires quelque part entre les deux. Ensuite, nous désélectionnerons ces blancs, tirets et textes.

Ensuite, je supprimerai les colonnes inutiles afin qu'il ne reste qu'un tableau avec uniquement les en-têtes et uniquement les détails. Nous avons besoin d'une clé pour réunir à nouveau ces sections.

Pour cela, nous pouvons utiliser une auto-fusion afin de pouvoir fusionner la table avec elle-même pour rassembler ces informations. Dans l' onglet Accueil , sélectionnez Fusionner , puis sélectionnez Colonne 5 et la même requête.

Au lieu de AllDetails, je veux AllHeaders comme table initiale avec laquelle je veux fusionner.

Cela a ramené toutes les informations de la table d'en-tête avec une seule ligne pour chaque article et chaque numéro de travail.

Nous avons utilisé une clé pour fusionner avec les lignes de détail. Si j'appuie sur le côté dans l'espace blanc ici, nous verrons un aperçu de toutes les lignes appartenant à l'en-tête 5.

Nous supprimerons la dernière colonne ici, puis nous aurons fini de corriger la largeur de colonne fixe mixte dans la requête Microsoft Power.

Conclusion

Dans ce didacticiel, nous avons trouvé un moyen de résoudre les problèmes de largeur de colonne fixe mixte à l'aide de Microsoft Power Query. Si vous avez apprécié le contenu couvert dans ce didacticiel particulier, n'oubliez pas de vous abonner à la chaîne de télévision LuckyTemplates.

Nous avons une énorme quantité de contenu qui sort tout le temps de moi-même et d'une gamme de créateurs de contenu, qui se consacrent tous à améliorer la façon dont vous utilisez LuckyTemplates et Power Platform.

Mélisse