Contact: 514-605-7112 / info@lecfomasque.com

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

Publié le : 20 avril 2020

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!

 

Formation recommandée

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.

 


NOTRE OFFRE DE FORMATIONS


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et ils sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Découvrez quelles formations vous conviennent

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

  • réponse Nico ,

    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.

    • réponse Sophie Marchand ,

      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

    • réponse Benoit ,

      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 ! 🙂

      • réponse Sophie Marchand ,

        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