Résumé

Découvrez quelques problèmes de formatage courants associés aux fichiers du grand livre et comment automatiser le processus de formatage à l’aide d’Excel Power Query.

Dans cet article, nous utiliserons l’exportation du journal de QuickBooks Desktop, mais la plupart des étapes sont également applicables aux exportations d’autres logiciels comptables.

Cas d’utilisation

Si vous disposez de nombreux fichiers de grand livre provenant du même logiciel de comptabilité, tous au même format et devant être convertis avant d’être traités dans MindBridge, Excel Power Query est l’outil idéal pour vous.

Une fois que vous avez converti un fichier en un format traitable et enregistré les étapes, vous pouvez convertir n’importe quel fichier dans le même format instantanément !

Le problème

Vous trouverez ci-dessous un exemple de formatage d’un rapport de journal QuickBooks Desktop, tiré de notre guide d’extraction.

Les problèmes empêchant le traitement de ce fichier par MindBridge sont les suivants :

1. Les données sont regroupées par transaction ; les colonnes Trans#, Type, Entered/Last Modified, Last Modified by, Date et Num doivent être remplies à chaque ligne de la transaction.
2. Trans# n’est pas un champ obligatoire dans QuickBooks, c’est pourquoi toutes les transactions n’ont pas de numéro Trans#.
3. Le nom et le numéro de compte occupent le même champ.
4. Certaines lignes sont superflues, comme les totaux.

La solution

1. Remplissez les lignes vides avec les données pertinentes de chaque colonne en utilisant la fonction Remplir > Vers le bas.
2. Lorsque vous remplissez les données, assurez-vous que les champs vides Trans# (par exemple, les cellules B5 et B27) sont conservés et ne sont pas remplis avec des données incorrectes à l’aide d’une Colonne personnalisée.
3. Fractionnez le nom et le numéro de compte en deux colonnes distinctes à l’aide de la fonction Fractionner par délimiteur.
4. Filtrez les champs vides sur les colonnes qui contiennent des données sur chaque ligne d’entrée.
   
   

Obtenir et convertir des données

Afin de commencer à convertir les données, nous devons d’abord les importer dans Power Query. Il y a plusieurs façons de procéder, selon le type de fichier.

1. Ouvrez une nouvelle feuille de calcul ou votre fichier de grand livre dans Excel.
2. Accédez à l’onglet Données en haut de l’écran. Vous trouverez dans la section Obtenir des données un certain nombre d’options pour obtenir les données, selon qu’elles se trouvent dans la feuille de calcul ouverte ou dans un fichier séparé.
3. Sélectionnez la méthode appropriée pour les données dont vous disposez. Dans cet exemple, nous allons sélectionner À partir de Tableau ou d’une Plage puisque nos données se trouvent dans notre feuille de calcul ouverte. Sélectionnez une cellule dans vos données. Excel tentera alors de détecter les données présentes et de les convertir en un tableau. Assurez-vous que toutes vos données se trouvent dans ce tableau et redimensionnez-les de manière appropriée dans la fenêtre contextuelle Créer un tableau.
   Si vous souhaitez importer un fichier .txt ou .csv, sélectionnez simplement À partir d’un fichier texte/CSV et sélectionnez Convertir les données dans la fenêtre d’aperçu qui s’affiche.
   
   
   
4. Maintenant que les données ont été chargées dans Power Query, nous pouvons commencer le processus de conversion. Il existe de nombreuses façons de manipuler les données le long des rubans supérieurs. Dans le volet de droite intitulé Paramètres d’une requête se trouvent les Propriétés  et les Étapes appliquées. Dans Power Query, lorsque vous effectuez une action pour manipuler les données, une étape est automatiquement créée et affichée dans ce volet. Ces étapes enregistrées sont le principe de l’automatisation.
5. Nous pouvons nommer notre requête dans le champ Nom. Vous verrez que quelques étapes ont été automatiquement appliquées. L’étape Type modifié a été appliquée à toutes les colonnes ; Excel essaie essentiellement de déterminer quelles données se trouvent dans chaque colonne et leur attribue un type.
   Nous devrons supprimer cette étape car nous voulons avoir la possibilité de sélectionner les types de données manuellement plutôt que de les voir attribués automatiquement. Pour ce faire, sélectionnez l’icône de croix à côté de l’étape.
   
   
   Nous sommes prêts à commencer la manipulation des données !
   
   

Renseigner les données à l’aide de la fonction Remplir > Vers le bas

Nous pouvons commencer à convertir les données en remplissant les lignes vides dans les colonnes suivantes :

• Numéro de transaction
• Type
• Saisi/dernière modification
• Dernière modification par
• Date
• Numéro

Dans cet exemple, nous savons que la date est toujours présente sur la première ligne d’un groupe de transactions, mais que Trans# peut parfois être vide.

1. Commencez par créer une colonne personnalisée pour le numéro de transaction, en tenant compte du fait que nous devons préserver les lignes où le numéro de transaction est vide. Accédez au ruban Ajouter une colonne et sélectionnez Colonne personnalisée. Lorsque la fenêtre s’affiche, nous la nommons Transaction ID.
   
   
   

2. if [#"Trans #"] = null and [Date] <> null then "@" else
   if [#"Trans #"] <> null and [Date] <> null then [#"Trans #"] else null

   Il s’agit de la formule que nous utilisons pour créer notre colonne personnalisée.
   Cette formule vise à vérifier qu’une ligne ne contient pas de données dans les colonnes Trans# et Date. Il convient de noter que la valeur « null » représente une cellule vide dans Power Query. Si la cellule du numéro de transaction est vide, et que la date ne l’est pas, nous générons un caractère spécial, ici le symbole « @ ». Si les cellules Trans# et Date contiennent toutes deux des valeurs, nous affichons la valeur de la cellule Trans#. Si les cellules Trans# et Date sont vides, nous affichons null.

   
   
   
3. La section Colonnes disponibles sur la droite vous permet d’insérer des noms de colonnes dans le champ Formule de colonne personnalisée afin de créer facilement une formule personnalisée faisant référence à vos propres données. Le créateur de colonne personnalisée détectera automatiquement toute erreur de syntaxe pour vous aider à trouver la bonne solution. Une fois la formule saisie, sélectionnez Ok pour générer votre nouvelle colonne personnalisée.
4. La colonne personnalisée Transaction ID est maintenant créée, et indique le numéro de transaction ou un caractère spécial sur la première ligne de chaque transaction. Réalisez un clic droit sur l’en-tête de la colonne Transaction ID et sélectionnez Remplir > Vers le bas pour remplir les valeurs nulles avec les valeurs ci-dessus.
5. Nous pouvons maintenant utiliser Remplacer les valeurs pour trouver ces caractères spéciaux, ici « @ », et les remplacer par null. Cela permettra de préserver les lignes avec des ID de transaction vides tout en remplissant les données nécessaires.
   
   
   
6. En suivant cette logique, nous pouvons désormais créer des colonnes personnalisées pour les valeurs :
   • Type
   • Saisi/dernière modification
   • Dernière modification par
7. Nous pouvons à nouveau ouvrir Colonne personnalisée et copier-coller la même formule, en substituant les colonnes appropriées.
   
   

Diviser les colonnes par délimiteur

Nous pouvons maintenant gérer le fait que le numéro de compte et le nom du compte occupent la même colonne. Dans cet exemple, le numéro et le nom sont séparés par un délimiteur. En utilisant la fonction Fractionner la colonne > Par délimiteur de Power Query, nous pouvons facilement séparer ces valeurs en deux colonnes distinctes.

1. Réalisez un clic droit sur l’en-tête de la colonne et sélectionnez Fractionner la colonne > Par délimiteur.
   Une fenêtre comportant plusieurs options s’affiche. Power Query tente de déterminer le délimiteur automatiquement et fournit une suggestion. En cas d’erreur, vous pouvez sélectionner un autre délimiteur dans le menu ou saisir un délimiteur personnalisé. Vous pouvez également sélectionner la position et l’occurrence du fractionnement dans la section Fractionner à.
   
   
   
2. Sélectionnez OK une fois que la configuration vous convient.
   Power Query fractionne la colonne en deux colonnes distinctes : numéro de compte et nom de compte. 
3. Vous remarquerez que Power Query essaie d’ajouter une étape Type modifié, qui détecte quel type de données se trouve dans les nouvelles colonnes, et essaie de leur attribuer automatiquement un type.
   Encore une fois, il est préférable de supprimer cette étape car elle peut entraîner des problèmes avec les numéros de compte comportant des zéros en tête.
4. Nous pouvons renommer ces colonnes nouvellement créées en double-cliquant sur le nom attribué dans l’en-tête de la colonne. Dans cet exemple, elles ont été nommées Account ID et Account Description afin de correspondre aux colonnes MindBridge.
   
   

Nettoyer les données

Les modifications majeures étant terminées, nous pouvons nous concentrer sur des tâches plus modestes telles que la réorganisation des colonnes, la suppression de toute donnée superflue, la modification des types de données et la vérification de la conformité des données aux normes permettant leur importation dans MindBridge.

1. Certaines colonnes ont été remplacées par les colonnes personnalisées créées plus tôt dans le processus. Vous pouvez supprimer l’une de ces colonnes en réalisant un clic droit sur l’en-tête de colonne et en sélectionnant Supprimer :
   • Numéro de transaction
   • Type
   • Saisi/dernière modification
   • Dernière modification par
   • Date
   • Numéro 
2. Vous pouvez réorganiser les colonnes selon vos besoins en sélectionnant et en faisant glisser l’en-tête de la colonne vers la gauche ou la droite jusqu’à la position souhaitée.
3. Vous pouvez modifier le type de données dans chaque colonne en sélectionnant l’icône correspondante dans chaque en-tête. Il est important de contrôler les points suivants :
   • Les colonnes Date utilisent le format Date
   • La colonne Entered Date utilise un format Date/Heure
   • Les colonnes Credit/Debit/Amount utilisent un format Nombre décimal
   • Nous recommandons de conserver la colonne Transaction ID au format Texte, car sa conversion dans un format numérique supprime les zéros de tête de l’identifiant.
4. Filtrez les espaces vides dans la Account D pour vous assurer que les lignes contenant des données superflues, telles que les totaux, sont supprimées.
5. Utilisez à nouveau l’outil Remplacer les valeurs pour remplacer les valeurs « nulles » dans Debits/Credits par « 0 ».
   
   

Chargement des données et utilisation future

Une fois que les données sont au bon format et que les étapes ont été enregistrées, nous pouvons fermer et charger les données dans une feuille de calcul Excel.

Sélectionnez Fermer et charger dans le coin supérieur gauche. Une nouvelle feuille sera créée avec un tableau contenant les données formatées. Vous pouvez maintenant copier et coller les données dans un nouveau classeur et les importer dans MindBridge.

La requête étant maintenant créée, vous pouvez utiliser les étapes générées pour tout ensemble de données se trouvant dans le même format, c’est-à-dire avec des noms de colonnes, une position de colonnes et un nombre de colonnes identiques. Il existe deux façons de charger un nouvel ensemble de données dans la requête :

1. Si vous avez utilisé Obtenir des données : À partir de Tableau ou d’une Plage, il suffit de supprimer les anciennes données du tableau et d’y intégrer les nouvelles. Une fois que vos nouvelles données sont en place, accédez au ruban Données et sélectionnez Actualiser tout.
   
   
   
2. Si vous avez utilisé Obtenir des données : À partir d’un fichier texte/CSV, modifiez la source de la requête à partir du volet Étapes appliquées :
   
   
   

Un autre problème ?

Rejoignez la communauté pour faire part de vos réflexions et demandes de renseignements ! Vous pouvez également discuter avec nous ou soumettre une demande d’assistance supplémentaire.