Présentation du langage de formules M dans Power Query

Microsoft Power Query procure une expérience « d’obtention et de transformation des données » puissante qui englobe de nombreuses fonctionnalités. Une fonctionnalité principale de Power Query consiste à filtrer et à combiner, autrement dit, à « hybrider » des données issues d’une ou de plusieurs des nombreuses sources de données prises en charge. Tout mashup de données de ce type est exprimé à l’aide du langage de formules Power Query (connu sous le nom de « M », première lettre de Mashup que l’on pourrait traduire en broyer, mixer, écraser). Power Query incorpore des documents M dans un large éventail de produits Microsoft, notamment Excel, Power BI, Analysis Services, Dataverse pour permettre un mashup reproductible de données.

Ce document fournit la spécification du langage M. Après une brève introduction qui vise à vous donner un premier aperçu du langage, le document couvre progressivement ce dernier plus en détail :

1. La structure lexicale définit l’ensemble de textes qui sont valides du point de vue lexical.
2. Les valeurs, les expressions, les environnements, les variables, les identificateurs et le modèle d’évaluation constituent les concepts de base du langage.
3. La spécification détaillée des valeurs, primitives et structurées, définit le domaine cible du langage.
4. Les valeurs ont des types, eux-mêmes un genre particulier de valeur, qui caractérisent les genres fondamentaux des valeurs et comportent des métadonnées supplémentaires propres aux formes de valeurs structurées.
5. L’ensemble des opérateurs dans M définit les genres d’expressions qui peuvent être formés.
6. Les fonctions, autre genre de valeurs spéciales, constituent la base d’une bibliothèque standard riche pour M et permettent l’ajout de nouvelles abstractions.
7. Les erreurs peuvent se produire lors de l’application d’opérateurs ou de fonctions pendant l’évaluation de l’expression. Bien que les erreurs ne soient pas des valeurs, il existe des moyens de gérer les erreurs consistant à mapper celles-ci à des valeurs.
8. Les expressions let permettent d’introduire des définitions auxiliaires utilisées pour créer des expressions complexes en plus petites étapes.
9. Les expressions if prennent en charge l’évaluation conditionnelle.
10. Les sections fournissent un mécanisme de modularité simple. (Les sections ne sont pas encore exploitées par Power Query.)
11. Enfin, une grammaire centralisée collecte les fragments de la grammaire de toutes les autres sections de ce document en une seule définition complète.

À l’attention des théoriciens du langage informatique : le langage de formule spécifié dans ce document est essentiellement un langage fonctionnel pur, d’ordre supérieur, typé dynamiquement et partiellement paresseux.

Dans M, la construction centrale est l’expression. Une expression peut être évaluée (calculée) et produire une valeur unique.

Bien que de nombreuses valeurs puissent être écrites littéralement sous la forme d’une expression, une valeur n’est pas une expression. Par exemple, l’expression 1 s’évalue à la valeur 1 ; l’expression 1+1 s’évalue à la valeur 2. Cette distinction est subtile mais importante. Les expressions sont des recettes pour l’évaluation ; les valeurs sont les résultats de l’évaluation.

Les exemples suivants illustrent les différents genres de valeurs disponibles dans M. En guise de convention, une valeur est écrite à l’aide de la forme littérale dans laquelle elle apparaîtrait dans une expression qui correspondrait à cette valeur uniquement. (Notez que // indique le début d’un commentaire qui continue jusqu’à la fin de la ligne.)

• Une valeur primitive est une valeur en une seule partie, comme un nombre, une valeur logique, du texte ou une valeur Null. Une valeur Null peut être utilisée pour indiquer l’absence de données.

  123                  // A number
  true                 // A logical
  "abc"                // A text
  null                 // null value
  

• Une valeur de liste est une séquence ordonnée de valeurs. M prend en charge les listes infinies, mais si elles sont écrites sous forme de littéral, les listes ont une longueur fixe. Les accolades { et } indiquent le début et la fin d’une liste.

  {123, true, "A"}     // list containing a number, a logical, and 
                        //     a text 
  {1, 2, 3}            // list of three numbers 
  

• Un enregistrement est un ensemble de champs. Un champ est une paire nom/valeur, où le nom est une valeur texte qui est unique dans l’enregistrement du champ. La syntaxe littérale des valeurs d’enregistrement permet d’écrire des noms sans guillemets, une forme également appelée identificateurs. L’exemple suivant montre un enregistrement contenant trois champs nommés « A », « B » et « C », qui ont les valeurs 1, 2et 3.

  [ 
        A = 1,  
        B = 2,  
        C = 3 
  ]
  

• Une table est un ensemble de valeurs organisées en colonnes (identifiées par nom) et en lignes. Il n’existe pas de syntaxe littérale pour la création d’une table, mais il existe plusieurs fonctions standard qui peuvent être utilisées pour créer des tables à partir de listes ou d’enregistrements.Par exemple :

  #table( {"A", "B"}, { {1, 2}, {3, 4} } ) 
  

  Cette syntaxe crée une table de la forme suivante :

  

• Une fonction est une valeur qui, quand elle est appelée avec des arguments, génère une nouvelle valeur. Pour écrire une fonction, vous listez ses paramètres dans des parenthèses, suivis du symbole de destination =>, suivi de l’expression définissant la fonction. Cette expression fait généralement référence aux paramètres (par nom).

  (x, y) => (x + y) / 2`
  

Le modèle d’évaluation du langage M est modélisé d’après le modèle d’évaluation généralement présent dans les tableurs, où l’ordre de calcul peut être déterminé en fonction des dépendances entre les formules dans les cellules.

Si vous avez écrit des formules dans un tableur tel qu’Excel, vous pouvez reconnaître que les formules de gauche aboutissent aux valeurs de droite quand elles sont calculées :

Dans M, certaines parties d’une expression peuvent référencer d’autres parties de l’expression par nom, et le processus d’évaluation détermine automatiquement l’ordre dans lequel les expressions référencées sont calculées.

Vous pouvez utiliser un enregistrement pour générer une expression équivalente au exemple précédent de feuille de calcul. Quand vous initialisez la valeur d’un champ, vous pouvez faire référence à d’autres champs de l’enregistrement au moyen du nom du champ, comme suit :

[  
    A1 = A2 * 2,  
    A2 = A3 + 1,  
    A3 = 1  
]

L’expression ci-dessus équivaut à la suivante (en ce sens que les deux correspondent à des valeurs égales) :

[  
    A1 = 4,  
    A2 = 2,  
    A3 = 1  
]

Les enregistrements peuvent être contenus, ou imbriqués, dans d’autres enregistrements. Vous pouvez utiliser l’opérateur de recherche ([]) pour accéder aux champs d’un enregistrement par nom. Par exemple, l’enregistrement suivant a un champ nommé Salescontenant un enregistrement et un champ nommé Total qui accède aux champs FirstHalf et SecondHalf de l’enregistrement Sales :

[  
    Sales = [ FirstHalf = 1000, SecondHalf = 1100 ], 
    Total = Sales[FirstHalf] + Sales[SecondHalf] 
]

L’expression ci-dessus équivaut à la suivante quand elle est évaluée :

[  
    Sales = [ FirstHalf = 1000, SecondHalf = 1100 ], 
    Total = 2100 
]

Les enregistrements peuvent également être contenus dans des listes. Vous pouvez utiliser l’opérateur d’index positionnel ({}) pour accéder à un élément d’une liste par son index numérique. Vous faites référence aux valeurs d’une liste à l’aide d’un index de base zéro à partir du début de la liste. Par exemple, les index 0 et 1 sont utilisés pour référencer le premier et le deuxième éléments de la liste ci-dessous :

[ 
    Sales =  
        {  
            [  
                Year = 2007,  
                FirstHalf = 1000,  
                SecondHalf = 1100, 
                Total = FirstHalf + SecondHalf // 2100 
            ], 
            [  
                Year = 2008,  
                FirstHalf = 1200,  
                SecondHalf = 1300, 
                Total = FirstHalf + SecondHalf // 2500 
            ]  
        }, 
    TotalSales = Sales{0}[Total] + Sales{1}[Total] // 4600 
]

Les expressions de membre de liste et d’enregistrement (ainsi que les expressions let) sont évaluées à l’aide de l’évaluation paresseus, ce qui signifie qu’elles sont évaluées uniquement en fonction des besoins. Toutes les autres expressions sont évaluées à l’aide de l’évaluation hâtive, ce qui signifie qu’elles sont évaluées immédiatement, quand elles sont rencontrées pendant le processus d’évaluation. Pour bien assimiler ces techniques, gardez à l’esprit que l’évaluation d’une expression de liste ou d’enregistrement retourne une valeur de liste ou d’enregistrement qui se souvient elle-même comment ses éléments de liste ou champs d’enregistrement doivent être calculés, quand ils sont demandés (par des opérateurs de recherche ou d’index).

Dans M, une fonction est un mappage entre un ensemble de valeurs d’entrée et une valeur de sortie unique. Pour écrire une fonction, vous commencez par nommer l’ensemble requis de valeurs d’entrée (les paramètres de la fonction), puis, après le symbole de destination (=>), vous fournissez une expression qui calcule le résultat de la fonction à l’aide de ces valeurs d’entrée (le corps de la fonction). Par exemple :

(x) => x + 1                    // function that adds one to a value 
(x, y) =>  x + y                // function that adds two values

Une fonction est une valeur comme un nombre ou une valeur de texte. L’exemple suivant montre une fonction qui est la valeur d’un champ Add qui est ensuite appelée, ou exécutée, à partir de plusieurs autres champs. Quand une fonction est appelée, un ensemble de valeurs est spécifié, puis logiquement substitué à l’ensemble de valeurs d’entrée nécessaire au sein de l’expression du corps de la fonction.

[ 
    Add = (x, y) => x + y,
    OnePlusOne = Add(1, 1),     // 2 
    OnePlusTwo = Add(1, 2)      // 3
]

M comprend un ensemble commun de définitions utilisables à partir d’une expression appelé bibliothèque standard, ou simplement bibliothèque en abrégé. Ces définitions se composent d’un ensemble de valeurs nommées. Les noms de valeurs fournis par une bibliothèque peuvent être utilisés dans une expression sans avoir été définis explicitement par l’expression. Par exemple :

Number.E                        // Euler's number e (2.7182...) 
Text.PositionOf("Hello", "ll")  // 2

M comprend un ensemble d’opérateurs qui peuvent être utilisés dans les expressions. Les opérateurs sont appliqués aux opérandes pour former des expressions symboliques. Par exemple, dans l’expression 1 + 2, les nombres 1 et 2 sont des opérandes et l’opérateur est l’opérateur d’addition (+).

La signification d’un opérateur peut varier en fonction du genre de valeurs de ses opérandes. Par exemple, l’opérateur plus peut être utilisé avec d’autres genres de valeurs que des nombres :

1 + 2                   // numeric addition: 3 
#time(12,23,0) + #duration(0,0,2,0) 
                        // time arithmetic: #time(12,25,0)

Un autre exemple d’opérateur avec une signification qui dépend de l’opérande est l’opérateur de combinaison (&) :

"A" & "BC"              // text concatenation: "ABC" 
{1} & {2, 3}            // list concatenation: {1, 2, 3} 
[ a = 1 ] & [ b = 2 ]   // record merge: [ a = 1, b = 2 ]

Notez que certains opérateurs ne prennent pas en charge toutes les combinaisons de valeurs. Par exemple :

1 + "2"  // error: adding number and text isn't supported

Les expressions qui, au moment de l’évaluation, rencontrent des conditions d’opérateur non définies génèrent des erreurs.

Les métadonnées sont des informations sur une valeur associées à une valeur. Les métadonnées sont représentées sous la forme d’une valeur d’enregistrement appelée enregistrement de métadonnées. Les champs d’un enregistrement de métadonnées peuvent servir à stocker les métadonnées d’une valeur.

Chaque valeur dispose d’un enregistrement de métadonnées. Si la valeur de l’enregistrement de métadonnées n’est pas spécifié, l’enregistrement de métadonnées est vide (autrement dit, il n’a aucun champ).

Les enregistrements de métadonnées offrent un moyen d’associer des informations supplémentaires à n’importe quel genre de valeur de manière discrète. L’association d’un enregistrement de métadonnées à une valeur ne change pas la valeur ou son comportement.

Une valeur d’enregistrement de métadonnées y est associée à une valeur existante x à l’aide de la syntaxe x meta y. Par exemple, le code suivant associe un enregistrement de métadonnées composé des champs Rating et Tags à la valeur de texte "Mozart" :

"Mozart" meta [ Rating = 5, Tags = {"Classical"} ]

Pour les valeurs qui contiennent déjà un enregistrement de métadonnées non vide, le résultat de l’application de l’opérateur meta est le calcul de la fusion des enregistrements de métadonnées existant et nouveau. Par exemple, les deux expressions suivantes sont équivalentes l’une à l’autre et à l’expression précédente :

("Mozart" meta [ Rating = 5 ]) meta [ Tags = {"Classical"} ] 
"Mozart" meta ([ Rating = 5 ] & [ Tags = {"Classical"} ])

Il est possible d’accéder à un enregistrement de métadonnées pour une valeur spécifique à l’aide de la fonction Value.Metadata. Dans l’exemple suivant, l’expression du champ ComposerRating accède à l’enregistrement de métadonnées de la valeur dans le champ Composer, puis accède au champ Rating de l’enregistrement de métadonnées.

[ 
    Composer = "Mozart" meta [ Rating = 5, Tags = {"Classical"} ], 
    ComposerRating = Value.Metadata(Composer)[Rating] // 5
]

Dans la plupart des exemples présentés jusqu’à présent, toutes les valeurs littérales de l’expression sont incluses dans le résultat de celle-ci. Avec l’expression let, vous pouvez calculer un ensemble de valeurs, leur affecter des noms, puis les utiliser dans une expression ultérieure qui suit in. Par exemple, dans notre exemple de données de vente, vous pourriez utiliser l’expression suivante :

let 
    Sales2007 =  
        [  
            Year = 2007,  
            FirstHalf = 1000,  
            SecondHalf = 1100, 
            Total = FirstHalf + SecondHalf // 2100 
        ], 
    Sales2008 =  
        [  
            Year = 2008,  
            FirstHalf = 1200,  
            SecondHalf = 1300, 
            Total = FirstHalf + SecondHalf // 2500 
        ] 
  in Sales2007[Total] + Sales2008[Total] // 4600

Le résultat de l’expression ci-dessus est une valeur numérique (4600) qui a été calculée à partir des valeurs liées aux noms Sales2007 et Sales2008.

L’expression if sélectionne entre deux expressions en fonction d’une condition logique. Par exemple :

if 2 > 1 then
    2 + 2
else  
    1 + 1

La première expression (2 + 2) est sélectionnée si l’expression logique (2 > 1) a la valeur true, et la deuxième expression (1 + 1) est sélectionnée si sa valeur est false. L’expression sélectionnée (dans ce cas 2 + 2) est évaluée et devient le résultat de l’expression if (4).

Une erreur indique que le processus d’évaluation d’une expression n’a pas pu produire de valeur.

Les erreurs sont générées par les opérateurs et les fonctions qui rencontrent des conditions d’erreur, ou à l’aide de l’expression error. Les erreurs sont gérées à l’aide de l’expression try. Quand une erreur est générée, une valeur est spécifiée pour indiquer la raison de l’erreur.

let Sales = 
    [ 
        Revenue = 2000, 
        Units = 1000, 
        UnitPrice = if Units = 0 then error "No Units"
                    else Revenue / Units 
    ], 
    UnitPrice = try Number.ToText(Sales[UnitPrice])
in "Unit Price: " & 
    (if UnitPrice[HasError] then UnitPrice[Error][Message]
    else UnitPrice[Value])

L’exemple ci-dessus accède au champ Sales[UnitPrice] et met en forme la valeur qui produit le résultat :

"Unit Price: 2"

Si le champ Units avait eu la valeur zéro, le champ UnitPrice aurait généré une erreur gérée par l’expression try. La valeur résultante aurait été :

"No Units"

Une expression try convertit les valeurs appropriées et les erreurs en une valeur d’enregistrement qui indique si l’expression try a géré une erreur ou non. Cette expression indique également soit la valeur appropriée, soit l’enregistrement d’erreur extrait au moment de la gestion de l’erreur. Par exemple, considérons l’expression suivante qui génère une erreur, puis la gère immédiatement :

try error "negative unit count"

Cette expression a la valeur de l’enregistrement imbriqué suivant, ce qui explique les recherches de champs [HasError], [Error] et [Message] dans l’exemple précédent relatif au prix unitaire.

[ 
    HasError = true, 
    Error = 
        [ 
            Reason = "Expression.Error", 
            Message = "negative unit count", 
            Detail = null 
        ] 
]

Il est courant de remplacer les erreurs par des valeurs par défaut. Dans ce cas, vous pouvez utiliser l’expression try avec une clause otherwise facultative pour obtenir une présentation compacte :

try error "negative unit count" otherwise 42 
// 42