Résumé
Découvrez quelques problèmes de formatage courants associés aux fichiers de grand livre général et comment automatiser le processus de formatage à l'aide de Power Query d'Excel.
Dans cet article, nous utiliserons l'exportation du Journal de QuickBooks Desktop, cependant, bon nombre des étapes suivies seront applicables à d'autres exportations de logiciels de comptabilité.
Cas d'utilisation
Si vous avez de nombreux fichiers de grand livre général provenant du même logiciel de comptabilité qui sont tous dans le même format et nécessitent une transformation avant ingestion dans MindBridge, alors Power Query d'Excel pourrait être l'outil parfait pour vous.
Une fois qu'un fichier a été transformé en un format ingérable et que les étapes suivies ont été enregistrées, vous pourrez transformer instantanément n'importe quel fichier dans le même format !
Le problème
Ci-dessous un exemple du formatage d'un rapport de Journal de QuickBooks Desktop, tiré de notre guide d'extraction.
Les problèmes empêchant ce fichier d'être ingéré par MindBridge sont :
- Les données sont regroupées par transaction ; les colonnes Trans#, Type, Entré/Dernière modification, Dernière modification par, Date et Num doivent avoir leurs données remplies jusqu'à chaque rangée de la transaction.
- Trans# n'est pas un champ obligatoire dans QuickBooks, donc toutes les transactions n'ont pas un Trans#.
- Le numéro de compte et le nom occupent le même champ.
- Il y a des rangées superflues, telles que les totaux.
La solution
- Remplissez les rangées vides avec les données pertinentes de chaque colonne en utilisant la fonction Remplir vers le bas.
- Lors du remplissage des données, assurez-vous que les champs Trans# vides (par exemple, les cellules B5 et B27) sont maintenus et non remplis avec des données incorrectes en utilisant une Colonne personnalisée.
- Divisez le numéro de compte et le nom en colonnes séparées en utilisant la fonction Diviser la colonne par délimiteur.
- Filtrez les blancs sur une colonne qui contient des données sur chaque rangée d'entrée.
Obtenir et transformer les données
Pour commencer à transformer les données, nous devons d'abord importer les données dans Power Query. Il existe plusieurs façons de le faire en fonction du type de fichier.
- Ouvrez soit une nouvelle feuille de calcul, soit votre fichier de grand livre général dans Excel.
- Accédez au ruban Données en haut. Dans la section Obtenir et transformer les données, vous trouverez plusieurs options pour obtenir les données en fonction de si vos données sont dans votre feuille de calcul ouverte ou dans un fichier séparé.
- Sélectionnez la méthode appropriée pour les données que vous avez. Dans cet exemple, nous sélectionnerons À partir de Table/Plage puisque nos données sont dans notre feuille de calcul ouverte. Sélectionnez une cellule dans vos données et Excel tentera alors de détecter les données présentes et de les convertir en tableau. Veillez à ce que toutes vos données soient dans ce tableau et redimensionnez-le 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 de Texte/CSV et sélectionnez Transformer les données dans la fenêtre d'aperçu qui s'affiche.
- Maintenant que les données ont été chargées dans Power Query, nous pouvons commencer le processus de transformation. Le long des rubans supérieurs, il existe de nombreuses façons de manipuler les données. Dans le volet droit intitulé Paramètres de la requête, nous pouvons voir 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 peut être visualisée dans ce volet. Ces étapes enregistrées sont le principe derrière l'automatisation.
- Nous pouvons nommer notre requête dans la boîte Nom. Vous verrez que quelques étapes ont été automatiquement appliquées. L'étape Changer le type a été appliquée à toutes les colonnes ; Excel essaie essentiellement de deviner quelles données se trouvent dans chaque colonne et leur a attribué un type.
Nous devrons supprimer cette étape car nous voulons avoir l'option de sélectionner les types de données manuellement plutôt que de les faire attribuer automatiquement. Pour ce faire, sélectionnez l'icône de croix à côté de l'étape.
Nous sommes maintenant prêts à commencer à manipuler les données !
Remplir les données en utilisant remplir vers le bas
Nous pouvons commencer à transformer les données en remplissant les rangées vides dans les colonnes suivantes :
- Trans#
- Type
- Entré/Dernière modification
- Dernière modification par
- Date
- Num
Dans cet exemple, nous savons que la Date est toujours présente sur la première rangée d'un groupe de transactions, cependant Trans# peut parfois être vide.
- 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 rangées où le numéro de transaction est vide. Accédez au ruban Ajouter une colonne et sélectionnez Créer une colonne personnalisée. Lorsque la fenêtre de colonne personnalisée s'affiche, nous la nommerons ID de transaction.
-
if [#"Trans #"] = null and [Date] <> null then "@" else
if [#"Trans #"] <> null and [Date] <> null then [#"Trans #"] else nullC'est la formule que nous utiliserons pour créer notre colonne personnalisée.
La logique derrière cette formule est de vérifier une rangée pour les données présentes dans les colonnes Trans# et Date. Il est important 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 n'est pas vide, nous voulons produire un caractère spécial, dans ce cas un symbole "@". Si les cellules Trans# et Date contiennent toutes deux des valeurs, nous produisons la valeur dans la cellule Trans#. Si les cellules Trans# et Date sont toutes deux vides, nous produisons null.
- La section Colonnes disponibles à droite vous permet d'insérer des noms de colonnes dans la boîte Formule de colonne personnalisée pour créer facilement la formule personnalisée en référence à vos propres données. Le créateur de colonne personnalisée détectera automatiquement les erreurs de syntaxe pour vous aider à trouver la bonne solution. Une fois la formule remplie, sélectionnez Ok pour générer votre nouvelle colonne personnalisée.
- La colonne personnalisée ID de transaction a maintenant été créée, remplissant soit le numéro de transaction, soit un caractère spécial sur la première ligne de chaque transaction. Faites un clic droit sur l'en-tête de la colonne ID de transaction et sélectionnez Remplir>Vers le bas pour remplir les valeurs nulles avec les valeurs ci-dessus.
- Nous pouvons maintenant utiliser Remplacer les valeurs pour trouver ces caractères spéciaux, dans ce cas "@", et les remplacer par null. Cela préservera ces rangées avec des ID de transaction vides tout en remplissant les données nécessaires.
- Avec cette logique en place, nous pouvons maintenant créer des colonnes personnalisées pour :
- Type
- Entré/Dernière modification
- Dernière modification par
- Nous pouvons ouvrir Créer une colonne personnalisée à nouveau et copier-coller la même formule, en substituant les colonnes appropriées.
Diviser les colonnes par délimiteur
Ensuite, nous pouvons résoudre 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 Diviser les colonnes par délimiteur dans Power Query, nous pouvons facilement diviser ces valeurs en deux colonnes distinctes.
- Faites un clic droit sur l'en-tête de la colonne et sélectionnez Diviser la colonne > Par délimiteur.
Cela fera apparaître une fenêtre avec quelques options. Power Query essaiera de détecter automatiquement le délimiteur et proposera un délimiteur suggéré. S'il est incorrect, vous pouvez sélectionner un délimiteur différent dans le menu ou entrer un délimiteur personnalisé. Vous pouvez également sélectionner la position et l'occurrence de la division dans la section Diviser à.
- Sélectionnez OK une fois que vous êtes satisfait de la configuration.
Power Query divisera la colonne en colonnes distinctes de numéro de compte et de nom de compte. - Vous remarquerez que Power Query essaie d'ajouter une étape Changer le type qui détectera le type de données dans les nouvelles colonnes et essaiera de leur attribuer automatiquement un type.
Encore une fois, nous voudrons supprimer cette étape car elle peut entraîner des problèmes avec les numéros de compte qui ont des zéros initiaux. - 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 ID du compte et Description du compte afin de correspondre aux colonnes de MindBridge.
Nettoyer les données
Avec les changements majeurs maintenant terminés, nous pouvons nous concentrer sur des tâches plus petites telles que réorganiser les colonnes, supprimer les données superflues, changer les types de données et s'assurer que les données sont conformes aux normes permettant leur importation dans MindBridge.
- Quelques colonnes ont été remplacées par les colonnes personnalisées créées plus tôt dans le processus. Vous pouvez supprimer n'importe laquelle de ces colonnes en faisant un clic droit sur l'En-tête de colonne et en sélectionnant Supprimer :
- Trans#
- Type
- Entré/Dernière modification
- Dernière modification par
- Date
- Num
- 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 à la position souhaitée.
- Vous pouvez changer le type de données dans chaque colonne en sélectionnant l'icône Type dans chaque en-tête de colonne. Il est important de s'assurer que...
- Les colonnes Date utilisent le format Date
- La colonne Date d'entrée utilise un format Date/Heure
- Les colonnes Crédit/Débit/Montant utilisent un format Nombre décimal
- Nous recommandons de maintenir la colonne ID de transaction en tant que texte, car la convertir en format numérique supprimera tous les zéros initiaux dans l'ID.
- Filtrez les blancs dans la colonne ID du compte pour vous assurer que les rangées contenant des données superflues, telles que les totaux, sont supprimées.
- Utilisez à nouveau l'outil Remplacer les valeurs pour remplacer les valeurs "null" dans Débits/Crédits par "0".
Charger les données et utilisation future
Une fois que les données sont dans le bon format et que les étapes ont été enregistrées, nous pouvons fermer et charger les données dans une feuille Excel.
Sélectionnez Fermer et charger en haut à gauche. Une nouvelle feuille sera créée avec un tableau rempli des données formatées. Vous pouvez maintenant copier et coller les données dans un nouveau carnet de travail et les importer dans MindBridge.
Avec la requête maintenant créée, vous pouvez utiliser les étapes générées pour tout ensemble de données qui est dans le même format, c'est-à-dire qu'il a les mêmes noms de colonnes, position des colonnes et nombre de colonnes. Il y a 2 façons de charger un nouvel ensemble de données dans la requête :
- Si vous avez utilisé Obtenir des données : À partir de Table/Plage, supprimez simplement les anciennes données du tableau et collez de nouvelles données. Une fois que vos nouvelles données sont en place, accédez au ruban des données et sélectionnez Actualiser tout.
- Si vous avez utilisé Obtenir des données : À partir de Fichier, modifiez la source de la requête à partir du volet Étapes appliquées :
Autre chose en tête ? Discutez avec nous ou soumettez une demande pour obtenir de l'aide supplémentaire.