Quelles sont les possibilités de créer des tables de dates dans des environnements libre-service ?

Un exemple de ceci est le calcul du numéro de semaine pour les années fiscales lorsqu’elles ne correspondent pas aux années civiles.

Faire cela avec une formule mathématique est un cauchemar, ou mes compétences en mathématiques ne sont pas assez sophistiquées.

Power Query et flux de données

Venons-en maintenant à la dernière variante : utiliser Power Query ou Data Flows.

Pour commencer, je ne fais pas de distinction entre Power Query et Data Flows en v1 ou v2, car ils fonctionnent tous sur les mêmes principes et utilisent le même langage.

Je commence à créer la table de dates dans Power Query en créant trois paramètres :

1. StartYear : la première année dans la table de dates
2. YearsToLoad : combien d’années doivent être couvertes par la table de dates
3. FirstMonthOfFiscalYear : qui est le premier mois de l’année fiscale.
   Si l’année fiscale correspond à l’année civile, ce sera 1 ; sinon, ce sera le numéro du premier mois de l’année fiscale.

Tout autre code reposera sur ces paramètres.

Le démarrage se fait toujours avec la même commande : Liste.Dates()

Les paramètres de cette fonction sont :

1. La date de début
2. Le nombre de jours pour créer la liste
3. L’intervalle, qui dans ce contexte est en jours

Cela conduit à une ligne comme celle-ci, en utilisant les paramètres mentionnés ci-dessus :

List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0))

Et voici le premier obstacle :

Habituellement, nous avons besoin d’un tableau de dates qui s’étend sur plusieurs années. Mais chaque quatrième année est une année bissextile.

Alors, comment pouvons-nous faire cela, puisque Microsoft exige un tableau de dates qui s’étend sur des années entières ?

La solution est d’obtenir la dernière date de l’année dernière (31 décembre) et de filtrer les lignes pour ne conserver que celles antérieures ou égales à cette date.

Et c’est la raison pour laquelle je multiplie 366 jours par le paramètre YearsToLoad.

Voici le M-Code complet pour ce scénario :

let
    Source = List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#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}}),
    #"Added Last Valid Date" = Table.AddColumn(#"Changed Type", "Last Valid Date", each #date(Date.Year(List.Max(#"Changed Type"[Date])) - 1, 12, 31), type date),
    #"Keep only valid dates" = Table.SelectRows(#"Added Last Valid Date", each [Date] <= [Last Valid Date])
in
    #"Keep only valid dates"

Ensuite, je peux commencer à ajouter toutes les colonnes nécessaires pour créer un tableau de dates complet.

Tout d’abord, j’ajoute un Date_ID, avec une représentation numérique de la date :

Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date])

Cette colonne doit être définie sur un type de données entier. Par conséquent, toute la ligne de M-Code est la suivante :

Table.AddColumn(#"Keep only valid dates", "Date_ID", each ( Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date]), Int64.Type)

Notez l’expression Int64.Type avant le dernier crochet fermant. Cela définit le type de données dans la même commande, éliminant ainsi le besoin d’une étape supplémentaire.

Ensuite, je peux utiliser les possibilités disponibles dans l’éditeur Power Query pour ajouter des colonnes supplémentaires que j’ajoute couramment à mes tables de dates :

Comme vous pouvez le constater, nous pouvons ajouter un grand nombre de colonnes sans écrire de code.

Mais à un moment donné, nous devons écrire notre propre code pour ajouter des colonnes supplémentaires, par exemple des colonnes pour stocker l’année et la période correspondante.

Voici quelques-unes de ces colonnes :

• Nom de l’année/mois
• Année/Trimestre
• Année/Semaine

Ensuite, pour les colonnes de date de début et de fin pour n’importe quelle période, comme une semaine ou un mois.

J’utilise ces colonnes pour le code d’intelligence temporelle personnalisé dans DAX. J’ai écrit d’autres articles ici sur ce sujet, comme les calculs hebdomadaires.

Et à un moment donné, le M-Code habituel s’avère insuffisant pour obtenir les informations requises.

Par exemple, lorsque j’ai besoin d’obtenir une colonne d’année alignée sur la semaine (YearForWeek).

Pour ces scénarios, j’ai commencé à écrire des fonctions M personnalisées qui me permettent d’accéder à une plage de dates pour chaque ligne, ce qui est autrement impossible dans M.

Dans ce cas, j’ai ajouté cette fonction :

(DateInput as date) as number =>
let
    ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3),
    Year = Date.Year(ClosestThursday)
in
    Year

Si vous n’êtes pas familier avec les fonctions M personnalisées, je vous recommande fortement de consulter cette fonctionnalité intéressante.

J’ajouterai quelques liens dans la section Références ci-dessous.

Après tout le développement de la table de dates, j’ai obtenu ces fonctions personnalisées :

• ObtenirISOAnnée
  Obtenez l’année alignée hebdomadairement
• ObtenirISOWeek
  Calculez le numéro de semaine correct en fonction de la norme ISO
• CalculerMonthDiff
  La différence en mois entre deux dates
• CalculerQuarterDiff
  La différence en trimestres entre deux dates
• Obtenir le numéro de la semaine fiscale
  Calculez le numéro de semaine en commençant par la semaine du jour où commence l’année fiscale.
• ObtenirCurrentFiscalYear
  Cela obtient l’année fiscale en cours en fonction de la date actuelle.
• GetCurrentFiscalStartYear
  Ceci calcule l’année au cours de laquelle l’année fiscale en cours commence.

Cela m’a pris du temps (2 à 3 jours de travail), mais j’ai réussi à intégrer toutes les colonnes dans le tableau des dates, ce que je considère utile dans la plupart des scénarios.

Mais les concepts de base du langage M ont ajouté du travail et de la complexité supplémentaires, ce qui n’est pas nécessaire, par exemple, en SQL.

Mais au lieu de copier tout le M-Code ici, je vais vous donner accès à le fichier Power BI contenant l’intégralité de la solution avec le tableau des dates.

Quelle est la prochaine étape ?

Eh bien, vous pouvez désormais prendre l’intégralité du M-Code, le copier dans un flux de données et le partager au sein de votre organisation.

Pour autoriser l’accès à votre flux de données, il suffit d’accorder des autorisations de visualisation aux consommateurs dans l’espace de travail.

De cette façon, vous disposez d’une seule version centralisée du tableau de dates que tout le monde peut utiliser.

C’est le point majeur qui rend cette approche très utile.

C’est la même chose que lorsque vous disposez d’une plate-forme de données centralisée, où vous créez un tableau de dates. Mais comme tout le monde ne dispose pas de cela, l’utilisation d’un flux de données constitue un bon compromis.

Dans mon travail avec Data Flows, j’ai constaté que le dépannage d’un échec d’importation peut être fastidieux. J’ai constaté que les messages d’erreur peuvent être minimes et manquer des détails importants.

Lequel utiliser ?

Qu’est-ce que je recommande d’utiliser ?

Tout d’abord, lorsque vous disposez d’un magasin de données centralisé, qu’il soit sur site ou basé sur le cloud, ou s’il s’agit d’une base de données relationnelle ou d’un autre magasin de données, utilisez-le pour créer votre table de dates.

Comme je l’ai déjà mentionné, cela ne fait aucun doute.

Dans un scénario de BI en libre-service, ou lorsque l’entreprise n’est pas si grande, la décision n’est pas si simple.

Tout d’abord, cela dépend des compétences disponibles.

Après avoir créé la table de dates dans Power Query, j’ai découvert qu’il était beaucoup plus facile de créer une table de dates dans DAX que dans Power Query.

Les capacités de DAX facilitent la création d’une table de dates qu’avec M-Code dans Power Query.

Je peux définir la table dans une seule instruction DAX et ajouter une logique complexe dans des colonnes calculées supplémentaires.

Mais chaque table de dates DAX est locale à chaque modèle sémantique Power BI. Par conséquent, vous vous retrouvez avec plusieurs tables de dates qui peuvent différer les unes des autres.

Mais dès que plusieurs équipes créent des solutions Power BI, il peut être avantageux de créer une seule table de dates centrale dans un seul espace de travail et de la partager avec toutes les équipes.

Lorsque quelqu’un a besoin d’une nouvelle fonctionnalité dans la table de dates, elle sera ajoutée à la table centrale et tout le monde pourra en bénéficier.

Bien entendu, cela est valable pour toute variante de tables de dates centralisées.

Dans de tels cas, le développeur du modèle de données peut toujours décider quelles colonnes importer, évitant ainsi l’importation de colonnes inutiles dans le modèle de données.

Conclusion

Vous connaissez désormais les différentes manières de construire une table de dates.

Vous décidez parmi les possibilités disponibles.

Mais il sera difficile de passer d’une table DAX locale à des tables centralisées.

Vous devez réfléchir le plus tôt possible à la direction que vous emprunterez pour éviter le travail supplémentaire lié au basculement entre eux.

Prenez votre temps et discutez avec tous les membres de l’équipe ou les créateurs de modèles potentiels pour choisir la bonne voie.

Cela n’a aucun sens de décider de créer une table de dates centralisée alors que personne ne l’utilise.

Alors, assurez-vous que tout le monde est d’accord avec l’utilisation de la table de rendez-vous centrale.

Références

Ici, la documentation Microsoft sur les fonctions personnalisées dans M :

https://learn.microsoft.com/en-us/powerquery-m/m-spec-functions

Une page sur Microsoft En savoir plus sur les fonctions personnalisées :

https://learn.microsoft.com/en-us/power-query/custom-function

Une bonne explication de Wicked Smart Data :

https://www.wickedsmartdata.com/articles/custom-m-functions-power-query

Si vous préférez une vidéo pour apprendre, celle-ci explique les fonctions personnalisées de A à Z :

Cette vidéo pose la question de savoir quand les fonctions personnalisées sont utiles :

Cela vous montre comment résoudre un défi, y compris une approche très pratique sur la façon de développer facilement une fonction personnalisée :