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

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

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

Il y a quelques jours, Philippe a posé une question sur notre forum, concernant la répartition de 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

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

forum-question

 

Données de départ

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.

 

Budget – Solution 1

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.

solution-1-etape-1

 

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.

solution-1-etape-2

 

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.

solution-1-premier-if

 

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.

solution-1-deuxieme-if

 

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.

solution-1-troisieme-if

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

solution-1

 

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

solution-1-etape-3

 

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

solution-1-etape-4

 

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.

solution-1-etape-5

 

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

solution-1-resultat-final

 

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

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

solution-finale-en-tcd

 

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 :

ahua blaise samouth
Écrit il y a 2 semaines
Formation très intéressante

Formation très intéressante

Sylvain LEPRÊTRE
Écrit il y a 8 mois
Formation très intéressante

Formation très intéressante si on veut monter au niveau supérieur dans Power Query

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

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 *

Scroll to Top