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

Power Query: Convertir des montants cumulatifs en montants mensuels

Publié le : 26 janvier 2017

Il y a quelques jours, François a posé la question suivante sur notre forum.

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

 

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

 


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel, notamment en tableaux de bord et en modélisation financière, ainsi qu’une série de formations en lien avec Power BI. 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. De plus, nos formations peuvent mener à l’obtention d’une accréditation.

 

Découvrez quelles formations vous conviennent

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

 

Sophie Marchand

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA, d’un titre MVP (Most valuable professional) Excel et d'un titre MVP Data Platform de Microsoft, et cumule de nombreuses années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • reply Pierre ,

    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.

    Laisser un commentaire