Magazine Informatique

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

Publié le 19 décembre 2016 par Sopmar01 @mon_cher_watson

Cet article fait suite à notre article Power Query: Répartir des montants entre différentes dates (1 de 2), qui présentait une approche pour répartir certains montants entre différentes dates, de façon intuitive mais avec certaines limites. Ce deuxième article présente une deuxième approche, qui est plus efficace, mais qui nécessite d’effectuer des transformations qui sont un peu moins intuitives. Vous vous demandez peut-être pourquoi avoir présenté une première solution qui n’était pas optimale? Et bien, c’est pour des fins éducatives seulement. Nous croyons qu’il est plus facile de comprendre cette deuxième solution, après avoir étudié la première solution.

Cas à résoudre

Rappelons que le cas à résoudre provient d’une question posée sur notre forum, par Philippe.

Données de départ

Rappelons que 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 première solution, nous n’avons pas utilisé de table de dates, ce qui représentait une limite, puisque les colonnes calculées devaient être ajustées manuellement avec les dates de l’analyse en cours. Dans notre deuxième solution, nous allons utiliser une table de dates, que l’usager pourra modifier à sa guise à partir du fichier Excel.

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

Dans notre deuxième solution, nous utiliserons les requêtes suivantes:

  • Budget_Intermediraire_1 (déjà expliqué dans l’article précédent)
  • Budget_Intermediaire_2 (déjà expliqué dans l’article précédent)
  • Budget_Intermediaire_Total (déjà expliqué dans l’article précédent)
  • Dates (importation de la table de dates présentée plus haut)
  • Budget_Solution2 (explications ci-dessous)

La requête de la solution 2 débute par une référence à la requête Budget_Intermediraire_Total et renvoie donc d’abord le résultat suivant:

Requête Power Query

Nous avons ensuite fusionné toutes les colonnes de données ensemble.

Requête Power Query

Une fois les colonnes fusionnées, nous avons transposé le résultat.

Requête Power Query

Nous avons ensuite ajouté une colonne, que nous avons nommé « Dates » et à laquelle nous avons attribué la valeur 0. Par la suite, nous avons pu ajouter bout à bout la table obtenue avec notre table de dates, afin d’obtenir le résultat suivant (append queries/ajouter requêtes).

Requête Power Query

Nous avons ensuite utilisé la fonctionnalité Fill Down/Remplissage vers le bas, sur nos 6 premières colonnes, afin d’obtenir le résultat suivant:

Requête Power Query

Nous avons ensuite sélectionné la colonne Dates et cliqué sur l’option Unpivot Other Columns / Supprimer les tableaux croisés dynamiques des autres colonnes, afin d’obtenir le résultat suivant:

Requête Power Query

Nous avons ensuite pu supprimer les dates égales à 0 et la colonne Attribute.

Requête Power Query

Finalement, nous avons pu dissocier les éléments de la colonne Value et renommer les colonnes résultantes, afin d’obtenir le résultat suivant:

Requête Power Query

L’étape suivante a nécessité d’ajouter une colonne personnalisée (au lieu de X colonnes personnalisées, comme dans la première solution démontrée, où X représentait le nombre de périodes sur lesquelles les montants étaient répartis).

Le code est donc identique à celui de la première approche mais il réfère à des valeurs dynamiques au niveau des dates et il n’est utilisé que dans une seule colonne personnalisée.

Première condition:

Requête Power Query

Deuxième condition:

Requête Power Query

Troisième condition:

Requête Power Query

Voici donc le résultat avec la nouvelle colonne personnalisée:

Requête Power Query

À nouveau, nous pouvons ajouter le nombre de jours entre la date de départ et la date de fin et répartir le montant en fonction du nombre de jours sous analyse dans le mois en cours sur le nombre de jours total (voir calcul de la première approche).

Requête Power Query

Au final, nous avons pu supprimer les colonnes excédentaires et ne retenir que les mois, les villes et les montants.

Requête Power Query

Ceci nous a permis de tracer le même tableau croisé dynamique que dans la première solution.

Requête Power Query

Avantages et limites

Les avantages sont nombreux, incluant le fait que cette approche utilise des dates dynamiques, qui peuvent être modifiées par l’utilisateur. De plus, le créateur de la solution n’a pas à ajouter de nombreuses colonnes personnalisées (code assez long à écrire). Il n’ajoute qu’une seule colonne personnalisée, qui s’adaptera à toutes les situations.

La limite dépend de la taille des données sous analyse. Cette façon de procéder peut être un peu moins performante si les données sous-jacentes contiennent de très nombreuses lignes et si l’analyse est effectuée selon un très grand nombre de dates, quoique l’ajout de nombreuses colonnes personnalisées peut également être problématique en terme de performance, dans ces mêmes conditions. Notons qu’il faudrait une volumétrie impressionnante pour rencontrer ces problèmes de performance.

Consulter la première approche

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


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 BI libre-service. 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


Vous pourriez être intéressé par :

Retour à La Une de Logo Paperblog

Ces articles peuvent vous intéresser :

  • Montres Swatch Printemps-Été 2016 : Flower Power !

    Montres Swatch Printemps-Été 2016 Flower Power

    Invité à rejoindre les rangs des curieux, Gentleman Moderne s’est rendu à l’Happy Hour Floral organisé ce jeudi 4 janvier par Swatch. Lire la suite

    Par  Quentin
    CULTURE, HIGH TECH, MODE FEMME, MODE HOMME
  • Girl Power à Dubai

    Girl Power Dubai

    Ce n'est pas une nouveauté, les femmes ont toujours eu une place importante à Dubai. L'émirat a d'ailleurs accueilli pour la première fois le Women's Forum en... Lire la suite

    Par  Pascal Iakovou
    CONSO, MODE, MODE FEMME
  • Color Power

    Color Power

    Bon me voilà officiellement rentrée de vacances, fini Rhodes, le soleil, les cocktails, la piscine, la mer, la bronzette, le bon temps entre amis et entre... Lire la suite

    Par  Parisgrenoble
    CONSO, CÔTÉ FEMMES, MODE
  • Critiques Séries : Power. Saison 3. Episode 3.

    Power // Saison 3. Episode 3. I Got This on Lock. Voilà le Power que j’attendais. Il est enfin de retour et c’est pile poil ce qu’il me fallait en un jour un pe... Lire la suite

    Par  Delromainzika
    CULTURE, MÉDIAS, SÉRIES, TÉLÉVISION
  • Critiques Séries : Power. Saison 3. Episodes 4 et 5.

    Power // Saison 3. Episodes 4 et 5. Don’t Worry, Baby / Help Me. Power n’est pas Power sans un brin d’ironie dramatique. C’est ce que la série aime utiliser à... Lire la suite

    Par  Delromainzika
    CULTURE, MÉDIAS, SÉRIES, TÉLÉVISION
  • Critiques Séries : Power. Saison 3. Episodes 6 et 7.

    Critiques Séries Power. Saison Episodes

    Power // Saison 3. Episodes 6 et 7. The Right Decision / Don’t Go. Power démontre plutôt bien sa capacité à se renouveler dans ces deux nouveaux épisodes. Lire la suite

    Par  Delromainzika
    SÉRIES
  • Les Recettes du bonheur, un film à voir!!!

    Recettes bonheur, film voir!!!

    Hello,Je suis une fan de la cuisine. J'aime cuisiner mais également découvrir de nouvelles adresses gourmandes, de nouveaux mets, feuilleter des magazines et... Lire la suite

    Le 21 juin 2018 par   Mélanie Deltenre
    JOURNAL INTIME, TALENTS

A propos de l’auteur


Sopmar01 3823 partages Voir son profil
Voir son blog

l'auteur n'a pas encore renseigné son compte l'auteur n'a pas encore renseigné son compte