3 pièges à éviter pour vos rapports Power BI de source Excel

Le contexte favorable aux sources Excel en entreprise

Bon nombre de sources de données, comme SAP ou tout logiciel tournant sur une base Oracle ou MySQL ne sont pas ouvertes à tous les consommateurs de rapports. Elles requièrent un niveau de droit élevé, ce qui est bien normal pour des applications critiques.

Le fait que Power BI puisse utiliser des feuilles Excel est alors un bénéfice majeur de Power BI. En effet toutes ces applications permettent aux utilisateurs des exports faciles  au format Excel. Mais attention : ceci peut générer des anomalies de résultat si vous ne respectez pas un minimum de trois conseils.

PREMIER CONSEIL : éviter les liaisons dans vos sources Excel

La première chose est d’éviter toute source Excel dans Power BI déjà liée à une autre source Excel par une formule de calcul qui effectue une liaison inter classeur.

Ceci peut afficher une donnée vétuste – le classeur enregistré n’ayant pas ouvert le fichier lié, et cela peut même provoquer une erreur lors de l’actualisation du rapport.

En effet, Power BI lit la source de donnée, et ne va pas rafraichir les données liées par des formules.  Si une cellule Excel source de donnée est liée à un autre classeur, et que la source a changé, la source Power BI ne le sait pas : il va afficher une donnée vétuste,  le refresh de Power BI va actualiser les données depuis le fichier enregistré, Power BI ne va pas ouvrir Excel pour rafraichir le classeur lié.

 

Si pour éviter cela, vous avez ouvert le fichier source de Power BI, mais pas eu le temps d’ouvrir tous  les classeurs liés, Excel va afficher une erreur #REF! sur les données non trouvées. Ici par exemple, la colonne DonnéeLiées fait référence à un autres classeur.

Erreur #REF!

Une fois dans Power BI ceci va bloquer l’actualisation avec le message :

DEUXIEME CONSEIL : limitez-vous à des données brutes

Eviter les formules de calcul dans vos sources Excel et limitez-vous aux données brutes. Laissez Power BI faire les calculs à la place d’Excel. Ceci a deux avantages :

1.Vous êtes sûr que toutes les cellules ont la même formule : plus d’erreurs cachées dans vos données

2.Vous évitez les messages d’erreur : ce qui arrive par exemple par division par zéro, ou par division ou soustraction par du vide, ce qui met en erreur Excel. Les cellules vides sont des pièges pour les formules de calcul d’Excel, car Excel ne sait pas gérer seul ce problème, et affiche une erreur #DIV/0!. Il faut une formule conditionnelle Excel complexe  pour anticiper le cas ou la cellule source soit vide.

Power BI au contraire, sait gérer nativement les cellules vides et peut facilement opérer des traitements.

 

Dans Power BI une multiplication par une cellule vide par exemple ne conduit pas à une erreur comme dans Excel, mais à une cellule vide.

Ici le rapport Power BI de source Excel a subi une division par zéro dans la source Excel. Voici le message d’erreur  de Power BI : Valeur de cellule « #DIV/0! » non valide.

Le message est clair mais peut vous induire en erreur car ce n’est pas un problème de format de donnée, mais une formule en erreur dans votre source Excel, qui finit par générer DIV/0!  dans la cellule. Ceci  est effectivement du texte et non une valeur. Et Power BI ne vous permettra pas de régler cette anomalie : il faudra l’éviter à la source.

 

TROISIEME CONSEIL : Afficher dans un visuel de type Carte la date de mise à jour des données.

 

Ce cartouche Power BI permet d’afficher la date et l’heure de mise à jour de la source de donnée.

Pour PowerBI, ce qui fait la source Excel est le dernier fichier enregistré, pas celui avec les données qui sont en mémoire vive et visible  à l’écran. Tant que le tableau source Excel n’est enregistré : le rapport PowerBI  en reste aux données non mises à jour. Il est donc conseillé d’afficher ainsi la date d’actualisation dans le rapport Power BI.

Pour savoir à quelle date un rapport a été mis à jour, il y a plusieurs techniques, en fonction de la date ciblée.

 

Pour les fichiers Excel de données brutes, on va simplement prendre le répertoire Excel comme source de données de Power BI et on va récupérer la date de dernière modification et la stocker dans une table. Ici  la Date « Date modified » donnera la dernière mise à jour.

 

Pour les fichiers Excel et un usage par Power BI Desktop,  on peut  également créer une requête  DateTime.LocalNow() dans Power Query, qui va stocker la date de mise à jour à chaque refresh de Power BI Desktop. Pour un usage Power BI Service, on procédera différemment, comme on peut le voir à l’adresse <https://www.kasperonbi.com/show-the-refresh-datetime-in-a-power-bi-report-and-dashboard/> .

 Conclusion : notre recommandation

 

Pour éviter la multiplicité de sources de données non vérifiées en entreprise, nous recommandons d’accéder directement aux applications et bases de données via les nombreux connecteurs de Power BI. Cependant, lorsque que ce n’est pas possible de faire autrement, on adoptera les fichiers Excel comme source de fichier, à condition d’en faire une source de données brutes, c’est à dire sans formule ni calcul. On utilisera le bouton « Mettre sous  forme de tableau » d’Excel pour formater et nommer correctement les données.

ruban Excel pour formater le tableau source à Power BI

NB : Il est plus efficace de stocker les tableaux source Excel dans SharePoint ou OneDrive , car outre le fait que la date de mise à jour y est disponible à tout moment, ces données seront sauvegardées en permanence.