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.
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.
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.
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.
Cliquer sur le bouton OK : la fonction est créée.
Pour vous aider, regarder la copie d’écran ci-dessous.
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.
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.
Cliquer sur le bouton OK.
Typer la colonne QEC Calculée en Nombre entier.
Pour vous aider, regarder la copie d’écran ci-dessous.
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.
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.
Pour aller plus loin
Téléchargez gratuitement en PDF mon support de cours pour apprendre à créer des rapports avec Power BI Desktop. Il comprend l’exposé des notions (185 pages) et plus de 50 travaux pratiques corrigés de Power BI.
Laisser un commentaire