Identifiez vos écarts de facturation en 1 minute, avec Power Query!

Publié le 20 avril 2020
par Sophie Marchand M.Sc., CPA, CGA, MVP
Données triées

Identifiez vos écarts de facturation en 1 minute, avec Power Query!

Récemment, sur le forum, quelqu’un a demandé comment faire pour comparer aisément deux sources de données de facturation pour en faire ressortir les écarts. Ça doit devenir un réflexe pour tous les usagers d’Excel. La réponse, quand vient le temps d’automatiser ce genre de tâche, c’est Power Query. Cet article vise à vous expliquer à quel point il est facile de préparer une requête qui fera le travail à votre place à tous les mois!

 

Données de départ

Deux sources de données ont été fournies. La première source est une extraction du système SAP et ressemble à ceci:

Données SAP

 

La deuxième source de données est issue du carnet de commandes et se trouve sous le format suivant:

Entry of order

 

Travailler avec Power Query

La première étape serait donc d’importer les données dans Power Query et de faire un ménage préliminaire comme supprimer les lignes (filtre sur les null) et colonnes inutiles et modifier les types de données. On cherche à ne retenir que la colonne de NoClient et la colonne de valeur à réconcilier.

Source 1 mi-parcours

 

Ensuite, on voudra regrouper les données par client, pour avoir la somme des valeurs par client.

Source1 fin parcours

 

On appliquera les mêmes étapes avec la deuxième source de données et ensuite, on fusionnera simplement côte à côte les deux sources de données, selon le no.client:

Fusion deux sources

 

On obtient alors un résultat comme le suivant:

Développé

 

Il faudra remplacer les null par des 0, de façon à pouvoir ajouter ensuite une colonne faisant la différence entre les valeurs des deux sources:

Écarts facturation arrondis

 

Une fois le résultat chargé dans Excel, il apparaîtra sous forme de tableau, ce qui permettra de filtrer rapidement les résultats. D’ailleurs, si désiré, on pourrait ajouter une dernière étape de transformation avant de charger notre résultat dans Excel, qui filtrerait les données pour exclure les différences à 0. Comme ça, le focus serait mis uniquement sur les différences à analyser.

Écarts facturation arrondis Excel

 

Automatiser la tâche avec Power Query

Ce que vous devez bien comprendre c’est qu’une fois que vous avez montré à Power Query ce que vous cherchez à obtenir comme résultat, vous n’aurez plus à refaire ces manipulations dans l’avenir. Vous n’aurez qu’à actualiser votre fichier. Ici, soit vos extractions portent toujours le même nom et sont toujours déposées au même endroit ou alors, vous effectuez votre requête sur un dossier, qui lui comprend l’ensemble des extractions mensuelles. Qui plus est, dans certains cas, vous n’aurez pas à importer des extractions mais vous pourrez vous connecter directement à la source. Peu importe, ce qu’il faut retenir, c’est qu’il ne faut plus faire ce genre de tâches manuellement. Il faut laisser Power Query faire le travail à votre place. Vous avez une plus grande valeur au niveau de l’analyse que de la manipulation de données!

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Formation complémentaire

J’ai déjà rédigé un article qui stipulait que si vous n’aviez qu’une seule formation à suivre, vous devriez choisir de vous former en Power Query (Article: Si vous deviez ne suivre qu’une formation cette année, ce serait celle-là!) car les retombées, en terme d’économies de temps sont simplement spectaculaires. Et vous le savez déjà, le temps, c’est de l’argent. Alors, c’est le temps d’automatiser vos fichiers Excel.

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Bertrand Fortin
Écrit il y a 1 an
Simplement un gros WOW.

Je suis totalement satisfait de la formation. Je ne pouvais demandé mieux.

Stephanie Lambert
Écrit il y a 2 ans
Enfin du contenu que je ne connaissais presque pas

J'ai enfin pu avoir l'expérience d'un nouvel apprenant et comme je m'y attendais, elle fut très positive. Les notions sont bien expliquées et illustrées avec des exemples concrets. Il est très utile d'avoir les mêmes tables de données pour pouvoir reproduire les exemples. Les documents pdf fournis sont clairs et bien faits.

Daniel Harvey
Écrit il y a 2 ans
J'ai adoré.

Comme introduction a l'outil, pour avoir un aperçu de ce qu'il est possible de faire, je pense que la formation est vraiment géniale.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

4 réflexions sur “Identifiez vos écarts de facturation en 1 minute, avec Power Query!”

  1. Bonjour.

    Utilisant des extractions SAP régulièrement j’ai remarqué que le nom des en-têtes de mes extractions Excel pouvaient changer d’un mois sur l’autre (une majuscule au lieu d’une minuscule par exemple) . Il fallait donc le modifier soit dans le fichier Excel pour coller à la requête ou vice versa sans quoi notre requête abouti en erreur. En effet lors de notre connexion à notre source de données, Power Query a tendance à promouvoir les en-têtes par défaut. Pour éviter le problème lié au changements de nom d’en-têtes dans les fichiers sources issus d’extractions SAP futures il suffit alors de supprimer l’étape « promouvoir les en-têtes » et de renommer chaque colonne par le nom souhaité qui du coup est statique quelque soit le nom d’en-tête du fichier d’extraction, puis de supprimer la première ligne.
    On évite ainsi d’avoir à trifouiller dans l’extraction ou la requête au moment où nous avons le plus besoin de nos données 🙂
    Bonne journée.

    1. Bonjour Nico,

      En effet, du moment qu’une des étapes de transformation utilise un nom de colonne, celui-ci est stocké dans le code, en texte. Donc si le nom change, la requête brise. Il faut être vigilant avec toutes les étapes de transformation qui se basent sur des noms de colonne.

      Au plaisir,

      Sophie

  2. Bonjour !
    C’est un truc que j’utilise bcp dans mes analyses de ventes, mais je ne comprend pas tout à fait la formule que vous avez utilisée ici ?

    Dans le dernier tableau (celui des résultats), comment se fait-il que la différence entre les 2 colonnes sur les lignes 4 et 12 donnent un résultat autre que 0?
    Je sais qu’il se peut qu’il y des fois des variances de quelques sous à l’affichage dû au format de données utilisé, mais ici on parle d’une différence vraiment flagrante ?

    Merci et continuez votre beau travail, j’ai énormément progressé grâce à vous ! 🙂

    1. Bonjour Benoît,

      J’ai fait le tutoriel à partir des données fournies par la personne qui a posé la question sur le forum. De toute évidence, ses données avaient plusieurs décimales. Comme je n’ai pas modifié l’affichage des données des écarts, ce que l’on voit comme écarts, ce sont des micro écarts au niveau des décimales. Si vous regardez bien, on voit le -15 à la fin. Donc, il faut placer 15 0 devant. Mais si je les avais arrondis, on n’aurait pas obtenu d’écarts du tout.

      Au plaisir,

      Sophie

Répondre à Nico Annuler la réponse

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top