Introduction à Power Query M Code

Power query est un outil d’extraction et de transformation de données très puissant qui est intégré à Excel 2016 (ou plus), Excel pour Office 365 et Power BI.

Il peut être trouvé dans l’onglet Données dans la section Obtenir & Transformer les données du ruban.

Il est très puissant et aussi très facile à utiliser et l’éditeur de requêtes a une interface utilisateur super intuitive pour un utilisateur d’Excel. De nombreuses étapes de transformation peuvent être facilement effectuées à partir du ruban de l’éditeur de requêtes power query et vous n’avez pas besoin de connaître le moindre code pour nettoyer et façonner vos données.

Dans les coulisses de l’éditeur convivial, Excel traduit chaque étape de votre processus de transformation à partir de ces commandes du ruban dans le langage de code de power query M.

Ce post vous présentera les bases du langage de code M de power query et supposera que vous connaissez déjà les bases de power query.

Qu’est-ce que le code M ?

Le M signifie data Mash-up, car power query consiste à se connecter à différentes sources de données et à les « Mash ».

Le code M est le langage qui se cache dans les coulisses de power query. Lorsque vous créez une transformation de données dans l’interface utilisateur de l’éditeur de power query, Excel écrit le code M correspondant pour la requête.

M est un langage fonctionnel, ce qui signifie qu’il est principalement écrit avec des fonctions qui sont appelées pour évaluer et retourner des résultats. Le code M est livré avec une très grande bibliothèque de fonctions prédéfinies disponibles et vous pouvez également créer vos propres fonctions.

Où pouvez-vous écrire le code M de Power Query ?

Si vous voulez commencer à écrire ou à modifier du code M, vous allez devoir savoir où vous pouvez le faire. Il y a deux endroits où c’est possible, dans la barre de formule ou dans l’éditeur avancé.

La barre de formule

Pour chaque étape qui est créée dans l’interface utilisateur de l’éditeur, vous pouvez voir le code M correspondant dans la barre de formule.

Si vous ne voyez pas la barre de formule, allez sur l’onglet Affichage et assurez-vous que l’option Barre de formule est cochée.

Vous pouvez modifier le code M pour n’importe quelle étape dans une requête en cliquant dans la formule et en modifiant le code existant. Lorsque vous avez terminé, vous pouvez accepter les modifications en cliquant sur la coche ou en appuyant sur la touche Entrée. Vous pouvez également abandonner vos modifications en cliquant sur le X ou en appuyant sur Esc.

Vous pouvez également créer des étapes entièrement nouvelles dans votre requête avec la barre de formule en cliquant sur le symbole fx à côté de la barre de formule. Cela créera une nouvelle étape qui fait référence à l’étape précédente par son nom et ensuite vous pouvez créer n’importe quel code M dont vous avez besoin.

L’éditeur avancé

La barre de formule ne montre que le code M pour l’étape actuellement sélectionnée dans la requête, mais l’éditeur avancé est l’endroit où vous pouvez voir et modifier le code M pour la requête entière.

Vous pouvez ouvrir l’éditeur avancé à partir de deux endroits dans le ruban de l’éditeur. Depuis l’onglet Accueil ou l’onglet Affichage, appuyez sur le bouton Éditeur avancé.

Malgré le moniker « avancé », l’éditeur est l’éditeur de code le plus basique que vous verrez et ne contient pas (encore) d’intellisense auto complète, de coloration syntaxique ou de fonctionnalités de formatage automatique.

L’éditeur avancé affichera le nom de la requête, montrera le code M pour la requête et affichera un avertissement sur toute violation de syntaxe dans le code M. C’est tout !

Bibliothèque de fonctions standard

Puisque le code M est un langage fonctionnel, tout tourne autour des fonctions et le code M est livré avec une grande bibliothèque de fonctions prédéfinies appelée bibliothèque standard.

Des informations sur toutes les fonctions disponibles de la bibliothèque standard peuvent être trouvées sur la page web Power Query M Reference de Microsoft, y compris la syntaxe des fonctions et des exemples.

La bibliothèque standard peut également être explorée à partir de l’éditeur de power query en utilisant le mot clé #shared.

Une fois entré dans la barre de formule, vous pouvez ensuite explorer toutes les fonctions disponibles en cliquant sur le mot Function à droite du nom de la fonction. Vous trouverez la même syntaxe et les mêmes exemples que sur la page web de référence.

Sensibilité à la casse

Une des premières choses dont quelqu’un doit être conscient lorsqu’il écrit du code M est que c’est un langage sensible à la casse.

Cela signifie que x n’est pas la même chose que X ou que « abc » n’est pas la même chose que « ABC ». Ceci est vrai pour toutes les valeurs, variables, fonctions etc.

Expressions et valeurs dans Power Query

Power query est tout au sujet des expressions et des valeurs.

Une expression est quelque chose qui peut être évaluée pour retourner une valeur dans power query. 1 + 1 est une expression qui s’évalue à la valeur 2.

Une valeur est un élément unique de données. Les valeurs peuvent être des valeurs uniques telles que des nombres, du texte, des logiques, des nullités, des binaires, des dates, des heures, des fuseaux horaires ou des durées.

Les valeurs peuvent également avoir des structures plus complexes que des valeurs uniques telles que des listes, des enregistrements et des tables. Vous pouvez également avoir des valeurs qui sont une combinaison de listes, d’enregistrements et de tables. Des listes de listes, des tableaux de listes, des tableaux de tableaux etc… sont toutes des structures de valeurs possibles.

Valeurs littérales simples

Les valeurs littérales simples sont la brique de base de toutes les autres valeurs.

• 123,45 est une valeur numérique.
• « Hello World ! » est une valeur textuelle.
• true est une valeur logique.
• null représentent l’absence de valeur.

Valeurs intrinsèques simples

Les valeurs intrinsèques sont construites en utilisant les différentes fonctions intrinsèques.

• #time(heures, minutes, secondes)
• #date(années, mois, jours)
• #datetime(années, mois, jours, heures, minutes, secondes)
• #datetimezone( années, mois, jours, heures, minutes, secondes, décalage-heures, décalage-minutes)
• #durée(jours, heures, minutes, secondes)

Par exemple, pour construire la date 2018-12-31, il faudrait la construire en utilisant la fonction intrinsèque #date(2018, 12, 31).

Valeurs structurées

Listes

Une liste est une séquence ordonnée de valeurs.

Vous pouvez définir une liste en utilisant des accolades. {1, 2, 3} est une liste contenant les nombres 1, 2 et 3. L’ordre étant important, ce n’est pas la même liste que {3, 2, 1}.

{« Bonjour », « Monde »} est une liste contenant les textes « Bonjour » et « Monde ».

Les listes de listes sont également possibles, ainsi {{1, 2}, {3, 4, 5}} est une liste de deux listes. La première liste contient les chiffres 1 et 2 et la seconde contient les chiffres 3, 4 et 5.

On peut créer des listes séquentielles en utilisant le format {x..y}. {2..5} produira la liste {2, 3, 4, 5}. Cela fonctionne également pour les caractères de texte. {« a ».. « d »} produira la liste {« a », « b », « c », « d »}.

Vous pouvez également avoir une liste sans éléments, {} est la liste vide.

Puisque les listes sont ordonnées, nous pouvons référencer les éléments de la liste avec un numéro d’index basé sur zéro. {1, 2, 3}{2} sera évalué à 3 puisque c’est le 2e élément de la liste (basé sur un indice zéro).

Enregistrements

Un enregistrement est une séquence ordonnée de champs. Chaque champ est constitué d’un nom de champ qui identifie de manière unique le champ et d’une valeur de champ qui peut être n’importe quel type de valeur.

Vous pouvez définir un enregistrement en utilisant des accolades carrées. est un enregistrement avec deux champs. Le premier champ de l’enregistrement a un nom de champ de FirstName et une valeur de « John ». Le deuxième champ dans l’enregistrement a un nom de champ de Age et une valeur de 38.

Les enregistrements d’enregistrements sont également possibles, ] est un enregistrement avec un champ avec un nom de champ de Personne et une valeur de champ qui est un enregistrement.

Les enregistrements vides sont également possibles, est l’enregistrement vide.

Vous pouvez référencer la valeur de champ dans un enregistrement par son nom de champ. sera évalué à « John ».

Tables

Une table est une séquence ordonnée de rangées où chaque rangée est une liste.

Les tables ne peuvent être construites qu’en utilisant une fonction intrinsèque. Vous pouvez construire un tableau en utilisant la fonction #table() à partir d’une liste d’en-têtes de colonnes et d’une liste de lignes.

#table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}) créera un tableau avec 2 colonnes, 3 lignes et les en-têtes de colonnes Lettres et Chiffres.

Il est possible de créer un tableau vide en utilisant des listes vides dans la fonction intrinsèque #table(). #table({}, {}) produira un tableau vide.

Vous pouvez référencer n’importe quelle valeur dans un tableau avec l’indice de ligne basé sur zéro et le nom de l’en-tête de colonne. #table({"Letters", "Numbers"}, {{"A", 1}, {"B", 2}, {"C", 3}}){2} sera évalué à « C » puisque c’est la 2e ligne (basée sur un indice zéro) de la colonne Lettres.

Expressions

Les expressions sont tout ce qui peut être évalué à une valeur. C’est vrai pour les valeurs elles-mêmes. Par exemple, l’expression 1 s’évalue à la valeur 1.

Bien que vous pensiez généralement que les expressions sont constituées d’opérations ou de fonctions plus complexes.

Par exemple :

• L’expression 1 + 1 s’évalue à 2.
• L’expression 3 > 2 s’évalue à vrai.
• L’expression « Hello  » & « World » s’évalue à « Hello World ».
• L’expression Text.Upper(« Hello World ») s’évalue à « HELLO WORLD ».

Opérateurs

A côté de la bibliothèque standard, le code M possède également une liste de fonctions spéciales appelées opérateurs. Ceux-ci prennent exactement deux valeurs (ou des expressions qui évaluent à une valeur) et renvoient une seule valeur.

Arithmétique

Le code M est livré avec les opérateurs arithmétiques de base que vous attendez et auxquels vous êtes habitué à partir des Excel réguliers +, -, * et /. Ceux-ci vous permettront d’ajouter, de soustraire, de multiplier et de diviser des valeurs respectivement.

Ceux-ci peuvent être utilisés avec divers types de valeurs autres que les simples nombres. Par exemple, vous pouvez ajouter une durée à une date.

#date(2018,12,25) + #duration(7, 0, 0, 0) sera évalué à 2019-01-01.

Comparaison

Vous pouvez comparer des valeurs dans le code M en utilisant les opérateurs de comparaison <, >, <=, >=, =, <>.

• x < y sera évalué à vrai si x est inférieur à y.
• x > y sera évalué à vrai si x est supérieur à y.
• x <= y évaluera à vrai si x est inférieur ou égal à y.
• x >= y évaluera à vrai si x est supérieur ou égal à y.
• x = y évaluera à vrai si x est égal à y.
• x <> y évaluera à vrai si x n’est pas égal à y.

Ils peuvent être utilisés avec différents types de valeurs. Par exemple, vous pouvez comparer deux listes avec l’opérateur égal.

{1,2,3,4} = {1,2,3} sera évalué à faux puisque les listes ne sont pas les mêmes.

Concaténation et fusion

Vous pouvez concaténer du texte et fusionner des listes, des enregistrements et des tableaux en utilisant l’opérateur esperluette &.

Par exemple:

"Hello " & "World" sera évalué à « Hello World ».

{1,2,3} & {3,4,5} sera évalué à {1,2,3,3,4,5}.

Logique

Vous pouvez effectuer des opérations sur des valeurs booléennes (ou des expressions qui évaluent à des valeurs booléennes) avec les opérateurs not, and et or.

Commenter le code

Comme on peut s’y attendre de tout langage de programmation, il est possible d’ajouter des commentaires à votre code.

Il existe deux types de commentaires possibles dans le code M. Les commentaires d’une seule ligne et les commentaires multilignes.

Les commentaires d’une seule ligne

M code goes hereM code goes here //This is a single line commentM code goes here

Un commentaire d’une seule ligne peut être créé en faisant précéder le commentaire de deux caractères de barre oblique //. Tout ce qui se trouve sur la même ligne avant cela sera interprété comme du code M, tout ce qui se trouve après sera interprété comme un commentaire.

Commentaires sur plusieurs lignes

M code goes here /*This is a commenton multiple lines*/ M code goes here

Un commentaire sur plusieurs lignes peut être créé en plaçant le commentaire entre les caractères /* et */. Tout ce qui se trouve en dehors de ceux-ci sera interprété comme un code M. Tout ce qui se trouve entre ces derniers sera interprété comme un commentaire.

L’instruction let

L’instruction let permet d’évaluer un ensemble de valeurs et de les affecter à des noms de variables puis de les utiliser dans une expression ultérieure qui suit l’instruction in.

let a = 1, b = 2, c = a + bin c

Cette expression est composée de trois expressions qui sont évaluées après l’instruction let. Chaque expression est séparée par une virgule, sauf la dernière avant l’instruction in. Dans cet exemple, l’ensemble de l’expression let et in sera évalué à 3.

let c = a + b, b = 2, a = 1in c

Vous pourriez penser que les expressions dans une instruction let doivent apparaître dans l’ordre où elles doivent être évaluées. Ce n’est pas le cas ! Le code ci-dessus est parfaitement valide et s’évaluera également à 3. L’évaluateur du code M calculera automatiquement l’ordre des calculs nécessaires en fonction des dépendances des expressions.

Il est évidemment plus facile pour une personne de lire le code M s’il est écrit dans l’ordre d’évaluation, mais il y a aussi un autre avantage. Les expressions apparaîtront comme des étapes séparées dans la fenêtre des étapes appliquées. Lorsqu’elles sont écrites dans le désordre, les expressions apparaîtront comme une étape combinée.

let a = 1, b = 2in a + b

Vous pouvez également évaluer des expressions dans la partie in d’une expression let… in….

Noms de variables

let #"Is this a variable name? Wow!!!" = 1 + 1in #"Is this a variable name? Wow!!!"

Vous pouvez attribuer à peu près n’importe quel nom à vos expressions en utilisant les caractères # » ». Vous pouvez même utiliser des caractères d’espacement et d’autres caractères spéciaux. L’utilisation de mots-clés réservés est la seule exception.

Les noms de variables sont ce qui apparaîtra dans les étapes appliquées de l’éditeur de requêtes, donc être capable d’utiliser des caractères d’espace est une grande fonctionnalité.

Chaque déclaration

L’expression each est un raccourci pour déclarer des fonctions prenant un seul paramètre nommé _ (underscore).

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", each 2*)in #"Added Custom"

Dans cet exemple, nous créons une nouvelle colonne qui multiplie la colonne Numbers par 2 pour chaque ligne.

let Source = #table({"Numbers"}, {{1}, {2}, {3}, {4}, {5}}), #"Added Custom" = Table.AddColumn(Source, "Double", (_) => 2*_)in #"Added Custom"

Nous pouvons créer la même requête en utilisant la syntaxe underscore qui est sémantiquement équivalente à l’expression each. Les deux requêtes fonctionneront de la même manière.

États If Then Else

Le code M est plutôt clairsemé par rapport à d’autres langages lorsqu’il s’agit d’expressions logiques. Il n’y a pas d’instructions select case ou loop disponibles. Il y a seulement une expression if… then… else… disponible.

if then else 

La syntaxe est directe et ressemble à la plupart des autres langages de programmation. Elle peut apparaître tout entière sur une ligne, ou être présentée sur des lignes séparées pour faciliter la lecture.

Enoncés Try Otherwise

Des erreurs peuvent se produire en essayant d’effectuer des opérations qui nécessitent des types particuliers de données. Par exemple, essayer de multiplier un nombre avec une valeur texte entraînera une erreur.

let Source = #table({"Number", "Number and Text"}, {{2, 2}, {2, "Hello"}}), #"Added Custom" = Table.AddColumn(Source, "Product", each try * otherwise 0)in #"Added Custom"

Les erreurs peuvent être évitées en utilisant l’expression try… otherwise….

Cela évitera les erreurs dans les résultats de votre requête et vous permettra de remplacer les erreurs par n’importe quelle valeur ou expression.

Fonctions

Une fonction est un mappage d’un ensemble de valeurs de paramètres vers une valeur. Parallèlement aux fonctions de la bibliothèque standard, le code M vous permet de créer vos propres fonctions.

let Product = (x,y) => x * y, Result = Product(2,3)in Result

Cette requête définit une fonction qui multiplie deux nombres. Ensuite, la requête appelle et évalue la fonction avec les valeurs 2 et 3 qui évalue à 6.

Fonctions avec paramètres optionnels

Il existe deux types de paramètres de fonction, un paramètre requis et un paramètre optionnel.

Les paramètres requis doivent toujours être spécifiés lorsqu’une fonction est invoquée.

Les paramètres optionnels n’ont pas besoin d’être spécifiés lorsqu’une fonction est invoquée. Si le paramètre optionnel est manquant, alors la valeur passée à la fonction sera nulle.

let Product = (x, optional y) => if y is null then x else x * y, Result = Product(2)in Result

Cette fonction a un deuxième argument optionnel. Ensuite, la requête appelle et évalue la fonction en utilisant uniquement le paramètre requis avec une valeur de 2, qui s’évalue à 2. Remarquez que la fonction doit tenir compte du fait que y est nul, sinon un argument facultatif manquant pourrait entraîner une erreur d’évaluation de la fonction.

Fonctions récursives

Il est également possible d’écrire une fonction qui se réfère à elle-même en utilisant l’opérateur de scoping @.

let Fibonacci = (n) => if n = 1 then 1 else if n = 2 then 1 else @Fibonacci(n-1) + @Fibonacci(n-2), Result = Fibonacci(7)in Result

La séquence de Fibonacci est un exemple de fonction définie de manière récursive. Le nombre suivant de la séquence est défini comme la somme des deux nombres précédents. Donc, pour obtenir le nième nombre, vous devez connaître le (n-1)ème et le (n-2)ème nombre.

Cette fonction trouvera le nième nombre de Fibonacci en additionnant le (n-1)ème et le (n-2)ème nombre de Fibonacci.

La requête évalue à 13, puisque 13 est le 7ème nombre de Fibonacci.

Voici d’autres exemples utiles où vous pouvez utiliser des fonctions récursives pour reproduire la fonction TRIM d’Excel afin de supprimer les espaces excédentaires entre les mots ou trouver et remplacer en vrac des valeurs basées sur une liste.

Fonctions de requête

Les exemples ci-dessus ont défini une fonction dans une requête puis ont appelé et évalué la fonction dans la requête.

Il est également possible de créer une requête qui est une fonction (une fonction de requête) et qui peut être appelée et évaluée à partir d’autres requêtes.

let FunctionResult = (Argument1, Argument2,...) => let /*M code to evaluate in the function goes here*/ in Resultin FunctionResult

Voici le format général nécessaire pour créer une fonction de requête. Notez, vous aurez besoin d’une déclaration let… in… dans la déclaration let… in… de la fonction de requête afin d’effectuer plusieurs étapes.

Conclusions

Power query est une grande fonctionnalité dans Excel qui peut vous aider à automatiser et simplifier votre importation et transformation de données.

Vous pouvez faire beaucoup en utilisant juste l’interface graphique de type pointer-cliquer sans jamais toucher le code M. Mais au fur et à mesure que vos besoins deviennent plus avancés, il peut y avoir un moment où vous devez modifier le code M que vous avez créé ou écrire le vôtre à partir de zéro.

Tout nouveau langage de programmation est illisible si vous ne connaissez pas d’abord les bases. Ce guide vous permettra, nous l’espérons, d’être opérationnel avec le code M afin que vous puissiez commencer à créer des requêtes plus avancées.