Power BI — novembre 17, 2022

À lire

Table de date dynamique dans Power BI

Dans cet article je vous présenterai 2 méthodes différentes afin de créer une table de dimension de date dynamique dans Power BI. Puisque vous lisez ceci, je tiens pour acquis que vous êtes un utilisateur de Power BI et que vous connaissez le concept de modélisation en étoiles. Si ce n’est pas le cas, je vous réfère à cet autre article qui en traite.

Pourquoi une table de date dynamique?

La date est la dimension qui se retrouve le plus souvent dans un modèle de données en étoiles. En effet, lorsque vous voulez analyser un fait selon un axe temporel, vous devez vous référer à une date. Si par exemple vous souhaitez croiser 2 faits différents, par exemple les ventes et les retours, vous n’avez d’autres choix que de passer par une dimension date qui sera rattachée aux 2 tables de faits. Lorsqu’on parle de dimensions conformes (consultez cet article si vous n’êtes pas familier avec le concept de dimension conforme), la dimension date vient au haut de la liste puisqu’elle est souvent partagée par de multiples tables de faits dans un modèle de données en étoiles. Bref, il est important de bien définir la table de date dans votre modèle de données puisqu’elle est une des tables les plus fréquentes et qu’elle doit évoluer au fil du temps pour prendre en considération les nouvelles journées qui s’ajoutent.

Maintenant que je vous ai convaincu (je sais, je sais, je n’avais pas besoin de vous convaincre 😉) d’utiliser une dimension date dans votre modèle de données, passons au sujet de l’article, soit de créer une table de date dynamique dans Power BI. Il existe 2 méthodes pour arriver à nos fins, soit de créer la table en DAX ou de la créer via du code M (code utilisé par Power Query).

Approche 1: table de date en DAX

Le code DAX ci-bas vous permet de créer dynamiquement une table de date en DAX. Vous remarquerez l’utilisation de la fonction CALENDAR(). Cette fonction est le pivot de notre table dynamique. En utilisant cette fonction, Power BI crée une table entre une date de début et une date de fin. Plusieurs options s’offrent à vous pour spécifier les dates de début et de fin. Vous pouvez naturellement les spécifier manuellement (en utilisant la fonction DATE()) mais nous nous éloignons de la notion de table dynamique puisqu’elle sera fixe. Vous pouvez également spécifier manuellement la date de début et utiliser la fonction TODAY() pour la date de fin. De cette manière, la table sera dynamique puisqu’à chaque nouvelle journée, une ligne représentant la nouvelle journée s’ajoutera dans la table. L’autre option est d’utiliser les fonctions MIN() et MAX(). Par exemple, si votre modèle de données est composé d’une seule table de fait qui contient une colonne représentant la date de transaction, vous pouvez utiliser la fonction MIN() sur cette colonne pour la date de début et la fonction MAX() sur cette colonne pour la date de fin. De cette manière, lorsqu’une nouvelle date de transaction apparaitra dans votre table de fait, elle sera aussi ajoutée dans la table de date. Dans le code ci-bas, c’est cette approche que nous avons utilisée. À noter que la fonction CALENDAR() crée automatiquement un table de date avec un champ Date. Nous pouvons ensuite créer d’autres colonnes qui sont dérivées de cette colonne, par exemple un colonne Année, une colonne Mois, etc. Finalement, il est également possible d’utiliser la fonction CALENDARAUTO() qui créera une table de date partant de la plus petite date dans votre modèle de données jusqu’à la plus grande date. Attention, cette fonction retournera la plus petite et plus grande date basée sur toutes les colonnes de dates dans votre modèle de données. Par exemple, si vous avez un dimension employé qui contient la date de naissance de vos employés, vous aurez compris que la date de début de votre table de date risque de remontrer à plusieurs années si vous avez des employés assez âgés.

let
  Source = List.Dates(StartDate, #"Table Length", #duration(1, 0, 0, 0)),
  #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
  #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
  StartDate = #date(2010, 1, 1),
  EndDate = DateTime.Date(DateTime.LocalNow()),
  #"Table Length" = Duration.Days(EndDate - StartDate)+1,
  #"Creat Date Table" = #"Changed Type",
  #"Inserted Year" = Table.AddColumn(#"Creat Date Table", "Year", each Date.Year([Date]), Int64.Type),
  #"Inserted Month" = Table.AddColumn(#"Inserted Year", "MonthNo", each Date.Month([Date]), Int64.Type),
  #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "MonthName", each Date.MonthName([Date]), type text),
    #"Inserted YearMonth" = Table.AddColumn(#"Inserted Month Name", "YearMonth", each Number.ToText([Year]) & "/" & [MonthName], type text),
    #"Inserted YearMonthNo" = Table.AddColumn(#"Inserted YearMonth", "YearMonthNo", each Number.ToText([Year]) & "/" & Number.ToText([MonthNo]), type text),
    #"Inserted YearMonthSort" = Table.AddColumn(#"Inserted YearMonthNo", "YearMonthSort", each [Year]*1000 + [MonthNo], Int64.Type),
  #"Inserted Quarter" = Table.AddColumn(#"Inserted YearMonthSort", "Quarter", each "Q" & Number.ToText(Date.QuarterOfYear([Date])), type text),
    #"Inserted YearQuarter" = Table.AddColumn(#"Inserted Quarter", "YearQuarter", each Number.ToText([Year]) & "/" & [Quarter], type text),
    #"Inserted Week" = Table.AddColumn(#"Inserted YearQuarter", "Week", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted YearWeek" = Table.AddColumn(#"Inserted Week", "YearWeek", each Number.ToText([Year]) & "/" & Number.ToText([Week]), type text),
    #"Inserted YearWeekSort" = Table.AddColumn(#"Inserted YearWeek", "YearWeekSort", each [Year]*1000 + [Week], Int64.Type),
    #"Inserted Day of Month" = Table.AddColumn(#"Inserted YearWeekSort", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Month", "WeekDay", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "DayName", each Date.DayOfWeekName([Date]), type text)
in
    #"Inserted Day Name"

Quelle approche choisir?

C’est bien beau tout ça, mais quelle approche dois-je utilise me direz-vous! Je vous répondrai qu’il n’y a pas de meilleure approche mais qu’au niveau performance, la table en M dans Power Query est plus performante. Les tables calculées ainsi que les colonnes calculées en DAX dans Power BI demandent généralement plus de temps de calcul que les tables et colonnes créées dans Power Query. Toutefois, si j’avais une recommandation à vous faire, je vous recommanderais de créer une table de date dynamique dans un Dataflow (qui utilise le code M). De cette manière, vous pourrez réutiliser la même table d’un jeu de données à l’autre. N’est-ce pas un des objectifs du BI, centraliser les données pour favoriser leur utilisation 🙂 .

Et voilà, vous savez maintenant comment créer une table de date dynamique dans Power BI! J’espère que cette petite astuce vous permettra d’optimiser vos modèles de données Power BI et de gagner en efficience.