• Objectifs : écrire, 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.
• Prérequis : série « Langage M », chapitres 1 à 5.
• Niveau : avancé.

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

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.

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

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

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"

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

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

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

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

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

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

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

(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

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

(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

(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

(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

(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

(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

(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"

(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

(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

• 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.

• 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 @.

• 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) :