Power Query: Répartir des montants entre différentes dates (1 de 2)

Publié le 12 décembre 2016
par Sophie Marchand M.Sc.
solution-1-resultat-final

Il y a quelques jours, Philippe a posé une question sur notre forum. Il cherchait à comprendre comment répartir des montants entre différentes dates, dans Power Query. Cet article vise à vous présenter une première façon d’obtenir le résultat recherché par Philippe. Notez qu’un deuxième article suivra pour présenter une deuxième solution, encore plus raffinée.

 

Question de Philippe : Comment répartir des montants entre différentes dates ?

Vous pouvez retrouver la question de Philippe sur notre forum. En voici une copie ci-dessous.

répartir des montants entre différentes dates

 

Données de départ pour la répartition des montants entre différentes dates

Nous allons démontrer deux solutions pour résoudre cette problématique. Nous sommes conscients que la première solution proposée dans le présent article est plus complexe que la deuxième solution qui sera présentée, mais elle semble plus intuitive pour les usagers d’Excel. Ce faisant, nous débuterons par présenter cette première solution, en énumérant ses avantages et ses limites et nous présenterons ensuite une deuxième solution, qui viendra contrecarrer, en partie, ces limites.

 

Les données fournies par Philippe ressemblaient beaucoup à celles fournies ci-dessous. Seules les dates ont été changées pour restreindre l’intervalle de temps à 12 mois.

 

Table Budget

table_budget

 

Dans notre deuxième solution, nous utiliserons également une table de dates, représentant les mois retenus pour notre analyse. Toutefois, dans cette première solution, nous n’utiliserons pas cette table.

 

Table Dates

table_dates

 

À noter que chacune de ces tables ont été mises sous forme de tableaux (format as table), dans le fichier Excel.

 

Requêtes Power Query

Au total, pour réaliser nos deux solutions, nous avons utilisé 6 requêtes, tel qu’illustré ci-dessous.

requetes

 

Budget intermédiaire #1

Notre première requête consiste simplement à importer la table de budget et à effectuer les manipulations suivantes:

  • Supprimer les colonnes J2, J3 et Budget J2-J3
  • Renommer les colonnes restantes
  • Changer les formats de données

budget-intermediaire-1

 

Budget intermédiaire #2

Notre deuxième requête consiste simplement à importer la table de budget et à effectuer les manipulations suivantes:

  • Supprimer les colonnes J0, J1 et Budget J0-J1
  • Renommer les colonnes restantes
  • Changer les formats de données

budget-intermediaire-2

 

Budget intermédiaire total

Notre troisième requête met simplement bout à bout les deux tables précédentes, pour ne créer qu’une seule table de budget consolidée (utilisation de la fonctionnalité Append/Ajouter).

budget-intermediaire-total

 

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

 

Budget – Solution 1 pour répartir des montants entre différentes dates

Dans un premier temps, nous avons simplement ajouté une colonne faisant la différence entre les colonnes Date_fin et Date_départ afin d’obtenir le nombre de jours total entre les deux dates.

répartir des montants entre différentes dates dans Power Query

 

Ensuite, nous avons ajouté 12 colonnes personnalisées (vous pouvez apercevoir 4 d’entre elles ci-dessous), chacune représentant le nombre de jours effectifs dans le mois mentionné en colonne.

répartir des montants entre différentes dates dans Power Query

 

Chacune des colonnes calculées comprend une fonction if en 4 étapes.

 

Un premier if est utilisé pour considérer le nombre de jours écoulés dans le mois, lorsque le mois de la date de départ est le même que le mois de la date de la colonne. On doit alors prendre le nombre de jours entre la date de départ et la date de fin de mois de la date de départ.

répartir des montants entre différentes dates dans Power Query

 

Un deuxième if est utilisé pour considérer le nombre de jours écoulés dans le mois, lorsque le mois de la date de fin est le même que le mois de la date de la colonne. On doit alors prendre le nombre de jours entre la date de fin du mois précédent et la date de fin fournie.

répartir des montants entre différentes dates dans Power Query

 

Un troisième if est utilisé pour considérer le nombre de jours écoulés dans les mois entre la date de départ et la date de fin. On doit alors prendre le nombre de jours total dans le mois ou la différence entre le dernier jour du mois de la colonne et le dernier jour du mois précédent.

répartir des montants entre différentes dates dans Power Query

Si aucune condition n’est rencontrée, on affiche alors la valeur null. Vous trouverez un extrait du résultat ci-dessous.

répartir des montants entre différentes dates dans Power Query

 

Par la suite, nous avons “dé-pivoté” les colonnes (unpivot columns) pour obtenir le résultat suivant.

répartir des montants entre différentes dates dans Power Query

 

Nous avons ensuite appliqué le format de dates à notre colonne “Attribute” et nous l’avons transformée en date de fin de mois.

répartir des montants entre différentes dates dans Power Query

 

Nous avons finalement ajouté une colonne calculée afin d’obtenir le budget du mois en question, en fonction du nombre de jours couverts dans le mois.

répartir des montants entre différentes dates dans Power Query

 

Nous avons ensuite supprimé les colonnes qui n’étaient plus nécessaires à notre analyse.

répartir des montants entre différentes dates dans Power Query

 

À noter que les deux dernière requêtes ne sont utilisées que dans notre deuxième solution, qui sera présentée dans un autre article.

 

Résultat recherché : Répartition des montants entre différentes dates

À l’aide de notre table résultante, nous avons pu construire le tableau croisé dynamique suivant:

répartir des montants entre différentes dates

 

Avantages et limites

Cette solution a l’avantage d’être intuitive pour les usagers d’Excel, qui travaillent fréquemment avec des colonnes calculées et qui réfléchissent en ces termes.

 

Cette solution a le désavantage d’exiger d’ajouter de nombreuses colonnes calculées…. une par mois couvert dans l’analyse, ce qui peut être laborieux à mettre en place. De plus, comme ces dates sont inscrites dans les titres de colonnes, elles ne sont pas dynamiques… La solution n’est donc pas vraiment réutilisable, si nos données sources évoluent et utilisent de nouveaux mois. De plus, il faut maîtriser le langage M pour rédiger tous les calculs. Il s’agit toutefois d’un mal nécessaire puisque notre deuxième solution utilisera également le langage M. Par contre, elle demandera d’ajouter une seule colonne calculée (plutôt qu’une par mois couvert par l’analyse, comme c’est le cas avec la solution actuelle) et les mois pourront être mis à jour par l’usager, à partir d’une table Excel.

 

Ne manquer pas la suite de cet article!

Abonnez-vous dès maintenant à notre blogue.

Suite: Power Query: Répartir des montants entre différentes dates (2 de 2).

 


 

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

1 réflexion sur “Power Query: Répartir des montants entre différentes dates (1 de 2)”

  1. Bonjour et bravo pour votre site
    Un petit commentaire pour dire que cette solution exclut la date de début.

    Sinon dans Excel je préfère les imbrications Max(0;Min(fin.mois;datefinpériode)-Max(debut.mois;datedébutpériode)+1) pour calculer le nombre de jours par mois de la période considérée plutôt que les “si” imbriqués.

    Avec les premiers jours du mois en B6:M6 et en B7:M9 la formule =MAX(0;MIN(FIN.MOIS(B$6;0);$C2)-MAX(B$6;$B2)+1)*$F2/($C2-$B2+1)+MAX(0;MIN(FIN.MOIS(B$6;0);$E2)-MAX(B$6;$D2)+1)*$G2/($E2-$D2+1)

    on retrouve votre tableau de synthèse avec la date de début incluse (modifier $B2 en $B2+1 et $D2 en $D2+1 pour l’exclure)

    Stéphane

Laisser un commentaire

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

Retour en haut