Formule QEC

Créer une fonction personnalisée de calcul en code M dans Power Query

Vous avez la possibilité de créer vos propres fonctions personnalisées 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 Créer la fonction d’import par lot, 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.

Cette formule sert à déterminer la quantité optimale d’une commande pour réapprovisionner un stock. 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.

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.

Manipulations

Ouvrir Power BI Desktop

Créer un nouveau fichier Power BI Desktop vierge nommé Rapport_Lab2D.pbix.

Ouvrir Power Query

Dans le groupe Requêtes, cliquer sur la partie supérieure du menu Transformer les données : cela ouvre l’éditeur Power Query.

Se connecter à un fichier Excel

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

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

Dans cet exemple simplifié :

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

Fermer le fichier Excel.

Ensuite, 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.

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.

Cocher la case du tableau Fruits.

Puis cliquer sur le bouton OK.

Noter la présence de la requête Fruits dans le volet Requêtes (à gauche).

Créer une requête vide

Vous allez créer une fonction personnalisée à l’aide d’une requête vide.

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.

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.

Requête1
Requête1

Créer une fonction

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

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.

Formule QEC
Formule QEC

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

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

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

Formule QEC Langage M
Formule QEC Code M

Cliquer sur le bouton OK  : la fonction est créée.

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

Formule QEC Résultat
Formule QEC Résultat

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.

Appeler une fonction personnalisée
Appeler une fonction personnalisée

Renommer colonne en QEC calculée

Indiquer la fonction sélectionnée Calcul de la QEC

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.

Paramètres de l'appel de la fonction
Paramètres de l’appel de la fonction

Cliquer sur le bouton OK.

Typer la colonne QEC Calculée en Nombre entier.

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

Contenu de la table
Contenu de la table

Fermer & appliquer Power Query

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. 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.

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.

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.

Ouvrir le volet Visualisations (à droite).

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

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

  • Produit
  • QEC calculée

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

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

QEC Calculée pour chaque fruit (extrait)
QEC Calculée pour chaque fruit (extrait)

Enregistrer son travail

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

Fermer le fichier Power BI Desktop

Fermer le fichier Rapport_Lab2D.pbix.

Résumé

Vous avez appris à créer votre propre fonction personnalisée en code M de Power Query à partir d’une requête vide.

Ensuite, vous avez pu appliquer cette fonction sur une requête de données.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.