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
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
À 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.
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:
Nous avons ensuite fusionné toutes les colonnes de données ensemble.
Une fois les colonnes fusionnées, nous avons transposé le résultat.
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).
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:
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:
Nous avons ensuite pu supprimer les dates égales à 0 et la colonne Attribute.
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:
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:
Deuxième condition:
Troisième condition:
Voici donc le résultat avec la nouvelle colonne personnalisée:
À 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).
Au final, nous avons pu supprimer les colonnes excédentaires et ne retenir que les mois, les villes et les montants.
Ceci nous a permis de tracer le même tableau croisé dynamique que dans la première solution.
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
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