Blog ENI : Toute la veille numérique !
💥 Un livre PAPIER acheté
= La version EN LIGNE offerte pendant 1 an !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. Power Query et M
  3. Guide pratique du code M
Extrait - Power Query et M Extraire et préparer les données en vue de leur exploitation dans Excel ou Power BI
Extraits du livre
Power Query et M Extraire et préparer les données en vue de leur exploitation dans Excel ou Power BI
10 avis
Revenir à la page d'achat du livre

Guide pratique du code M

Introduction

Ce chapitre a pour objectif de vous permettre de comprendre un peu mieux le code M, sans pour autant aller jusqu’à coder directement dans ce langage : l’idée est de savoir améliorer le code généré par l’interface, le rendre plus dynamique et plus puissant, ceci dans une perspective résolument pratique.

Nous allons dans un premier temps préciser les aspects généraux de ce code, sa librairie de fonctions, et les types d’éléments que vous y trouverez, afin de mieux comprendre ce qu’attendent les différentes fonctions en termes de paramètres.

Nous verrons la création de fonctions personnalisées, puis nous ferons un récapitulatif structuré dans différents exemples que nous avons pu voir, en élargissant à chaque fois sur de nouvelles « recettes », autrement dit des techniques courantes pour extraire de l’information à l’aide du code.

Présentation générale du code M

Le code M est un langage fonctionnel, c’est-à-dire qu’il se compose d’un ensemble de fonctions, la librairie, utilisées dans le cadre d’étapes de transformations, dans lesquelles chaque fonction attend un certain type de paramètre, des types dits « primitifs » (date, nombre, etc.) et des types dits « complexes » (des listes, des enregistrements, des tables ou des fonctions).

Bien comprendre le type de paramètre qu’une fonction attend est la base du code M.

DAX ou M ?

Nous avons évoqué dans cet ouvrage la création d’une table du temps en code M. Mais vous le savez déjà, cette table peut facilement être générée avec du code DAX. Alors à quoi bon ?

Il est vrai que M et DAX se recoupent par endroit. Ces deux langages ont été développés totalement indépendamment l’un de l’autre, et n’ont été rassemblés dans un même outil que récemment. Ceci explique l’existence de redondances telles que la création de la table du temps.

Alors, comment choisir lorsque la même chose (table du temps ou toute autre transformation) peut être faite dans l’un ou dans l’autre langage ?

C’est d’abord la préférence et la familiarité de l’utilisateur pour l’un ou l’autre qui pourra faire pencher la balance.

Un deuxième facteur, plus critique, repose sur le fait que dans bien des cas, le code M est « plié », autrement dit traduit dans le langage de la source (SQL par exemple) : c’est donc la machine où se trouve la source qui fait le travail, plutôt que le poste utilisateur. Lorsque vous manipulez une table avec des millions de lignes, la différence peut être très significative.

Dans le même esprit, si vous agrégez une table en M « plié » plutôt qu’en DAX, le volume de données transféré sur votre poste est fortement réduit, l’application fonctionnera donc mieux, et vous augmentez vos chances de rester dans les limites de volume propres à chaque licence.

Enfin, n’oublions pas que le code M sert en amont...

Réutiliser le code M entre plusieurs requêtes ou documents

L’intérêt du code M, c’est qu’il peut être copié et collé, et être donc réutilisé dans une autre requête, voire dans un autre document. La représentation graphique des étapes qui apparaissent dans le volet à la droite de votre fenêtre Power Query ne peut pas, elle, être portée ailleurs.

Mais paradoxalement, il n’existe pas de fonctionnalité toute prête pour faciliter cet export du code. Reste donc le très rudimentaire, mais efficace, copier-coller.

Encore faut-il garder en tête les règles de base. Dupliquer une ou plusieurs lignes suppose de modifier et de vérifier un ensemble de points :

  • Gardez d’abord en tête que la première ligne du code que vous avez copié contient le plus souvent une référence au nom de l’étape qui a précédé dans la requête initiale. Il faudra évidemment le modifier.

  • Soyez également attentif à la virgule à la fin de la ligne : toutes les étapes sont terminées par une virgule, sauf la dernière étape avant le IN.

  • Si vous collez le bout de code au milieu d’une requête, il faudra là encore raccorder la dernière étape du script collé avec...

Créer et utiliser une fonction personnalisée

Lorsque vous appliquez des transformations à une table ou une colonne, ces opérations sont directement associées à cette table ou cette colonne. Créer une fonction personnalisée consiste à nommer l’ensemble de ces opérations pour être en mesure de les appliquer rapidement à d’autres tables ou d’autres colonnes.

Ce sont donc généralement des opérations fréquentes, ou du moins courantes, ou encore un enchaînement complexe de nombreuses opérations.

La fonction personnalisée n’existe que dans le document où elle a été créée : elle peut être appliquée à toutes les entités de ce document, mais ne peut pas être utilisée dans d’autres documents. Un simple copier-coller permet cependant de recréer la fonction dans un autre fichier.

Créer la fonction personnalisée

Une fonction est composée de trois éléments :

  • un nom,

  • un ou plusieurs paramètres en entrée (une table, une colonne ou une valeur par exemple),

  • une expression (l’ensemble des lignes de code).

Voyons tout de suite un exemple : dans le fichier source09, nous avons « nettoyé » une colonne de la table Customer contenant des numéros de téléphone....

Travailler avec les tables

Les tables sont la structure la plus courante dans le code M. Voyons d’abord quelques règles courantes de manipulation des tables, puis un ensemble d’exemples basés sur des tables.

Manipulations courantes avec les tables

Pour accéder à une colonne de la table, utilisez la syntaxe :

= table[colonne] 

table est le nom de la table et colonne le nom de la colonne.

Pour accéder à une ligne de la table, utilisez la syntaxe :

= table{ligne} 

table est le nom de la table et ligne le numéro de la ligne moins 1 : l’index commence à zéro, et par conséquent table{0} indique la première ligne de la table.

Par combinaison des deux précédentes syntaxes, le code suivant indique la première ligne de la colonne1 de la table :

= table[colonne1]{0} 

Une table peut être créée manuellement à l’aide la syntaxe #table :

= #table 
( 
    {"ID", "Name", "City"},  
    { 
      {123, "Alice", "Wonderland"}, 
      {234, "Bob", "Wonderland"} 
    } 
) 

Fonctions de table courantes

Le nombre de fonctions de table est très important. Le but de cette section est de n’en...

Travailler avec les listes

Manipulations courantes avec les listes

Une liste est le résultat d’une fonction de type liste, mais la création manuelle d’une liste est également simple, à l’aide d’accolades et de séquences de n’importe quel type (nombre, date, texte notamment) :

Pour créer une liste de 1 à 100 :

= {1..100} 

Pour créer une liste de 1 à 100 puis de 200 à 250 :

= {1..100, 200..250} 

Pour créer une liste de dates (par exemple en vue de la création d’une table du temps :

= List.Dates(#date(2020, 1, 1), 366, #duration(1, 0, 0, 0)) 

Ici, la liste retourne toutes les dates à partir du 1er janvier 2020, auxquelles sont ajoutés 366 jours (#duration indique ici un intervalle de 1 jour).

Les listes peuvent être combinées à l’aide de l’opérateur &. Nous avons notamment vu l’exemple de la liste ensemble, créée à partir de 4 autres listes :

ensemble = majuscules & minuscules & accents & signes 

Enfin pour accéder à une valeur de la liste, il suffit d’en indiquer la position à partir de zéro :

= {"A", "B", "C"}{2} 

Retourne « C ».

Fonctions de liste courantes

Le nombre de fonctions de liste est très important....

Travailler avec les enregistrements (records)

Si les listes peuvent être considérées comme des colonnes de données, les enregistrements sont eux des lignes de données. Ils sont toujours définis par une paire nom du champ et valeur du champ.

Manipulations courantes avec les enregistrements

Ainsi la syntaxe suivante (notez les crochets) crée une ligne de données sous forme de liste. Deux colonnes sont définies, et leur contenu indiqué :

images/06sob011.png

Pour créer plusieurs enregistrements, ajoutez des accolades au début et à la fin, et séparez les enregistrements par une virgule :

images/06sob012.png

Power Query retourne alors une liste d’enregistrements Record qui contiennent chacun les données.

Enfin, il est parfois utile de retrouver un enregistrement dont vous ne connaissez que l’identifiant (ici, le symbole). Attention, cette recherche est effectuée sur une table (et pas une liste) :

images/06sob013.png

Fonctions d’enregistrement courantes

Les fonctions d’enregistrements sont moins utilisées, mais en voici tout de même un ensemble d’exemples :

Record.AddField()

Pour ajouter un champ à un enregistrement.

Record.RemoveFields()

Pour enlever un champ à un enregistrement.

Record.RenameFields()

Pour renommer un champ dans un enregistrement.

Record.Field()

Pour extraire la valeur d’un champ dans un enregistrement.

Record.FieldCount()

Pour...

Les fonctions Texte, Date et Nombre

La liste de fonctions est trop importante pour les limites de cet ouvrage, et leur utilisation est en général simple à comprendre. Aussi je vous renvoie avant toute chose à la librairie des fonctions (rappelez-vous, #shared) ou à la documentation en ligne, très complète :

L’expression each

Le mot-clé each n’est pas le plus simple à comprendre. Il s’agit en l’occurrence d’un raccourci de syntaxe, permettant de définir une fonction simple.

Ou pour citer l’éditeur, « each-expression est un raccourci syntaxique pour déclarer des fonctions non typées qui prennent un paramètre formel unique nommé _ . Les déclarations simplifiées sont couramment utilisées pour améliorer la lisibilité de l’appel de fonction d’ordre supérieur».

Je reprends ici l’explication claire qu’en donne Gil Raviv dans son ouvrage.

Une fonction est déclarée à l’aide du symbole =>. Ainsi, l’expression suivante définit-elle une fonction qui reçoit un nombre (x) et retourne x+1 :

(X) => X + 1 

La même expression peut être écrite à l’aide du tiret bas :

(_) => _ + 1 

each permet de simplifier cette écriture, pour un résultat équivalent :

each _ + 1 

Dans certaines situations, le tiret bas peut être omis, notamment lorsque l’expression fait référence à une colonne ou à une table.

Prenons un exemple. Lorsque vous filtrez les valeurs d’une colonne, Power Query génère le code suivant :

Table.SelectRows( Source, each [Prix]...

Comprendre l’actualisation incrémentielle

Lors de l’actualisation d’un rapport, l’intégralité des données présentes dans le rapport (E) est effacée, et remplacée par l’intégralité des données présentes dans la source, c’est-à-dire l’ensemble E (dont certains enregistrements ont pu être modifiés) plus les nouveaux enregistrements (apparus depuis la précédente actualisation).

C’est ce remplacement complet du jeu de données qui peut, à mesure qu’augmente le nombre d’enregistrements dans la source, allonger la durée d’actualisation du rapport.

Il est possible de mettre en place des stratégies d’actualisation pour agir sur ce point : découper une grande table en plusieurs sections et n’actualiser que la dernière section, en prenant en compte la possibilité que les anciens enregistrements puissent être modifiés. Imaginez par exemple une très grande table dans laquelle sont stockés les passages en caisse pour un hypermarché. Les transactions, une fois enregistrées, ne changent pas, à moins qu’un client ne ramène un produit. Si la politique de retour est limitée à 7 jours, il est possible, moyennant quelques contorsions de la requête, de ne récupérer qu’une partie des enregistrements (après le 7 de chaque mois, uniquement ceux du mois en cours ; avant...

Extraire les données sur plusieurs pages d’un site avec le code M

Cette section aurait pu trouver sa place dans le chapitre Exploiter la puissance de Power Query : je l’intègre dans celui-ci, consacré au code M, dans la mesure où nous allons y combiner des éléments de transformations avancés (où nous allons modifier le code M dans l’éditeur), la création d’une fonction, et la création d’une liste, soit une bonne partie des éléments que nous venons d’évoquer.

L’exemple que je vous propose est le suivant : extraire la liste des billets d’un blog et en créer une sorte de table des matières. En l’occurrence, nous allons extraire cette liste du blog que je consacre à Power BI, www.daxone.fr.

La première difficulté ici, c’est l’absence de tableau explicite en HTML, mais nous avons vu que Power Query est capable de suggérer des tables ou de comprendre à partir d’exemples le besoin. Ceci devrait donc être un obstacle mineur.

La deuxième difficulté, c’est que les billets du blog (de même, par exemple, que les résultats d’une recherche), s’étalent sur plusieurs pages du site : il va donc falloir trouver un moyen pour que la requête soit exécutée sur toutes les pages. Et avant même cela, il va falloir comprendre comment est libellée l’adresse de chaque page.

Lorsque vous saisissez l’adresse du blog dans un navigateur, vous arrivez sur une liste de billets, le plus récent en haut de page. Il y a 10 billets sur la première page, dont le lien, récupéré dans la barre d’adresse, est https://daxone.fr/. En bas de cette page, un lien permet d’accéder aux entrées précédentes, et par clic-droit > copier l’adresse...

Où trouver des ressources sur M ?

J’ai cité dans l’introduction des sites de référence sur Power Query en général.

Dans cette section, citons plus spécifiquement des sites connus pour être des sources très pointues sur le code M :

Et par ailleurs, du côté de l’éditeur :