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. Voici donc comment répartir des montants entre différentes dates dans Power Query.

Cas à résoudre : Répartir des montants entre différentes dates dans Power Query

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.

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.

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)

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 :

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.