Ajout de plusieurs feuilles dans Excel à LuckyTemplates

Ce didacticiel explique comment importer et ouvrir un fichier Excel avec plusieurs feuilles dans une table LuckyTemplates.

Ce fichier Excel de données sera utilisé pour ce tutoriel.

Le fichier contient 60 feuilles de données avec la même mise en page.

L'objectif de ce didacticiel est de charger toutes les feuilles Excel dans LuckyTemplates et de récupérer les informations Bond dans un seul tableau. Vous devez obtenir le ticker de sécurité et l'ajouter dans une colonne séparée. Ensuite, ajoutez-les tous ensemble dans un seul tableau qui exploite l'interface utilisateur pour générer le code dont vous avez besoin.

Table des matières

• Importation des feuilles Excel dans LuckyTemplates
• Faire ressortir le ticker de sécurité d'Excel à LuckyTemplates
• Suppression des lignes vides
• Dupliquer une requête
• Affectation de types de données aux colonnes
• Ajout d'une colonne de téléscripteur de sécurité d'Excel à LuckyTemplates
• Création d'une colonne personnalisée pour la requête Bond
• Conclusion

Importation des feuilles Excel dans LuckyTemplates

La première chose à faire est d'ouvrir l' éditeur Power Query et de créer un paramètre pour stocker l'emplacement du fichier à l'aide de l' option Gérer les paramètres .

Dans la zone Gérer les paramètres, cliquez sur Nouveau et saisissez FileLocation comme nom de paramètre. Ensuite, définissez le Type de données sur Texte et les Valeurs suggérées sur N'importe quelle valeur . Copiez le chemin d'accès au fichier Excel et collez-le dans les champs Valeur actuelle.

Après avoir appuyé sur OK, cliquez sur Nouvelle source et sélectionnez Classeur Excel .

Ensuite, sélectionnez et ouvrez le fichier Excel. Une fois cela fait, l'écran du navigateur s'ouvrira et listera toutes les feuilles du fichier Excel.

Sélectionnez la première feuille et appuyez sur OK. Après cela, vous verrez le tableau dans Power Query Editor.

La prochaine chose à faire est de changer le chemin du fichier codé en dur dans le paramètre file. Ouvrez la fenêtre de l'éditeur avancé et remplacez le chemin d'accès au fichier dans le code source par FileLocation .

Il est préférable d'avoir vos données dans des tableaux Excel car les limites ou la plage de données sont définies avant de les importer dans Power Query . Étant donné que le fichier contient des feuilles et pas de tableaux, il risque d'apporter des colonnes vides et des lignes vides, vous devez donc trier cela.

Faire ressortir le ticker de sécurité d'Excel à LuckyTemplates

Le fait de savoir que la mise en page des feuilles est fixe aide à créer une solution, en particulier si vous souhaitez optimiser et utiliser l'interface utilisateur pour générer le code. Par exemple, une exigence consiste à ajouter une colonne contenant le symbole de sécurité. Si vous regardez les données, vous pouvez voir le ticker.

L'interface utilisateur peut vous aider à extraire la valeur du ticker. Cliquez avec le bouton droit sur la cellule et sélectionnez Ajouter en tant que nouvelle requête .

Dans la barre de formule, vous verrez que le nom de la table est suivi d'un numéro de ligne de base zéro entre accolades. Les accolades sont appelées opérateurs d'index de position . Vous verrez également le nom du champ entouré de crochets appelés opérateurs d'accès au champ .

Avec ces codes, vous pouvez maintenant extraire la valeur. Revenez à la table Bond et obtenez les données Bond. Tout d'abord, supprimez les huit premières lignes. Cliquez sur Supprimer les lignes et sélectionnez Supprimer les lignes supérieures .

Ensuite, saisissez 8 dans le champ Nombre de lignes et appuyez sur OK.

Ensuite, cliquez sur Utiliser la première ligne comme en-têtes pour définir les en-têtes.

Une fois cela fait, la table Bond ressemblera maintenant à ceci.

Juste en dessous de l'en-tête, vous verrez une fine ligne qui représente la qualité de la colonne. À partir de là, vous pouvez voir qu'il y a un certain nombre de blancs dans les colonnes. Cela signifie que le fichier a apporté beaucoup de lignes vides.

Suppression des lignes vides

Pour supprimer les lignes vides, cliquez sur Supprimer les lignes et sélectionnez Supprimer les lignes vides .

Cette transformation génère cette syntaxe :

Record.FieldValues ​​obtient toutes les valeurs de la ligne actuelle de la table sous forme de liste. List.RemoveMatchingItems supprime toutes les valeurs de la première liste qui ont une correspondance dans la seconde liste. La deuxième liste ne contient qu'une chaîne de texte vide ou un null. Ce sont les valeurs qui seront exclues de la première liste.

Si toutes les chaînes de texte vides et nulles ont été supprimées de la liste avec les valeurs de champ d'enregistrement, la liste doit être vide et List.IsEmpty prendra la valeur True. Ensuite, Table.SelectRows conservera les Trues.

Vous ne devriez pas vous retrouver avec un tableau avec uniquement des lignes vides. C'est pourquoi le mot-clé not est ajouté avant List.IsEmpty . Cela renvoie ensuite une table contenant des lignes non vides.

Outre les lignes vides, vous devez également supprimer les colonnes vides. Mais avant cela, jetez un œil à ce que génère Power Query lorsque vous supprimez une colonne. Sélectionnez la quatrième colonne et cliquez sur Supprimer les colonnes .

Après avoir effectué la transformation, vous verrez cette syntaxe dans la barre de formule.

Il appelle la fonction Table.RemoveColumns puis référence et transmet l'étape précédente dans le volet Étapes appliquées comme premier argument. La transformation transmet ensuite une liste contenant le nom de colonne des colonnes que vous souhaitez supprimer.

Dupliquer une requête

Maintenant, dupliquez la requête et sélectionnez l'étape Promouvoir les en-têtes dans le volet Étapes appliquées. Ensuite, faites un clic droit sur cette étape et sélectionnez Supprimer jusqu'à la fin .

N'oubliez pas que vous pouvez utiliser l'opérateur d'index de position pour passer un numéro de ligne de base zéro entre accolades. Ne conservez donc que la première ligne en saisissant 0 entre deux accolades dans la barre de formule.

Une fois cela fait, la table Bond ressemblera à ceci.

À partir de là, réutilisez une partie de la logique créée par l'étape Suppression des lignes vides pour transformer l'enregistrement en liste et supprimer la valeur nulle. Revenez à la requête Bond et sélectionnez l'étape Supprimer les lignes vides. Ensuite, copiez ce code M.

Revenez à la requête dupliquée et collez le code dans la barre de formule. Ensuite, arrangez quelques chaînes pour que la formule ressemble à ceci.

Vous avez maintenant créé une liste avec les noms de colonnes que vous souhaitez conserver. Renommez la requête Column Names .

Ensuite, revenez à la requête Bond. Puisque vous avez créé une requête qui contient toutes les colonnes que vous souhaitez conserver, vous devez répertorier toutes les colonnes que vous souhaitez exclure dans la fonction Table.RemoveColumns .

Modifiez {Column4} avec la même syntaxe que celle copiée à partir des étapes de suppression des lignes vides. Remplacez également {“”, null} par ColumnNames .

Ensuite, vous devez transmettre une liste avec les noms de colonnes réels de la table Bond. Remplacez Record.FieldValues(_) par Table.ColumnNames() . Entrez # "Lignes vides supprimées" entre parenthèses pour passer un argument de référence de table.

Affectation de types de données aux colonnes

La prochaine chose à faire est d'affecter les types de données appropriés aux colonnes. Pour la colonne Date, cliquez sur l'icône à côté de l'en-tête et sélectionnez Date.

Pour les colonnes PX_LAST et YLD_YTM_MID, sélectionnez le type de données Nombre décimal.

Avec ces trois requêtes, vous avez créé tous les blocs de construction que vous pouvez utiliser pour concevoir une solution qui traite toutes les feuilles du fichier Excel . Pour cela, vous devez dupliquer la requête Bond et supprimer toutes les étapes sauf l'étape Source dans le volet Étapes appliquées.

Ajout d'une colonne de téléscripteur de sécurité d'Excel à LuckyTemplates

À l'étape Source, vous pouvez voir toutes les données contenues dans le fichier Excel. Au lieu de créer une requête distincte pour chaque feuille, vous pouvez utiliser la requête Bond et transformer les tables imbriquées dans la colonne Données.

Tout d'abord, ajoutez le ticker de sécurité. Si vous cliquez sur l'espace blanc à l'intérieur de la cellule de n'importe quel tableau, vous verrez un aperçu du contenu de ce tableau.

Vous devez créer une logique à l'aide d'une instruction pour obtenir le ticker de sécurité. Accédez à la requête Column2 et copiez l'adresse du ticker de sécurité à partir de la barre de formule.

Ensuite, revenez à la requête Bond et ajoutez une colonne personnalisée.

Nommez la colonne Security Ticker et écrivez le code M suivant.

La formule a une instruction IF indiquant que si le mot Security est trouvé dans Column1, il fournira la valeur de la cellule de Column2 qui lui est adjacente. Sinon, une valeur nulle sera fournie.

Après avoir appuyé sur OK, une nouvelle colonne avec les tickers de sécurité sera ajoutée au tableau.

Cliquez sur le bouton déroulant de la colonne Security Ticker et désélectionnez null pour supprimer tous les nulls de la colonne.

Après cela, il vous restera toutes les informations sur les obligations de toutes les feuilles. Tout ce que vous avez à faire est de répéter les transformations effectuées dans la requête Bond(2) et de les appliquer aux tables imbriquées dans la colonne Données.

Création d'une colonne personnalisée pour la requête Bond

Accédez à la requête Bond(2), ouvrez la fenêtre de l'éditeur avancé et copiez le code suivant :

Ensuite, revenez à la requête Bond et créez une autre colonne personnalisée. Étant donné que vous devez appliquer plusieurs transformations sur plusieurs étapes, vous devez utiliser une instruction let . Alors, saisissez let et collez le code de l'éditeur avancé.

Ensuite, changez Bond_Sheet en Data pour transformer le tableau dans la colonne Data.

Après avoir appuyé sur OK, une nouvelle colonne sera ajoutée dans le tableau.

Si vous cliquez sur l'espace blanc à l'intérieur de n'importe quelle cellule de cette colonne, vous verrez les données de la requête Bond(2).

Tout ce dont vous avez besoin se trouve dans les deux dernières colonnes de la requête Bond. Sélectionnez donc les colonnes Security Ticker et Custom et cliquez sur Remove Other Columns dans le ruban Remove Columns.

Développez la colonne Personnalisé et décochez la case Utiliser le nom d'origine comme préfixe.

Une fois cela fait, validez le tableau. Sous le tableau, sélectionnez l' option Profilage de colonne basé sur l'ensemble de données complet .

Après cette transformation, une erreur se produira dans la colonne Données.

Examinez donc l'erreur en cliquant sur la colonne Données et en sélectionnant Conserver les erreurs dans le ruban Conserver les lignes.

Si vous cliquez sur la valeur Erreur dans la colonne Données, vous pouvez lire le message d'erreur ci-dessous :

Pour résoudre les problèmes, supprimez d'abord l'étape Conserver les erreurs. Cliquez sur la colonne Données et sélectionnez Supprimer les erreurs dans le ruban Supprimer les lignes.

Ensuite, redéfinissez l'option de profilage de colonne sur les 1 000 premières lignes. Et c'est tout!

Conclusion

Cette solution basée sur l'interface utilisateur peut vous aider à ajouter plusieurs feuilles d'un fichier Excel à LuckyTemplates. Au lieu de créer 60 requêtes distinctes et d'effectuer toutes les transformations à plusieurs reprises, cette solution peut créer une seule requête qui exécute toutes les transformations. Utilisez et optimisez cette solution pour créer un excellent rapport de données .

Mélisse