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

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

Publié le : 19 décembre 2016

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:

 

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


Nos formations sont éligibles à la subvention PACME du Québec

et notre entreprise est datadockée en France

Microsoft Most Valuable Professional

Le CFO masqué vous offre un vaste choix de formations Excel et Power BI, réparties dans 8 catégories: Excel – Tableaux de bord, Excel – Modélisation financière, Excel – VBA, Excel – Power Tools, Excel – Ninja, Power BI, Finance corporative et Compléments avancés. 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

 

Sophie Marchand

Sophie Marchand, M.Sc., CPA, CGA, MVP, est détentrice d’une M.Sc. en finance corporative et d’un titre comptable CPA, CGA. Elle est également MVP Office Apps and Services (Excel) et MVP Data Platform (Power BI). Le titre de MVP est un titre honorifique remis par Microsoft et signifie « Most Valuable Professional ». Il est attribué à des experts de solutions Microsoft qui se distinguent non seulement par leurs compétences mais également par leur implication dans la communauté. Seuls 4 professionnels possèdent cette double-qualification dans le monde. Mme Marchand cumule de nombreuses années d’expérience dans le milieu des affaires et se spécialise en modélisation financière et en intelligence d’affaires avec Excel et Power BI. Après avoir longtemps offert ses services comme consultante, elle se consacre désormais à la formation, sous toutes ses formes (formations en classe, formations en ligne, formations en entreprise, webinaires, animation de groupe d'usagers, rédaction d'articles de blogue, animation de forum en ligne et plus encore). Vous pouvez d'ailleurs la retrouver à la tête du groupe d'usagers Montreal Modern Excel and Power BI.

Laisser un commentaire