Sommaire

Excel et Power BI sont deux outils complémentaires qui bénéficient chacun de leurs propres forces. Si vous hésitez entre utiliser Power BI ou Excel, notamment pour la restitution de vos données, consultez notre article dédié à la présentation des avantages de chaque outil.

Dans cet article, nous présentons les synergies entre les deux outils, notamment en ce qui concerne le transfert de données.

D’une part, via Power Query, Microsoft permet d’importer des données Excel dans Power BI, aussi bien depuis une source en local que depuis SharePoint. De l’autre, via Power Pivot, Microsoft permet d’utiliser dans Excel des données issues de Power BI. L’utilisation de l’outil DAX Studio en complément apporte beaucoup de flexibilité. Il permet notamment de filtrer des tables ou d’ajouter des colonnes calculées à l’import.

Comment importer des données d’Excel dans Power BI ?

Sources de données en local

Pour importer des données provenant d’un fichier Excel dans Power BI Desktop, cliquez sur « Obtenir des données » (« Get Data » en anglais) puis sur le connecteur Excel.

Une interface s’ouvre ensuite et vous propose de choisir les éléments du fichier Excel que vous souhaitez importer (tableau ou onglet). Deux possibilités s’offrent à vous :

  • Importer directement la donnée dans Power BI Desktop
  • Transformer la donnée via l’éditeur Power Query, un puissant outil de retraitement de données qui permet, par exemple, de supprimer des colonnes non pertinentes ou des données vides

Power Query peut aussi bien lire des tableaux (icône avec les en-têtes bleus) que des onglets (icône sans en-tête). L’utilisation des tableaux est recommandée afin de limiter le nombre de retraitements

Pour être importés, les fichiers Excel doivent posséder les caractéristiques suivantes :

  • Version Excel 2007 ou ultérieure
  • Format .xlsx ou .xlsm (l’import de fichiers .xlsb est possible mais très lente)
  • Taille maximale d’1 Go
  • Le fichier ne doit pas être ouvert en parallèle sur votre poste

Sources de données SharePoint

Il est également possible de se connecter à un fichier Excel présent sur SharePoint, via le connecteur « SharePoint folder ». Une fois le connecteur choisi, une interface similaire à la précédente s’affiche.

Les performances du connecteur SharePoint sont faibles par rapport au connecteur Excel en local. Le temps d’actualisation des données est particulièrement long. Nous vous conseillons donc de travailler sur Power Query avec une source stockée localement puis de vous connecter aux fichiers sur SharePoint lors de la publication.

Le niveau de confidentialité des données doit être configuré sur « Organizational » aussi bien dans Power Query que sur Power BI Service (réglage accessible dans Fichier/Paramètres de la source de données). Sans cela, le rapport ne peut pas être actualisé via l’interface de Power BI Service (qui permet notamment de planifier des actualisations régulières).

Si vous souhaitez des conseils pour créer votre rapport PBI, consultez notre article dédié à ce sujet.

Comment importer des données de Power BI dans Excel ?

Avantages de l’import de données de Power BI dans Excel

L’analyse de données de Power BI dans Excel est particulièrement intéressante à plusieurs titres :

  • Intégration des données : Power BI permet de consolider de nombreuses sources de données grâce à Power Query. Se connecter à un unique jeu de données Power BI offre donc la possibilité d’accéder à des données agrégées multi-sources.
  • Actualisation automatique : les données du rapport Power BI peuvent être régulièrement actualisées. L’utilisateur est assuré de travailler sur des données à jour.
  • Collaboration : seuls les utilisateurs ayant un rôle minimum de « Contributeur » peuvent librement se connecter aux données du rapport. Grâce à la RLS (Row-Level Security), des restrictions d’accès à certaines données peuvent être mises en place.
  • Modèle de données : en se connectant directement au jeu de données, l’utilisateur profite des avantages de la création d’un modèle de données (présence de référentiels, agrégation de données, puissance de calcul).
  • Indicateurs précalculés : toutes les mesures créées dans le rapport Power BI sont accessibles. L’utilisateur final n’a pas besoin de recréer les indicateurs : il peut se concentrer sur l’analyse des données.

Création de tableaux dynamiques

La mise en place de l’import des données de Power BI vers Excel peut être effectuée soit via Power BI Service, soit via Excel.

Obtenir des données de Power BI via Power BI Service

Une fois votre rapport ouvert sur Power BI Service, allez dans « Exporter » > « Analyser dans Excel ». Un fichier Excel avec la base de données est alors créé.

Si vous souhaitez uniquement exporter la donnée sous-jacente d’un visuel, vous pouvez utiliser l’option d’exporter les données d’un visuel (ex. matrice) via les « … » en haut à droite du visuel. Cette fonctionnalité est uniquement disponible lorsque l’option a été sélectionnée pour ce visuel via le panneau de visualisations lors du développement du rapport.

Obtenir des données de Power BI depuis Excel

Le paramétrage se fait à partir du ruban en allant dans « Données » > « Obtenir des données » > « À partir de la plateforme Power » > « A partir de Power BI ».

Une interface s’ouvre alors à droite de l’écran et présente l’ensemble des jeux de données auxquels vous avez accès. En cliquant ensuite sur « Insérer un tableau croisé dynamique », la fenêtre dédiée s’ouvre.

Import de tables plates avec DAX Studio

Il est également possible de s’affranchir des tableaux croisés dynamiques (TCD) et d’obtenir des tables simples plus facilement manipulables :

  • Une fois la création des TCD réalisée, glissez une mesure de la table que vous souhaitez dans le TCD
  • Réalisez ensuite un clic droit sur les valeurs puis cliquer sur « Afficher les détails ». Un nouvel onglet s’ouvre et affiche la table avec les 1000 premières lignes
  • Ouvrez les paramètres de la requête en allant dans « Table » > « Modifier la requête »

Une fenêtre vous permet de modifier le texte de la commande avec du DAX (langage utilisé dans Power BI). Si vous disposez d’un niveau avancé en DAX, vous pouvez ajuster directement le texte de la commande. Sinon vous pouvez utiliser le Query Builder de DAX Studio.

DAX Studio est un logiciel gratuit – et approuvé par Microsoft – qui permet de faciliter certaines actions sur Power BI (prévisualisation de tables, analyse de la performance, formatage de la syntaxe). Il est nécessaire d’avoir le rapport ouvert sur Power BI Desktop pour pouvoir s’y connecter avec DAX Studio.

Une fois le rapport chargé, cliquez sur « Query Builder » (1). Avec le même fonctionnement que les TCD, glissez les champs qui vous intéressent dans l’interface qui s’ouvre (2).

  • Appuyez sur « Update » pour générer la commande (3)
  • Cliquez sur « Run » (4), puis sur Results (5) pour prévisualiser le tableau obtenu

Cette méthode est ensuite facilement réplicable. Il vous suffit pour cela de copier le tableau puis de modifier à nouveau la commande.

A noter, les paramètres de commandes des tableaux sont modifiables en VBA.

Conclusion

L’utilisation des données de Power BI dans Excel n’est pas encore simplifiée. Cependant, utiliser les deux outils ensemble ouvre un vaste champ de possibilité pour les utilisateurs de Power BI. Microsoft a pour volonté d’améliorer la complémentarité entre les deux outils. Les utilisateurs devraient donc profiter à court-terme de nouveaux développements qui faciliteront les passerelles entre les deux outils.

Pour aller plus loin :

  • Si vous souhaitez des conseils pour analyser vos données sur Excel, consultez notre article listant les meilleures astuces.