Si vous vous intéressez à Power BI, et plus particulièrement à Power Query et Power Pivot, vous avez certainement entendu parler de la table de dates. C’est un pré-requis indispensable pour créer un modèle de données propre et utiliser les fonctions de dates du langage DAX. Je vous propose donc aujourd’hui d’étudier comment vous pouvez facilement créer votre propre table de dates !
Ce que vous devez retenir :
- une table de dates est donc indispensable pour utiliser proprement les outils de Power BI (principalement Power Query et Power Pivot)
- une date de dates doit être correctement formaté (une colonne contient tous les jours, de manière unique)
- cette table vous permettra de gagner en performance et en efficacité dans l’environnement Power Pivot.
La fonction M pour générer une table
Voici le code que j’utilise au quotidien pour générer une table de dates du 01/01/2017 au 31/12/2018 :
let
CreateDateTable = () as table =>
let
StartDate =#date(2017,1,1),
EndDate = #date(2018,12,31),
Culture = « dd/mm/yyyy »,
DayCount = Duration.Days(Duration.From(EndDate – StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{« Column1 », type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{« Column1 », « JOUR »}}),
InsertYear = Table.AddColumn(RenamedColumns, « ANNEE », each Date.Year([JOUR]), Int64.Type),
InsertQuarter = Table.AddColumn(InsertYear, « TRIMESTRE », each Date.QuarterOfYear([JOUR]), Int64.Type),
InsertMonth = Table.AddColumn(InsertQuarter, « MOIS », each Date.Month([JOUR]), Int64.Type),
InsertDay = Table.AddColumn(InsertMonth, « JOUR_MOIS », each Date.Day([JOUR]), Int64.Type),
InsertDayInt = Table.AddColumn(InsertDay, « JOUR_INT », each [ANNEE] * 10000 + [MOIS] * 100 + [JOUR_MOIS], Int64.Type),
InsertMonthName = Table.AddColumn(InsertDayInt, « MOIS_LIBELLE », each Date.ToText([JOUR], « MMMM », Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, « MOIS_CALENDRIER », each (try(Date.ToText([JOUR], « MMM », Culture)) otherwise [MOIS_LIBELLE]) & » » & Text.End(Number.ToText([ANNEE]),2), type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, « TRIMESTRE_CALENDRIER », each « T » & Number.ToText([TRIMESTRE]) & » » & Number.ToText([ANNEE]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, « JOUR_SEMAINE », each Date.DayOfWeek([JOUR], Day.Monday)+1, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, « JOUR_LIBELLE », each Date.ToText([JOUR], « dddd », Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, « JOUR_FIN_SEMAINE », each Date.EndOfWeek([JOUR]), type date),
InsertMonthOrder = Table.AddColumn(InsertWeekEnding , « ORDRE_MOIS », each [ANNEE]*100+[MOIS], Int64.Type)
in
InsertMonthOrder
in
CreateDateTable
Utiliser ce code dans Power Query
Pour utiliser ce code, rien de plus simple :
- Démarrer Power Query avec une requête vide
- Copiez/collez le code ci-dessus dans l’éditeur avancé
- Validez puis exécuter la requête.
Quand utiliser cette table de dates ?
Je ne cesserai de le répéter : à chaque fois que vous souhaitez travailler avec les outils Power BI ! En effet, une grande quantité de formules DAX dans l’environnement Power Pivot nécessitent une table de dates pour fonctionner.
N’hésitez pas à poser vos questions ou remarques en commentaire, et j’y répondrai !
N’hésitez pas à faire appel à nos experts pour vos besoins de développement, de formation, d’expertise ou d’assistance sur Power BI en nous contactant au 09.72.54.52.00 ou par mail à l’adresse contact@experts-powerbi.fr !