Support formation Rapports Power BI

Techniques avancées en M

Ecrire, tester et documenter une fonction personnalisée ; imbriquer des expressions let ... in ; comprendre les closures et la récursivité ; rechercher la dernière valeur connue avant une date.

Pourquoi ?

Une règle de calcul répétée dans plusieurs requêtes (TVA, tranches, jours ouvrés) doit être écrite une seule fois, testée, puis réutilisée : c'est le rôle des fonctions personnalisées. Les notions de portée (let imbriqués, closures) et la récursivité expliquent comment ces fonctions accèdent aux valeurs qui les entourent, et permettent de résoudre des problèmes que l'interface ne couvre pas.

Notions clés

Fonctions personnalisées

Copiez ce prototype dans une requête vide (Accueil > Nouvelle source > Requête vide, puis Éditeur avancé) :

(param1) =>
    let
        Retour = param1 * 2
    in
        Retour

Après validation, la requête s'affiche comme une fonction (icône fx). Pour l'utiliser :

  1. Dans une requête, Ajouter une colonne > Appeler une fonction personnalisée.
  2. Sélectionnez la fonction et renseignez ses paramètres (valeur fixe ou colonne).
  3. Validez. Si la fonction renvoie une table, la colonne affiche Table : développez-la avec le bouton à droite de l'en-tête.

Pour tester une fonction seule, sélectionnez-la, saisissez des valeurs dans les zones de paramètres, puis cliquez sur Appeler : Power Query crée une requête « Fonction appelée » avec le résultat. Vous pouvez aussi l'appeler dans le code d'une étape :

= Table.AddColumn(Source, "Prix TTC", each fxPrixTTC([Montant HT]), type number)

Une requête existante peut aussi devenir une fonction : clic droit > Créer une fonction, ses paramètres devenant les arguments (méthode de la page « Combiner des fichiers et des feuilles »). Source

let ... in imbriqués

Code nouveau (les exemples de l'ancienne page n'avaient jamais été rédigés). Une variable d'un let peut contenir elle-même une expression let ... in, dont les étapes intermédiaires restent invisibles à l'extérieur.

Imbrication dans une variable : le seuil est calculé dans un bloc à part, puis utilisé par le filtre.

let
    Source = #table({"Produit", "Montant"}, {{"A", 100}, {"B", 250}, {"C", 50}}),
    Seuil =
        let
            Total = List.Sum(Source[Montant]),
            Moyenne = Total / Table.RowCount(Source)
        in
            Moyenne,
    AuDessusMoyenne = Table.SelectRows(Source, each [Montant] > Seuil)
in
    AuDessusMoyenne

Résultat : la seule ligne B (moyenne 133,33). Total et Moyenne ne sont pas accessibles dans AuDessusMoyenne ; seule Seuil l'est.

Imbrication dans le résultat final :

let
    Source = #table({"Produit", "Montant"}, {{"A", 100}, {"B", 250}, {"C", 50}})
in
    let
        NbLignes = Table.RowCount(Source)
    in
        "La table compte " & Text.From(NbLignes) & " lignes"

Imbrication dans une fonction personnalisée (fxNettoyerEspaces) : supprime les espaces au début, à la fin et en double entre les mots.

(Texte as text) as text =>
    let
        Mots = Text.Split(Text.Trim(Texte), " "),
        MotsNonVides = List.Select(Mots, each _ <> ""),
        Resultat = Text.Combine(MotsNonVides, " ")
    in
        Resultat

fxNettoyerEspaces(" Power BI ") renvoie « Power BI ». Limitez l'imbrication à deux ou trois niveaux et indentez chaque bloc. Source

Closures (fermetures)

Code nouveau (les trois exemples de l'ancienne page ont été perdus lors de la migration). Une closure est une fonction qui conserve l'accès aux variables de l'endroit où elle a été définie, même lorsqu'elle est appelée ailleurs.

Exemple 1 : la fonction utilise x, définie dans le même let.

let
    x = 10,
    FonctionClosure = () => x * 2,
    Resultat = FonctionClosure()
in
    Resultat

Résultat : 20.

Exemple 2 : la fonction est appelée dans un bloc où x vaut 20, mais elle garde la valeur de x de son lieu de définition.

let
    x = 10,
    FonctionClosure = () => x * 2,
    Resultat =
        let
            x = 20
        in
            FonctionClosure()
in
    Resultat

Résultat : 20, et non 40. La portée est lexicale : seule compte la valeur de x visible là où la fonction a été écrite. Source

Exemple 3 : une fonction qui renvoie une fonction. Multiplier(10) renvoie une fonction qui « se souvient » de x = 10.

let
    Multiplier = (x) => (multiplicateur) => x * multiplicateur,
    FoisDix = Multiplier(10),
    Resultat = FoisDix(2)
in
    Resultat

Résultat : 20. Usage pratique : fabriquer des fonctions spécialisées à partir d'un seul modèle, par exemple un taux de TVA par colonne.

let
    Source = #table(type table [Article = text, HT = number], {{"Livre", 20}, {"Stylo", 2.5}}),
    AppliquerTaux = (taux as number) => (montant as number) => Number.Round(montant * (1 + taux), 2),
    TTC = Table.TransformColumns(Source, {{"HT", AppliquerTaux(0.055), type number}})
in
    TTC

Les closures sont partout dans le code généré : chaque each d'un Table.AddColumn ou d'un Table.SelectRows est une fonction qui peut utiliser les étapes précédentes de la requête.

Récursivité

Code nouveau. Une fonction récursive s'appelle elle-même. Dans un let, son propre nom n'est pas encore disponible pendant sa définition : on le préfixe par l'opérateur de référence inclusive @.

let
    Factorielle = (n as number) as number =>
        if n <= 1 then 1 else n * @Factorielle(n - 1),
    Resultat = Factorielle(5)
in
    Resultat

Résultat : 120. Exemple pratique, remplacer les doubles espaces jusqu'à ce qu'il n'en reste plus :

let
    SupprimerDoublesEspaces = (t as text) as text =>
        if Text.Contains(t, "  ") then @SupprimerDoublesEspaces(Text.Replace(t, "  ", " ")) else t,
    Resultat = SupprimerDoublesEspaces("Power    BI   Desktop")
in
    Resultat

Résultat : « Power BI Desktop ». La récursivité est lente sur de gros volumes ; List.Generate ou List.Accumulate sont souvent préférables pour les calculs itératifs (voir fxTableauAmortissement ci-dessous). Source

Exemple

Recherche de la valeur précédente

Code nouveau (les formules de l'ancienne page ont été perdues). Une table de relevés contient des compteurs qui ne sont pas saisis tous les jours. Pour chaque date, on veut la dernière valeur connue avant cette date, ou 0 s'il n'y en a pas. Les fichiers d'exemple sont joints :

recherche-valeur-precedente.xlsx10 KB⬇
recherche-valeur-precedente.zip45 KB⬇

Fonction fxValeurPrecedente (nom de colonne passé en texte, grâce à Record.Field) :

(TableMesures as table, NomColonne as text, DateRef as date) as number =>
    let
        FiltreDateAvant = Table.SelectRows(TableMesures, each [DateObservation] < DateRef),
        FiltreCompteurPasVide = Table.SelectRows(FiltreDateAvant, each Record.Field(_, NomColonne) <> null),
        FiltreDateMax = Table.Sort(FiltreCompteurPasVide, {{"DateObservation", Order.Descending}}),
        Retour = if Table.IsEmpty(FiltreDateMax) then 0 else Record.Field(FiltreDateMax{0}, NomColonne)
    in
        Retour

Test complet dans une requête vide :

let
    Mesures = Table.Buffer(#table(
        type table [DateObservation = date, CompteurA = nullable number, CompteurB = nullable number],
        {
            {#date(2024, 1, 1), 10, null},
            {#date(2024, 1, 2), null, 5},
            {#date(2024, 1, 3), 14, null},
            {#date(2024, 1, 4), null, null},
            {#date(2024, 1, 5), 20, 9}
        }
    )),
    AvecPrecedent = Table.AddColumn(Mesures, "CompteurA précédent", each fxValeurPrecedente(Mesures, "CompteurA", [DateObservation]), type number)
in
    AvecPrecedent

Résultat attendu pour CompteurA précédent : 0, 10, 10, 14, 14. Table.Buffer évite de relire la table des mesures pour chaque ligne. Source

[Capture : résultat du test, colonne CompteurA précédent avec les valeurs 0, 10, 10, 14, 14]

Bibliothèque de fonctions de calcul

Chaque fonction se colle dans une requête vide renommée avec le nom indiqué (préfixe fx).

fxDateAAAAMMJJ convertit un nombre ou un texte AAAAMMJJ (20241231) en date, indépendamment des paramètres régionaux :

(ColonneDate) =>
    let
        Txt = Text.From(ColonneDate),
        Retour = #date(Number.From(Text.Start(Txt, 4)), Number.From(Text.Range(Txt, 4, 2)), Number.From(Text.End(Txt, 2)))
    in
        Retour

fxPrixTTC montre un paramètre optional avec valeur par défaut : fxPrixTTC(100) renvoie 120, fxPrixTTC(100, 0.055) renvoie 105,5. Source

(MontantHT as number, optional TauxTVA as nullable number) as number =>
    let
        Taux = if TauxTVA = null then 0.2 else TauxTVA,
        Retour = Number.Round(MontantHT * (1 + Taux), 2)
    in
        Retour

fxVariation gère les null et la division par zéro : fxVariation(120, 100) renvoie 0,2, fxVariation(120, 0) renvoie null.

(Valeur as nullable number, Reference as nullable number) as nullable number =>
    if Valeur = null or Reference = null or Reference = 0 then null
    else (Valeur - Reference) / Reference

fxAge calcule l'âge en années révolues : fxAge(#date(1990, 6, 15), #date(2026, 6, 14)) renvoie 35. Sans seconde date, la date du jour est utilisée ; elle est évaluée à chaque actualisation, en heure UTC dans le service. Source

(DateDebut as date, optional DateFin as nullable date) as number =>
    let
        Fin = if DateFin = null then Date.From(DateTime.LocalNow()) else DateFin,
        Ecart = Date.Year(Fin) - Date.Year(DateDebut),
        AnniversaireNonPasse = Date.Month(Fin) * 100 + Date.Day(Fin) < Date.Month(DateDebut) * 100 + Date.Day(DateDebut),
        Retour = if AnniversaireNonPasse then Ecart - 1 else Ecart
    in
        Retour

fxJoursOuvres compte les jours du lundi au vendredi, hors jours fériés facultatifs : fxJoursOuvres(#date(2026, 10, 1), #date(2026, 10, 14)) renvoie 10. Avec une requête Feries : fxJoursOuvres([Date commande], [Date livraison], List.Buffer(Feries[Date])). Source

(DateDebut as date, DateFin as date, optional JoursFeries as nullable list) as number =>
    let
        Feries = if JoursFeries = null then {} else JoursFeries,
        NbJours = Duration.Days(DateFin - DateDebut) + 1,
        ListeDates = if NbJours <= 0 then {} else List.Dates(DateDebut, NbJours, #duration(1, 0, 0, 0)),
        JoursOuvres = List.Select(ListeDates, each Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(Feries, _)),
        Retour = List.Count(JoursOuvres)
    in
        Retour

fxTranche classe un montant dans une tranche définie à un seul endroit : fxTranche(250) renvoie « De 100 à 999 € ».

(Montant as nullable number) as text =>
    if Montant = null then "Non renseigné"
    else if Montant < 100 then "Moins de 100 €"
    else if Montant < 1000 then "De 100 à 999 €"
    else "1 000 € et plus"

fxMensualite calcule la mensualité d'un emprunt : fxMensualite(10000, 0.05, 12) renvoie 856,07. Source

(Capital as number, TauxAnnuel as number, NbMois as number) as number =>
    let
        TauxMensuel = TauxAnnuel / 12,
        Mensualite = if TauxMensuel = 0 then Capital / NbMois
                     else Capital * TauxMensuel / (1 - Number.Power(1 + TauxMensuel, -NbMois)),
        Retour = Number.Round(Mensualite, 2)
    in
        Retour

fxTableauAmortissement renvoie une table d'échéances calculée avec List.Generate et appelle fxMensualite : fxTableauAmortissement(10000, 0.05, 12) renvoie 12 lignes (intérêts, capital amorti, capital restant dû). Le dernier capital restant peut présenter quelques centimes d'écart dus aux arrondis.

(Capital as number, TauxAnnuel as number, NbMois as number) as table =>
    let
        TauxMensuel = TauxAnnuel / 12,
        Mensualite = fxMensualite(Capital, TauxAnnuel, NbMois),
        Lignes = List.Generate(
            () => [Mois = 0, Interets = 0, Amortissement = 0, Restant = Capital],
            each [Mois] <= NbMois,
            each [
                Mois = [Mois] + 1,
                Interets = Number.Round([Restant] * TauxMensuel, 2),
                Amortissement = Mensualite - Interets,
                Restant = Number.Round([Restant] - Amortissement, 2)
            ]
        ),
        Retour = Table.FromRecords(
            List.Skip(Lignes, 1),
            type table [Mois = Int64.Type, Interets = number, Amortissement = number, Restant = number]
        )
    in
        Retour

Pièges et bonnes pratiques

  • Nommez les fonctions avec un préfixe (fx) et rangez-les dans un groupe de requêtes « Fonctions ».
  • Typez les paramètres et le retour (as number, as table) : une erreur est signalée dès l'appel, et la boîte d'appel propose le bon contrôle de saisie.
  • Prévoyez les null : as number refuse null, utilisez as nullable number si la colonne peut contenir des vides.
  • Documentez la fonction (description, exemples, libellés des paramètres) avec Value.ReplaceType et les champs Documentation.*. Source
  • Une fonction appelée sur chaque ligne et qui lit une autre requête doit recevoir une table ou une liste mise en mémoire avec Table.Buffer ou List.Buffer.

À retenir

  • Une fonction personnalisée s'écrit (paramètres) => expression et s'appelle par Appeler une fonction personnalisée.
  • Les variables d'un let imbriqué ne sont visibles que dans leur bloc.
  • Une closure garde les valeurs de son lieu de définition (portée lexicale).
  • Une fonction récursive définie dans un let s'appelle avec @.

Pour aller plus loin

  • Fonctions personnalisées. Source
  • Spécification : fonctions. Source
  • Documentation des fonctions personnalisées. Source
  • Supprimer les espaces en trop dans Power Query (gorilla.bi, en anglais) :