Sur Excel, parfois nous sommes limités quand il s'agit de joindre des bases de données entre elles. En effet, que faire si les données ne font pas le même volume, ou ne concernent pas le même sujet ? Certes, il est possible d’empiler ou de fusionner les requêtes sur Power Query pour pallier à ça, ce qui peut n’avoir aucun sens quand les données ne coïncident absolument pas, ou encore d’utiliser des formules bien complexes.
Une chose n’est cependant pas possible sans passer à minima par Power Pivot : créer un tableau croisé dynamique à partir de 2 tableaux de données séparés.
Le problème, c’est que par défaut, rien ne réunit les tableaux (que nous allons appeler « tables de faits » à partir de maintenant. La seule solution est de les relier selon une ou plusieurs colonnes communes.
Seulement voilà, sur Power Pivot, pour relier deux tables de faits, il faut absolument une liaison un vers un ou un vers plusieurs. Pour traduire cela en termes plus simples, il faudrait que les valeurs de la colonne commune se retrouvent toutes dans les 2 tables de faits, et que dans au moins une des 2 tables de faits, qu’aucune valeur n’apparaissent deux fois dans le tableau. C’est un cas absolument rare.
Côté Power BI, ce n’est pas beaucoup mieux. Bien que Power BI autorise les liaisons plusieurs à plusieurs, il reste tout de même le souci où il faut que chaque valeur ait son vis-à-vis dans les 2 tables de faits, et puis sur des tables de faits qui vont sur plusieurs années, des mauvaises surprises sont très courantes.
Il est donc souvent primordial de créer des tables de dimension, c’est-à-dire des tables qui vont servir de référentiel pour placer les différentes données de 2 (ou plus) tables de faits. Elles doivent remplir deux conditions : avoir toutes les valeurs de la colonne en commun entre les deux tables de faits, mais également ne les avoir qu’en un seul exemplaire, pour avoir la liaison 1 pour plusieurs.
Il se trouve également que souvent, la colonne en commun est une colonne de dates. Cela veut donc dire que la table de dimensions la plus importante à savoir créer est la table de dates.
Cet article est fait pour fournir les informations sur comment créer une table de dates de 3 manières différentes :
- Sur Power Pivot en quelques clics (niveau 1)
- Sur Power Query
- Sur Power Pivot
Il est possible de juste copier les codes présents dans cet article, et les coller sur votre éditeur Power Query ou DAX, et vous aurez votre propre table de date.
Cela étant, nous détaillons en vidéo le raisonnement derrière la création de chaque table de dates :
- Lien hypertexte [Créer une table de dates en quelques clics sur Power Pivot]
- Lien hypertexte [Créer une table de dates sur Power Query à partir de rien]
- Lien hypertexte [Créer une table de dates en DAX]
Niveau 1 : La table de dates générée automatiquement par Power Pivot
C’est le plus simple et surtout le plus rapide à créer, quand on n’a pas déjà le code de création de la table sous la main. C’est aussi très adapté aux cas où nous cherchons à créer un tableau croisé dynamique à partir de 2 (ou plus tables de faits).
Pour cela, il y a plusieurs étapes.
Tout d’abord, nous ajoutons nos tables de faits une par une sur Power Pivot en allant dans l’onglet Power Pivot du ruban.
Si vous n’avez pas l’onglet Power Pivot sur votre ruban (en haut), il est temps de l’ajouter (si vous avez Windows, n’existe malheureusement pas sur mac) ! Voici une procédure : aller dans Fichier, puis options. Dans la fenêtre options, sélectionner la rubrique Compléments, puis, dans la fenêtre des compléments, dans Gérer (en bas de la fenêtre), sélectionner « Compléments COM » puis cliquer sur Atteindre juste à côté. Une petite fenêtre s’ouvre alors à la place de la fenêtre des options, dans laquelle se trouve une liste de compléments. Dans cette liste, il y a Power Pivot, il ne reste plus qu’à le cocher puis cliquer sur Ok.
Maintenant que nous sommes sûrs que Power Pivot est accessible, nous pouvons ajouter nos tables de faits de la manière suivante : Cliquer sur n’importe quelle cellule de la première table de faits, puis, dans l’onglet Power Pivot, cliquer sur « Ajouter au modèle de données ». Fermer directement la fenêtre de Power Pivot (si elle met du temps à s’ouvrir, pas de panique : c’est toujours comme ça à la première ouverture) et répéter la manipulation sur la seconde table de faits. Les deux tables de faits sont maintenant importées sur Power Pivot, ou, en des termes plus conventionnels : utilisables dans le modèle de données.
Maintenant que les deux tables sont dans Power Pivot, il va falloir les relier, sinon, si on choisit d’en créer directement un tableau croisé dynamique, il sera impossible de mettre en face les bonnes informations dans les bonnes catégories. Donc, dans Power Pivot, si on essaye de relier les tables, par exemple par la colonne Date, ça ne fonctionnera très probablement pas, pour les raisons énoncées dans l’introduction.
La bonne nouvelle c’est que dans cet environnement, une table de dates automatique peut être créée en 3 clics : aller dans l’onglet Conception, puis table de dates puis Nouveau. La table de dates est générée automatiquement. A noter que la table de dates générée de cette manière manque d’une colonne de trimestres. Donc, en cas de besoins, aller un peu plus bas dans cet article pour voir le code en DAX (qui fonctionne sur Power Pivot également, à la différence près que les virgules deviennent des points-virgules) pour ajouter cette colonne également.
Maintenant, plus qu’à relier la colonne Date de la table de dates (donc de dimensions) aux colonnes de dates des deux tables de faits, et, dans l’onglet Accueil du ruban, insérer le tableau croisé dynamique. Pour avoir les données correctes dans le tableau croisé dynamique, il faudra utiliser les colonnes de la table de dates comme critère de découpage des valeurs.
Niveau 2 : Créer une table de dates depuis Power Query
Nous passons à une seconde approche en low-code, où nous créons la table de dates sur Power Query (depuis Excel ou Power BI). Pour le détail du raisonnement, vous pouvez voir ou revoir la vidéo sur la création d’une table de date sur Power Query. Voici le code que vous pouvez récupérer en partant d’une requête vide et coller dans l’éditeur avancé. Bien entendu, contrairement à la fonction CALENDARAUTO en DAX, il faudra déterminer la date de début et la date de fin. Ici, c’est paramétré sur le 01/01/2024 et le 31/12/2025, n’hésitez pas à modifier cela dans votre requête pour avoir une table de date plus adaptée à votre besoin.
let
Datedebut = #date(2024,1,1),
DateFin = #date(2025,12,31),
ListeDates = List.Dates(Datedebut, Number.From(DateFin - Datedebut)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(ListeDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Année", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Mois", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Nom du mois", each Date.MonthName([Date]), type text),
#"Added Custom" = Table.AddColumn(#"Inserted Month Name", "Année-Mois", each DateTime.ToText([Date], "yyyy-MMM")),
#"Inserted Quarter" = Table.AddColumn(#"Added Custom", "Trimestre", each "Trim. " & Text.From(Date.QuarterOfYear([Date])), type text),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Semaine de l'année", each Text.From([Année]) & "-" & Text.From(Date.WeekOfYear([Date])), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Week of Year", "Jour", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Jour de la semaine", each Date.DayOfWeek([Date])+1, Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Nom du jour", each Date.DayOfWeekName([Date]), type text)
in
#"Inserted Day Name"
La raison pour laquelle, même avec ce gros bloc de code, nous considérons cette approche comme de niveau 2 et pas 3, c’est que la plupart des étapes consignées dans le code ci-dessus ont été ajoutées en 2 clics chacune, et au final, sauf pour corriger des détails ou pour définir les variables de base, très peu de code a réellement été écrit au clavier.
Niveau 3 : Créer une table de dates en DAX, depuis Power BI
Sur Power BI, la table de dates automatique de Power Pivot n’existe pas. Il est possible de la créer depuis Power Query, mais il est plus efficace de la créer en DAX. Bien que les liaisons plusieurs à plusieurs soient autorisées sur Power BI, il est primordial de créer une table de dates pour lier les tables de faits pour les raisons citées en introduction. Voici un code en DAX que vous pouvez coller sur la barre de formules de Power BI, après avoir cliqué sur « Affichage Table » à gauche de la fenêtre, puis dans « Outils de table » puis « Nouvelle table » :
Calendrier =
ADDCOLUMNS(
CALENDARAUTO(),
"Année", YEAR([Date]),
"Numéro du mois", MONTH([Date]),
"Nom du mois", FORMAT([Date], "MMMM"),
"Année - Mois", FORMAT([Date], "yyyy-mmm"),
"Trimestre" , "Trim. " & QUARTER([Date]),
"Année - Semaine", YEAR([Date]) & " - " & WEEKNUM([Date], 2),
"Jour", DAY([Date]),
"Numéro du jour", WEEKDAY([Date],2),
"Nom du jour", FORMAT([Date], "dddd")
)
Conclusion
La table de dates est très certainement un incontournable quand il s’agit de la mise en relation de différentes bases de données entre elles. Ce n’est pas la seule table de dimensions possible, selon les points communs existant entre les tables de faits, mais c’est la plus courante, donc la plus importante à avoir dans son arsenal d’outils. En plus de cela, elle est utile sur Excel comme sur Power BI !
