La version de votre navigateur est obsolète. Nous vous recommandons vivement d'actualiser votre navigateur vers la dernière version.

Langage M de Power Query

Power Query est un outil que dispose Excel, et qui permet de créer un classeur qui se connecte aux données externes, les transformer et de créer un modèle de données. Il utilise un langage spécifique qui s’appelle Langage M. En traitant un exemple on va explorer des notions de bases pour ce langage.

Pour construire notre modèle des données en Power Query , on commence par se connecter à la source des données, dans notre exemple c’est un fichier Excel qui s’appelle « État des articles » constitué des colonnes suivantes :

Code Article

Ventes annuelles

Stock

Commande encours

Prévisions

Encours Production

 

Pour ce faire, sous le menu Données, groupe « Récupérer et transformer les données » → Obtenir les données → À partir d’un fichier → À partir d’un classeur, et on choisit notre classeur.

 

L’éditeur des requêtes s’ouvre en mettant à notre disposition les colonnes de la feuille source, et fournit la possibilité de manipuler les données : Modifier les entêtes, modifier les types, ajouter des colonnes, supprimer des colonnes… A chaque fois qu’on effectue une opération, un enregistreur vient de l’ajouter dans un ordre chronologique, pour qu’il reproduise les mêmes étapes en important les données une autre fois.

Dans notre exemple nous allons effectuer les opérations suivantes :

  1. Modifier le titre « Prévision » par « Prévisions annuelles »
  2. Modifier le titre « Commande encours » par « Commande fermes »
  3. Ajouter une colonne qui s’appelle « Quantité Planifiée »
  4. Pour la colonne « Quantité » planifiée » la formule est : « Prévisions Annuelles » +1000
  5. Ajouter une colonne « Alerte »
  6. La formule de cette colonne est si le stock est inférieur à 200 alors écrire « Risque rupture » sinon « État normal »

 

Le résultat est ainsi sur le classeur :

 

 

On va s’intéresser au code M qui a été traduit à ces modifications, et on va effectuer des modifications sur ces opérations en manipulant le code. Afin de visualiser le code M, dans l’éditeur des requêtes on clique sur Affichage à Éditeur avancé :

 

 

Le code M de ces opérations est comme montre la figure suivante :

let
    Source = Excel.Workbook(File.Contents("C:\Users\bilel\Desktop\Etat des articles.xlsx"), null, true),


    Bilan_Sheet = Source{[Item="Bilan",Kind="Sheet"]}[Data],


    #"En-têtes promus" = Table.PromoteHeaders(Bilan_Sheet, [PromoteAllScalars=true]),


    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Code Article", type text}, {"Ventes annuelles", Int64.Type}, {"Stock", Int64.Type}, {"Commande encours", Int64.Type}, {"Prévisons", Int64.Type}, {"Encours Production", Int64.Type}}),


    #"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Prévisons", "Prévisons Anuelles"}, {"Commande encours", "

Commande fermes"}}),
    

    #"Addition insérée" = Table.AddColumn(#"Colonnes renommées", "Addition", each [Prévisons Anuelles] + 1000, type number),


    #"Colonnes renommées1" = Table.RenameColumns(#"Addition insérée",{{"Addition", "Quantité planifiée"}}),


    #"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Colonnes renommées1", "Alerte", each if [Stock] < 200 then "Risque de rupture" else "Etat normal" )


in


    #"Colonne conditionnelle ajoutée"

 

Remarques :

  • Les deux premières lignes ne sont pas en relation avec la succession des opérations, elles concernent plutôt la connexion : Type de la source des données, chemin de classeur, nom de la feuille.
  • La syntaxe   #"Nom" est une manière de créer une table dans le langage M. par exemple     #"En-têtes promus" signifie qu’il crée une table qui s’appelle « En-têtes promus ».
  • La logique du langage M est le suivant : A chaque opération, il crée une table nommée par le nom de l’opération, et qui est le résultat d’une transformation de la table crée juste avant. De cette façon il garde la traçabilité des toutes les opérations faites et peut ainsi reproduire l’enchainement des opérations.
  • Les fonctions de transformation sont de la forme Table.(Opération de transformation) , par exemple Table.Addcolumn pour ajouter des colonnes , Table.RenameColumn pour renommer des colonnes..
  • Le premier argument pour les fonctions de transformation est la table de départ, dans le cas du langage M c’est la table juste avant.
  • Pour désigner les éléments d’une colonne on utilise la syntaxe [Nom de la colonne]. « Nom de la colonne entre [] »

  

Modification sur le code M

Modification 1 : Modifier le code de l’opération 4 pour que la formule devient :

Quantité planifiée=Prévisions annuelles-Stock-Encours production+1000.

Pour ce faire il faut changer le code suivant :

#"Addition insérée" = Table.AddColumn(#"Colonnes renommées", "Addition", each [Prévisons Anuelles] + 1000, type number)

 

Le nouveau code est ainsi :

#"Addition insérée" = Table.AddColumn(#"Colonnes renommées", "Addition", each [Prévisons Anuelles] - [Stock] - [Encours Production] + 1000, type number)

 

Modification 2 : Modifier le code de l’opération 6 pour que l formule devient :

Si Stock<50 alors écrire « Rupture », sinon si Stock < « Commande ferme »+200-Encours production alors « Risque du rupture » sinon « État normal ».

Pour ce faire il faut changer le code suivant : 

 

#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Colonnes renommées1", "Alerte", each if [Stock] < 200 then "Risque de rupture" else "Etat normal" )

 

Le nouveau code est ainsi :

 

#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Colonnes renommées1", "Alerte", each if [Stock] < 200 then "Rupture" else if [Stock]+[Encours Production]-[Commande fermes] <200 then "Risque de rupture" else "Etat normal" )