Créer une fonction en code M

Vous pouvez créer une fonction en code M avec vos règles métiers personnalisées, grâce à Power Query dans Power BI.

L’intérêt d’une fonction est d’automatiser un traitement. Une fonction Power BI se crée à partir d’une requête. Il est fortement recommandé que la requête utilise un paramètre. Ce paramètre sera passé comme « variable » à la fonction. Le paramètre peut être de type Texte, etc.

Il est fortement conseillé de lire et comprendre les explications de l’article sur comment Créer une fonction Power Query, avant de se lancer dans cet exercice.

Dans cet exercice avancé, vous allez créer de A à Z une fonction personnalisée en code M.

Cette fonction va calculer la formule du modèle de Wilson pour une liste de fruits.

Par ailleurs, cette formule sert à déterminer la quantité optimale d’une commande pour réapprovisionner un stock. En effet, cette quantité est aussi nommée Quantité Economique à Commander (QEC). Sans rentrer dans les détails, l’objectif de la QEC est de minimiser le coût du stock, sans être en rupture de stock.

Notamment, pour déterminer la QEC, il faut connaître le coût unitaire du produit, sa consommation annuelle, le coût associé à chaque commande et le taux de possession du stock (entreposage, etc.)

La formule précise est donnée plus bas.

Gardez à l’esprit que l’interface a pu évoluer fortement depuis que cet article a été rédigé.

Ouvrir Power Query pour créer une fonction en code M

Premièrement, créer un nouveau fichier Power BI Desktop vierge nommé Rapport_Lab2D.pbix.

Ensuite, dans le groupe Requêtes, cliquer sur la partie supérieure du menu Transformer les données pour ouvrir l’éditeur Power Query.

Connaître les données pour créer une fonction en code M

Ensuite, avec Microsoft Excel, ouvrir le fichier Data_Fruits_Statistiques.xlsx afin de prendre connaissance de sa structure et de ses données.

En effet, chaque ligne de ce fichier contient le nom d’un fruit, son coût unitaire en € HT / kg, et sa consommation annuelle en kg.

Par ailleurs, dans cet exemple simplifié :

• Le coût associé à chaque commande est fixe : 44 €
• Le taux de possession est fixe : 15 %

Ensuite, fermer le fichier Excel.

Se connecter à un fichier Excel

Puis, cliquer sur le ruban Accueil de Power Query puis cliquer sur la partie haute du menu Nouvelle source : le volet Obtenir les données s’ouvre.

Ensuite, sélectionner le choix Classeur Excel puis cliquer sur le bouton Se connecter et ouvrir le fichier Data_Fruits_Statistiques.xlsx : le volet navigateur s’ouvre.

Ensuite, cocher la case du tableau Fruits.

Puis cliquer sur le bouton OK.

De plus, noter la présence de la requête Fruits dans le volet Requêtes (à gauche).

Requête vide pour créer une fonction en code M

Premièrement, vous allez créer une fonction en code M à l’aide d’une requête vide.

Notamment, la fonction va calculer la quantité économique à commander (QEC).

Si vous ne savez pas ce qu’est la QEC, lisez les explications de la partie Objectifs de cet exercice.

Pour créer la fonction, cliquer sur le ruban Accueil de Power Query puis cliquer sur la partie basse du menu Nouvelle source, puis cliquer sur Requête vide : une zone de formule vierge s’ouvre.

Ensuite, vous allez agrandir la zone de formule.

Pour ce faire, dans le ruban Accueil, cliquer sur le menu Éditeur avancé : un pop-up s’ouvre avec la requête Requête1.

Pour vous aider, regarder la copie d’écran ci-dessous.

Créer une fonction en code M avec la formule de calcul

Maintenant, vous allez créer la fonction personnalisée avec la formule de calcul.

Notamment, la formule nécessite 4 paramètres (cf. supra) :

• ConsommationAn : Consommation annuelle du produit
• CoûtCommande : Coût associé à chaque commande
• CoûtUnitaire : Coût unitaire du produit
• TauxPossession : Taux de possession du stock

Pour vous aider, regarder la copie d’écran ci-dessous.

Aussi, la traduction dans Requête1 en code M est immédiate :

let QEC = (ConsommationAn,CoûtCommande,CoûtUnitaire,TauxPossession) =>

Number.Sqrt((2*ConsommationAn*CoûtCommande)/(CoûtUnitaire*TauxPossession))

in QEC

En effet, la fonction Power Query Number.Sqrt() retourne la racine carrée du nombre.

Pour vous aider, regarder la copie d’écran ci-dessous.

Ensuite, cliquer sur le bouton OK  : la fonction est créée.

Pour vous aider, regarder la copie d’écran ci-dessous.

Puis dans le volet Requêtes [2] (à gauche), renommer Requête1 en Calcul de la QEC.

Une fois que la fonction est définie, il faut l’appeler.

Appeler une fonction personnalisée

Maintenant, vous allez appeler la fonction personnalisée.

Pour ce faire, cliquer sur la requête Fruits.

Ce clic sert uniquement à désélectionner la fonction. En effet, lorsqu’une fonction est sélectionnée, il n’est pas possible d’appeler une fonction personnalisée ou d’ajouter une nouvelle colonne.

Ensuite, cliquer sur le ruban Ajouter une colonne puis cliquer sur Appeler une fonction personnalisée.

Pour vous aider, regarder la copie d’écran ci-dessous.

Ensuite, renommer colonne en QEC calculée

Puis indiquer la fonction sélectionnée Calcul de la QEC

Puis indiquer les valeurs à transmettre aux paramètres :

• ConsommationAn : colonne Consommation annuelle
• CoûtCommande : 44
• CoûtUnitaire : colonne Coût unitaire
• TauxPossession : 15%

Pour vous aider, regarder la copie d’écran ci-dessous.

Ensuite, cliquer sur le bouton OK.

Puis, typer la colonne QEC Calculée en Nombre entier.

Pour vous aider, regarder la copie d’écran ci-dessous.

Fermer & appliquer Power Query

Enfin, dans le ruban Accueil (en haut), cliquer sur Fermer et appliquer.

Formater un champ

Vous allez formater en milliers le champ QEC calculée.

Pour ce faire, ouvrir le volet Champs (à droite).

Ensuite, cliquer sur la petite flèche à gauche du nom Fruits, sous le volet Champs. En effet, cela permet d’afficher tous les champs.

Il y a deux manipulations distinctes dans le volet Champs : Cocher un champ ou Sélectionner un champ. Dans le cas présent, il faut sélectionner le champ.

Notamment, pour sélectionner le champ QEC calculée, il faut cliquer sur le nom QEC calculée (et non pas cocher la case).

La sélection est faite, quand toute la ligne du champ est grisée.

Ensuite, dans le ruban Outils de colonne (en haut), cliquer sur la virgule, sous Format. La virgule sert de séparateur de milliers.

Ajouter un visuel de type Table

Maintenant, cliquer sur une partie vierge quelconque du rapport.

Ensuite, ouvrir le volet Visualisations (à droite).

Puis ajouter un objet visuel de type Table. C’est celui qui ressemble à une feuille de calcul Excel.

Ensuite, dans l’onglet Ajouter des données à votre visuel, ajouter dans Colonnes :

• Produit
• QEC calculée

Enfin, dans l’onglet Mettre en forme votre visuel, rechercher et modifier :

• Taille
  • Hauteur : 640
  • Largeur : 300
• Position
  • Horizontale : 10
  • Verticale : 0
• Présélections de style
  • Style : En-tête en gras

Enregistrer son travail

Puis enregistrer le fichier Rapport_Lab2D en cliquant sur la disquette en haut et à gauche.

Fermer le fichier Power BI Desktop

Enfin, fermer le fichier Rapport_Lab2D.pbix.

La manipulation est terminée.