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

Publié le 19 décembre 2016
par Sophie Marchand M.Sc., CPA, CGA, MVP
Requête Power Query

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

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.
 
 

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.

 

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)

 


 

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 :

Edith Lemay
Écrit il y a 1 semaine
Wow! La partie sur le web Scraping ouvre tout un autre univers!

Super compétences techniques et pédagogiques. La formation est utile ET agréable!

ahua blaise samouth
Écrit il y a 3 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

 

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

  1. Bonjour, la fonction utilisée pour calculer le nombre de jours de chaque mois fonctionne pour moi sauf pour le mois de janvier lorsque je passe à une année suivante (car ma répartition englobe plusieurs années..). Existe-t-il une solution ? Bien à vous !

Laisser un commentaire

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

Scroll to Top