Avez-vous des questions? Nous avons des réponses.

Guides et conseils sur le formatage des données : Automatiser les tâches répétitives dans Excel Power Query

  • Mise à jour

Sommaire

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

Dans cet article, nous utiliserons l’exportation de journal à partir de QuickBooks Desktop, mais bon nombre des mesures prises s’appliqueront aux autres exportations de logiciels comptables.


Cas d’utilisation

Si vous avez de nombreux fichiers du grand livre général du même logiciel comptable qui sont tous dans le même format et qui nécessitent une transformation avant d’être intégrés dans MindBridge, alors Excel Power Query pourrait être l’outil idéal pour vous.

Une fois qu’un fichier a été transformé en un format ingérable et que les mesures prises ont été enregistrées, vous pourrez transformer instantanément n’importe quel fichier dans le même format!


Le problème

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

mceclip2.png

Voici les problèmes qui empêchent MindBridge d’ingérer ce fichier :

  1. Les données sont regroupées par transaction; les colonnes Numéro de transaction, Type, Saisie/Dernière modification, Dernière modification par, Date et Num. doivent avoir leurs données remplies jusqu’à chaque ligne de la transaction.
  2. Le numéro de transaction n’est pas un champ obligatoire dans QuickBooks, donc pas toutes les transactions ont un numéro de transaction.
  3. Le numéro de compte et le nom occupent le même champ.
  4. Il y a des lignes non pertinentes, comme les totaux.

La solution

  1. Remplissez les lignes vides avec les données pertinentes de chaque colonne en utilisant la fonction Remplissage.
  2. Lorsque vous remplissez les données, assurez-vous que les champs vides Numéro de transaction (par exemple, les cellules B5 et B27) sont maintenus et ne contiennent pas de données incorrectes à l’aide d’une Colonne personnalisée.
  3. Fractionnez le numéro de compte et le nom en colonnes distinctes à l’aide de la fonction Fractionner la colonne par délimiteur.
  4. Filtrez les espaces vides d’une colonne qui contient des données sur chaque ligne d’écriture.


Obtenir et transformer des 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 selon le type de fichier.

  1. Ouvrez une nouvelle feuille de calcul ou votre fichier du grand livre général dans Excel.
  2. Naviguez jusqu’au ruban Données en haut. Dans la section Obtenir et transformer des données, vous trouverez un certain nombre d’options pour obtenir les données selon que ces dernières se trouvent dans votre feuille de calcul ouverte ou dans un fichier distinct.
  3. Sélectionnez la méthode appropriée pour les données que vous avez. Dans cet exemple, nous sélectionnerons À partir du tableau/de la plage puisque nos données se trouvent dans notre feuille de calcul ouverte. Sélectionnez une cellule dans vos données et Excel tentera ensuite de détecter les données présentes et de les convertir en tableau. Assurez-vous que toutes vos données se trouvent dans ce tableau et redimensionnez-les de façon 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 contextuelle qui s’affiche.
    Get and transform data

  4. Maintenant que les données ont été chargées dans Power Query, nous pouvons commencer le processus de transformation. Le long des rubans du haut, vous trouverez de nombreuses façons de manipuler les données. Dans le volet de droite intitulé Paramètres de 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.
  5. Nous pouvons nommer notre requête dans la case Nom. Vous verrez que quelques étapes ont été automatiquement appliquées. L’étape Modifier le type a été appliquée à toutes les colonnes; Excel tente essentiellement de deviner quelles données se trouvent dans chaque colonne et lui a attribué 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 attribuer automatiquement. Pour ce faire, sélectionnez l’icône en forme de croix à côté de l’étape.
    mceclip1.png

    Nous sommes maintenant prêts à commencer à manipuler les données!


Remplir les données à l’aide de la fonction Remplissage

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

  • Numéro de transaction
  • Type
  • Saisie/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 ligne d’un groupe de transactions, mais le numéro de transaction 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 conserver les lignes où le numéro de transaction est vide. Naviguez jusqu’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 l’appellerons Code de transaction.
    mceclip2.png

  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 utiliserons pour créer notre colonne personnalisée.
    La logique derrière cette formule est de vérifier une ligne pour les données présentes dans les colonnes Numéro de transaction 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 n’est pas vide, nous voulons créer un caractère spécial. Dans ce cas, un symbole « @ ». Si les cellules Numéro de transaction et Date contiennent toutes deux des valeurs, nous produisons la valeur dans la cellule Numéro de transaction. Si les cellules Numéro de transaction et Date sont vides, nous produisons une valeur nulle.

    mceclip2.png

  3. La section Colonnes disponibles à droite vous permet d’insérer des noms de colonne dans la case Formule de colonne personnalisée pour créer facilement la formule personnalisée faisant référence à vos propres données. Le créateur de colonne personnalisé détectera automatiquement toute erreur 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.
  4. Le Code de transaction de la colonne personnalisée a maintenant été créé, qui a rempli le numéro de transaction ou un caractère spécial sur la première ligne de chaque transaction. Cliquez avec le bouton droit de la souris sur l’en-tête de la colonne Code de transaction et sélectionnez Remplissage 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, dans ce cas « @ », et les remplacer par des valeurs nulles. Cela préservera ces lignes avec des codes de transaction vides tout en remplissant les données nécessaires.
    Fill_down.gif

  6. Avec cette logique en place, nous pouvons maintenant créer des colonnes personnalisées pour :
    • Type
    • Saisie/Dernière modification
    • Dernière modification par
  7. Nous pouvons ouvrir à nouveau Créer une colonne personnalisée et copier-coller la même formule, en remplaçant les colonnes appropriées.


Fractionner 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 Fractionner les colonnes par délimiteur dans Power Query, nous pouvons facilement fractionner ces valeurs en deux colonnes distinctes.

  1. Cliquez avec le bouton droit sur l’en-tête de colonne et sélectionnez Fractionner la colonne > Par délimiteur.
    Cela affichera une fenêtre avec quelques options. Power Query tentera de détecter le délimiteur automatiquement et de lui donner 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 fraction sous la section Fractionner à.
    mceclip0.gif

  2. Sélectionnez OK une fois que vous êtes satisfait de la configuration.
    Power Query fractionnera 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 Modifier le type qui détectera quel type de données se trouve dans les nouvelles colonnes et tentera de lui 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 au début.
  4. Nous pouvons renommer ces colonnes nouvellement créées en double-cliquant sur le nom attribué dans l’en-tête de colonne. Dans cet exemple, ils ont été nommés Identifiant du compte et Description du compte afin de correspondre aux colonnes MindBridge.


Nettoyage des données

Avec les changements majeurs maintenant terminés, nous pouvons nous concentrer sur des tâches plus petites telles que la réorganisation des colonnes, la suppression de toutes les données superflues, la modification des types de données et la vérification que les données sont conformes aux normes qui permettent de les importer dans MindBridge.

  1. 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 cliquant avec le bouton droit sur l’en-tête de colonne et en sélectionnant Supprimer :
    • Numéro de transaction
    • Type
    • Saisie/Dernière modification
    • Dernière modification par
    • Date
    • Num.
  2. Vous pouvez réorganiser les colonnes au besoin en sélectionnant l’en-tête de colonne et en le faisant glisser vers la gauche ou la droite jusqu’à la position désirée.
  3. Vous pouvez modifier 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 saisie utilise un format Date/Heure
    • Les colonnes Crédit/Débit/Montant utilisent un format de nombre décimal
    • Nous vous recommandons de conserver la colonne Code de transaction sous forme de texte, car la convertir en format numérique supprimera les zéros au début du code.
  4. Filtrez les espaces vides dans la colonne Identifiant du compte pour vous assurer que les lignes contenant des données étrangères, comme les totaux, sont supprimées.
  5. Utilisez à nouveau l’outil Remplacer les valeurs pour remplacer les valeurs « nulles » dans Débits/Crédits 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 les données dans le coin supérieur gauche. Une nouvelle feuille de calcul sera créée avec un tableau rempli avec les données formatées. Vous pouvez maintenant copier et coller les données dans un nouveau classeur et les importer dans MindBridge.

mceclip0.png

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.-à-d. qu’il a les mêmes noms de colonne, position de colonne et nombre de colonnes. 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 du tableau/de la plage, supprimez simplement les anciennes données du tableau et collez les nouvelles données dedans. Une fois vos nouvelles données en place, naviguez jusqu’au ruban de données et sélectionnez Actualiser tout.
    mceclip0.png

  2. Si vous avez utilisé Obtenir des données : À partir du fichier, modifiez la source de la requête à partir du volet Étapes appliquées :
    mceclip1.png


Avez-vous autre chose en tête? Vous pouvez également clavarder avec nous ou soumettre une demande d’aide supplémentaire.

Cet article vous a-t-il été utile?