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

Publié le 20 avril 2020
par Sophie Marchand M.Sc.
Données triées

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 ! Voici donc comment utiliser Power Query pour calculer les écarts entre deux fichiers.

 

Données de départ à comparer

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

 

L’objectif est donc de comparer les écarts entre ces deux fichiers.

 

Travailler avec Power Query pour calculer les écarts entre deux fichiers

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

Vous avez des sources de données volumineuses à rassembler et analyser dans Excel ? Apprenez à automatiser tout le processus d’importation, de transformation et de modélisation  des données avec nos formations sur Power Query et Power Pivot dans Excel.

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. À noter que vous n’avez pas à ajouter de calculs par la suite pour comparer les écarts entre vos deux fichiers. Tout est déjà calculé !

Écarts facturation arrondis Excel

 

Automatiser les calculs pour trouver les écarts entre deux fichiers 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 :

Excel – Introduction à Power Query et au langage M
CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

4 réflexions sur “Identifiez vos écarts de facturation en une 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

Laisser un commentaire

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

Retour en haut