(Last Updated On: )

Voici une super méthode qui utilise le connecteur Power Automate “Exécuter une requête sur un jeu de données” pour exporter automatiquement les données de vos rapports Power BI.

⚡️ En utilisant des techniques d’automatisation no-code comme celle-ci, vous passerez clairement un niveau supérieur dans votre pratique de la BI self-service.

Mes clients me demandent souvent comment ils peuvent récupérer un visuel Power BI dans un fichier Excel.

Car ils souhaitent travailler sur les données, notamment calculer des indicateurs ou effectuer des analyses comparatives.

Il existe plusieurs méthodes pour récupérer des données d’un rapport Power BI dans Excel, à savoir :

• Exporter un visuel en CSV ou en Excel
• Connecter un fichier Excel au jeu de données Power BI (fonctionnalité “Analyser dans Excel“)
• Power BI Desktop & DAX Studio

Cependant, ces méthodes requièrent toutes à un moment donné d’effectuer une action de la part de l’utilisateur :

• Consulter le rapport et exporter le visuel,
• Ouvrir le fichier Excel connecté et rafraichir les données, etc.

J’ai donc cherché s’il n’y avait pas une manière pour automatiser l’exportation de visuels Power BI (essentiellement des tableaux) automatiquement dans Excel,

…et cerise sur le gateau, envoyer le fichier Excel par e-mail 📧.

Pour cela, Power Automate est l’outil auquel j’ai tout de suite pensé.

Avantages de la méthode :

• C’est automatisé : pas d’action requise de l’utilisateur
• C’est paramétrable (comme une requête DAX)
• C’est utilisable par tous : en exportant les données dans Excel les utilisateurs restent dans un environnement familier
• C’est adaptable : on peut imaginer une multitude de scenarios possibles

Inconvénients (il y en a toujours) :

• Limitations du nombre de lignes maximum exportées (voir détails à la fin de l’article)
• Il faut une licence Power Automate (peut être incluse ou pas dans votre abonnement Microsoft 365)
• Il faut bien maîtriser le langage DAX pour créer des exports un peu complexes

Power Automate

Power Automate est une solution Microsoft qui vous aide à créer des flux de travail automatisés entre vos applications et services favoris pour synchroniser des fichiers, recevoir des notifications, collecter, etc…

Fonctionnalités de Power Automate :

Automatiser vos processus d’entreprise	Mettre en place des flux d’approbation pour vos tâches essentielles	Améliorer vos flux de travail avec l’IA	Se connecter à plus de 500 sources de données ou à toute API accessible au public
Donnez à chacun les moyens de créer des processus automatisés au moyen d’outils de glisser-déplacer low-code.	Renforcez et sécurisez l'efficacité de vos processus de bout en bout à l’aide de l’approbation de tâches	Traitez rapidement les formulaires à l’aide de l’automatisation des documents, et traitez les approbations, détectez les images et le texte ou créez avec des modèles prédéfinis.	Grâce à des centaines de connecteurs prédéfinis, à des milliers de modèles et à l’aide de l’IA, il est très simple d’automatiser facilement les tâches répétitives.

Ici, nous allons utiliser une nouvelle action Power Automate [mai 2022] pour exécuter des requêtes contre des ensembles de données Power BI.

Cette action offre une expérience simple, sans code, aux utilisateurs de Power BI qui souhaitent automatiser les tâches d’extraction de données.

Voici donc la méthode !

Regarder la vidéo

Scenario

Nous allons créer un Flux Power Automate réalisant les actions suivantes :

1. Exporter les données de Power BI avec une requête DAX
2. Convertir le résultat en table CSV
3. Convertir la table CSV en Excel via un script Office
4. Enregistrer le résultat dans un fichier Excel stocké sur Onedrive
5. Envoyer le fichier par email en pièce jointe

Partie 1 : Exporter les données de Power BI

Pour commencer, afin d’exporter les résultats des données de Power BI, il faut d’abord :

1. Publier son rapport Power BI dans un espace de travail Power BI Online
2. Créer une requête DAX permettant d’extraire les données qui nous intéressent sous forme de table

Commencez par ouvrir votre rapport Power BI depuis Power BI Desktop, puis Publiez-le dans un espace de travail

Générer la requête DAX depuis le rapport Power BI

Maintenant, on va créer la requête DAX pour exporter les données.

Pour cette étape, rassurez-vous : vous n’avez même pas besoin de connaître le langage DAX !

Car vous pouvez copier des requêtes directement depuis Power BI Desktop en utilisant l’outil Analyseur de performances :

• Allez dans la page du visuel que vous souhaitez exporter
• Appliquez les filtres et sélections qui vous intéressent
• Lancez l’Analyseur de performances depuis le menu Afficher
  1. Démarrez l’enregistrement
  2. Actualisez les visuels
  3. Arrêtez
  4. Sélectionnez le visuel (ici la table) et copiez la requête DAX dans le presse-papier

Modifier la requête DAX avec l’utilitaire DAX Studio (étape facultative)

Si on copie-colle la requête dans un éditeur, ici DAX Studio, on peut modifier le code DAX pour générer le visuel à exporter (ici une table) :

💡 Vous pourriez par exemple vouloir modifier les filtres appliqués ou l’ordre de tri des colonnes.

Créer le flux Power Automate depuis Zéro

Rendez-vous sur flow.microsoft.com, et créez un nouveau flux de cloud instantané (à déclenchement manuel) :

Ajoutez une nouvelle étape, recherchez Power BI dans la zone de recherche du connecteur.

Puis sélectionnez l’action intitulée Exécuter une requête sur un jeu de données, et collez la requête DAX :

💡 Vous pouvez tout à fait inclure des éléments dynamiques dans le texte de la requête ! (attention aux formats des valeurs par contre)

Générer un tableau csv

Pour récupérer le résultat de la requête, nous allons créer un tableau CSV via l’action du même nom dans Power Automate.

Insérez une nouvelle étape, recherchez l’action Créer un tableau CSV  et sélectionnez le contenu dynamique de l’action précédente “Lignes de la première table” :

Et voilà, à ce stade vous pouvez enregistrer et tester votre flux.

La table exportée sera sous la forme d’une seule ligne de texte, mais vous pourrez vérifier que les valeurs de Power BI sont bien là.

Partie 2 : Générer un fichier Excel à partir des données extraites en csv

Maintenant que le flux extrait les données de Power BI sous forme de tableau CSV, nous allons convertir le CSV en fichier Excel.

📌 A noter : j’ai cherché des connecteurs Power Automate effectuant la conversion CSV vers XLSX, cela existe mais c’est payant (Plumsail par exemple).

Du coup, pour la suite je n’ai utilisé que des connecteurs standards, et je me suis servi de ce script Office Excel.

Car on peut exécuter des scripts Office depuis Power Automate (contrairement aux macro VBA).

Créer un template de fichier Excel Online

Allez dans Excel Online : depuis Office.com, ouvrez Excel et créez un nouveau fichier vide.

Allez dans Automatiser, Nouveau script, l’éditeur de code apparaît à droite. Vous allez remplacer le contenu par le script Office ci-dessous.

Copiez le code ci-dessous dans l’éditeur de script Office. Voici également le lien pour récupérer le script Office de conversion csv en excel (modifié).

/** * Convert incoming CSV data into a range and add it to the workbook. * Source : https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/convert-csv * */function main(workbook: ExcelScript.Workbook, csv: string) { let sheet = workbook.getFirstWorksheet(); // Modification du script// Test Script : enlever commentaires puis Run /* csv = `Colonne 1, Colonne 2, Colonne 3, 1, 2, 3 4, 5, 6 7, 8, 9`; */// Remove any Windows \r characters. csv = csv.replace(/\r/g, “”);// Split each line into a row. let rows = csv.split(“\n”); /* * For each row, match the comma-separated sections. * For more information on how to use regular expressions to parse CSV files, * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753 */ const csvMatchRegex = /(?:,|\n|^)(“(?:(?:””)*[^”]*)*”|[^”,\n]*|(?:\n|$))/g rows.forEach((value, index) => { if (value.length > 0) { let row = value.match(csvMatchRegex);// Check for blanks at the start of the row. if (row[0].charAt(0) === ‘,’) { row.unshift(“”); }// Remove the preceding comma. row.forEach((cell, index) => { row[index] = cell.indexOf(“,”) === 0 ? cell.substr(1) : cell; });// Create a 2D array with one row. let data: string[][] = []; data.push(row);// Put the data in the worksheet. let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length); range.setValues(data); } });/********************************************* * Ajouts du 15/12/2022 * Source : https://xlbusinesstools.com *********************************************/// Get the range containing all the cells with data or formatting. let usedRange = sheet.getUsedRange();// Convert it to a table let newTable = workbook.addTable(usedRange.getAddress(), true);}

📌 A noter : par rapport au code d’origine, j’ai modifié le séparateur de colonne pour avoir des virgules.

Voilà, il ne reste plus qu’à enregistrer ce template Excel sur votre Onedrive personnel ou professionnel (ici j’utilise mon Onedrive professionnel).

Insérer le script Office et générer fichier Excel

Retournez dans le flux Power Automate, et insérez une nouvelle étape, recherchez Onedrive, Obtenir le contenu du fichier

Cliquez sur l’icone de Dossier de l’action, et sélectionnez le fichier template Excel que vous venez de créer.

Insérez une nouvelle étape, action Onedrive, Créer un fichier :

• Chemin d’accès : renseignez le chemin où sera créé le fichier Excel contenant les données Power BI exportées.
• Nom de fichier : renseignez un nom de fichier Excel. Par exemple Export.xlsx. N’oubliez pas l’extension .xlsx.
• Contenu : Insérez le contenu dynamique Contenu du fichier de l’étape précédente Obtenir le contenu du fichier

💡 Vous pouvez ajouter du contenu dynamique ici. Par exemple, j’ai ajouté la date et l’heure actuelle pour conserver un historique des fichiers Excel exportés.

Insérez une nouvelle étape, action Excel, Exécuter le script :

• Allez récupérer l’emplacemment du script : Onedrive, bibliothèque Onedrive (dans mon cas)
• Fichier : utilisez le contenu dynamique ID de l’étape Créer un fichier
• Script : sélectionnez le script Office que vous avez créé
• csv : utilisez le contenu dynamique Sortie de l’étape Créer un tableau CSV

Et voilà, à ce stade le flux Power Automate est opérationnel, testez-le et vous verrez apparaître un fichier Excel avec vos données Power BI !

 

Partie 3 : Envoyer le fichier Excel par email

Last but not least…

Pour contenter vos utilisateurs et leur faire adopter Power BI plus facilement, quoi de mieux que de leur servir le fichier Excel automatiquement par email sans effort de leur part… 😆

Maintenant, on va insérer 2 nouvelles étapes comme suivant :

Etape 1 : récupérer le contenu du fichier Excel contenant notre export Power BI

• Insérez une action OneDrive, Obtenir le contenu du fichier (notez l’indice ‘2’ car c’est la deuxième action de ce type dans le flux)
• Fichier : insérez le contenu dynamique ID de l’étape Créer un fichier

Etape 2 : générer l’email avec Outlook

• Insérez une action Outlook (ça marche aussi avec Gmail), Envoyer un email (V2)
• Renseignez le destinataire, l’objet, le corps de texte de l’e-mail
• Cliquez sur “Options avancées” pour faire apparaître les options de Pièces jointes
• Pièces jointes Nom – 1 : insérez le contenu dynamique Nom de l’étape Créer un fichier
• Pièces jointes Contenu – 1 : insérez le contenu dynamique Contenu du fichier de l’étape Obtenir le contenu du fichier 2

⚠️ Notez l’indice 2 : il ne faut pas prendre le contenu du premier “Obtenir le contenu du fichier” qui correspond au fichier Excel template, qui est vide… mais vous vous ferez avoir comme moi la première fois… ah ah 😉

Enregistrez le flux, testez-le, et…

…TA-DAM ! 

Le destinataire a reçu un nouvel e-mail 👇

Avec en pièce jointe le fichier Excel contenant les données du rapport Power BI 👇

Modifier le déclencheur du flux

Une fois que le flux tourne parfaitement, vous pouvez changer le déclencheur du flux (première étape) pour avoir un déclenchement planifié tous les jours, par exemple :

Limitations

Voici néanmoins quelques limites à cette méthode d’exportation des données qui peuvent limiter selon moi son usage :

• Maximum de 100 000 lignes ou 1 000 000 de valeurs par requête (selon ce qui est atteint en premier). Par exemple, si vous effectuez une requête sur 5 colonnes, vous pouvez obtenir un maximum de 100 000 lignes. Si vous effectuez une requête sur 20 colonnes, vous pouvez obtenir un maximum de 50 000 lignes (1 million divisé par 20).
• Maximum de 120 requêtes par utilisateur par minute. Le jeu de données cible n’a pas d’impact sur cette limite de taux.

Voir la liste complète des limitations du connecteur Power Automate

Conclusion

Et c’est tout pour ce tutoriel à propos de Power Automate qui permet d’exporter automatiquement des données de Power BI.

J’espère que vous trouverez des exemples utiles de votre côté pour contribuer à faire passer votre BI en libre-service au niveau supérieur !

Dites-moi en commentaires quels seraient vos usages de ce type de flux👍

 

Enfin, si vous souhaitez apprendre une méthode rapide et efficace pour collecter et analyser vos données dans Excel à l’aide de PowerQuery ou Power BI, et créer des rapports dynamiques, je vous propose un programme 100% en ligne spécialement conçu pour découvrir et maîtriser les Power Tools de Microsoft facilement 👏👏 :