Power Query : Convertir des montants cumulatifs en montants mensuels

Publié le 26 janvier 2017
par Sophie Marchand M.Sc.
Blogue Power Query

Il y a quelques jours, François a demandé comment convertir des montants cumulatifs en montants mensuels sur notre forum et cet article vise à répondre précisément à cette question.

Question forum François

 

Plutôt que d’utiliser le DAX, je lui ai recommandé d’utiliser Power Query. L’article qui suit vise à montrer comment convertir des montants cumulatifs en montant mensuels aisément avec Power Query.

 

Données initiales

Imaginons une base de données qui fournit des données de ventes cumulatives, par mois et par produit,comme l’extrait ci-dessous.

Données initiales Power Query

 

Importation de la table dans Power Query

Dans un premier temps, j’ai simplement importé la table dans Power Query. Ici, j’ai également renommé la colonne Montant, Montant cumulatif.

Ventes cumulatives Power Query

 

Création d’une deuxième requête qui réfère à la première

Ensuite, j’ai cliqué sur le bouton droit de ma souris sur cette première requête et j’ai choisi l’option “Reference” pour créer une deuxième requête, qui ne fait que ramener les résultats de la première.

Reference Power Query

 

Vous devez analyser de grandes quantités de données et les présenter dans des rapports et tableaux de bord, avec des indicateurs de performance pertinents ? Développez vos compétences avec nos formations en Power BI.

Formations Power BI disponibles en anglais

 

Ajout d’une colonne personnalisée

Dans cette deuxième requête, j’ai ajouté une colonne personnalisée qui ne fait qu’additionner 1 à la colonne mois. Ainsi, vis-à-vis le mois 1, on va retrouver un 2 et vis-à-vis le mois 2, on va retrouver un 3, et ainsi de suite.

Power Query Étape intermédiaire

 

Fusion des deux requêtes

Pour créer la dernière requête, j’ai utilisé l’option “Merge Queries as New” (Fusionner des requêtes comme une nouvelle requête).

Merge queries as new

 

J’ai fusionné côte-à-côte mes deux première requêtes, en faisant correspondre les colonnes “Produit” de chaque requête et la colonne “Mois” de la première requête avec la colonne “Custom” de la deuxième requête, i.e. la colonne personnalisée calculée au point précédent.

Fusion de requête Power Query

 

Extraction du montant du mois précédent

J’ai ensuite extrait la colonne montant de cette nouvelle colonne fusionnée.

Power Query Fusion

 

J’ai donc obtenu la table suivante:

PowerQuery résultat intermédiaire

 

Ajout d’une colonne personnalisée

Afin d’obtenir les montants mensuels, j’ai simplement ajouté une colonne personnalisée, qui fait la différence entre le montant cumulatif et le montant cumulatif du mois précédent.

Colonne personnalisée Power Query

 

Conversion des null

Ce faisant, j’ai obtenu les montants mensuels pour toutes les lignes de ma table, sauf pour les lignes de données où il n’y avait pas de montant au mois précédent. Pour ces lignes de données, j’ai obtenu des valeurs null.

Power Query étape intermédiaire 2

 

J’ai donc ajouté une colonne conditionnelle pour remplacer les null par les montants cumulatifs initiaux (puisque le premier montant cumulatif de chaque item est aussi équivalent au montant mensuel de cet item).

Colonne conditionelle Power Query

 

Obtention des montants cumulatifs et mensuels

Au final, j’ai obtenu une table de données avec les mois, les produits et les montants cumulatifs et mensuels pour chacune des combinaisons.

Power Query résultat final

 


 

Formation complémentaire

Afin d’approfondir vos connaissances avec Power BI et plus particulièrement vos compétences avec l’éditeur de requête Power Query, nous vous recommandons notre formation Recettes magiques pour transformer vos données.

 

Voici quelques commentaires d’apprenants ayant suivi la formation en ligne Recettes magiques pour transformer vos données :


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é.

5 réflexions sur “Power Query : Convertir des montants cumulatifs en montants mensuels”

  1. Merci beaucoup pour cet élément de réponse. C’est très utile surtout dans le domaine financier ou l’on raisonne surtout en YTD et donc le mensuel s’effectue par différence.

  2. Bonjour, merci beaucoup pour ce tuto qui permet de faire beaucoup de chose. Je rencontre une problématique lorsque que j’adapte cette méthode à mon domaine d’activité : la logistique d’approvisionnement. Je cherche à faire des projections de rupture.
    Je simplifie ma data base :
    Article : A
    Client : B
    Fournisseur : C
    Année : 2019
    Semaine : 10
    Rupture à J : 0
    Rupture à S+1 : 1

    J’ai donc utilisé votre méthode pour faire la projection. J’ajoute +1 à la semaine. Le problème est que pour la semaine 11, l’article A du fournisseur C pour le client B n’est pas en rupture (ni à J, ni à S+1). Je n’ai donc pas la ligne :
    Article : A
    Client : B
    Fournisseur : D
    Année : 2019
    Semaine : 11

    Le résultat est que lorsque je fusionne les requêtes comme nouvelle, je perds une partie des données. La data de la projection S+1 de la semaine 10 n’est pas rattaché à la semaine 11 lors du développement de la table, puisque la semaine 11 n’existe pas.

    Avez-vous une solution qui permet d’ajouter les semaines manquantes lors du développement de la table ou bien un conseil permettant d’attaquer ce sujet sous un autre angle ?

    1. Bonjour,

      Pourriez-vous svp poser votre question sur notre forum et y insérer votre fichier? Présentement je n’arrive pas à comprendre votre question. Je crois que la réponse se trouve probablement dans le fait que vous ne normalisez pas vos données mais je ne suis pas certaine. En insérant votre fichier sur le forum, je pourrai le consulter et vous fournir la réponse directement dans votre fichier.

      Merci.

      Sophie

  3. Bonjour,
    J’ai lu avec beaucoup d’attention votre tutoriel. Malheureusement je reste bloqué à l’étape “Extraction du montant du mois précédent”. Il y a une étape qui m’échappe car je ne trouve pas de colonne mois précédent. J’ai bien la table avec New Column mais après je ne sais pas comment faire. Si vous pouvez m’aider, j’apprécierai.
    Par avance, merci.
    Coridalement,

Laisser un commentaire

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

Retour en haut