Power bi excel : comment connecter et exploiter ses données efficacement

Excel est souvent le premier outil dans lequel une entreprise centralise ses données. Ventes, suivi commercial, budgets, stocks, campagnes marketing : chacun possède son fichier, son onglet et parfois même sa propre définition du chiffre d’affaires. Au début, cela fonctionne. Puis les versions se multiplient, les copier-coller s’accumulent et le fameux fichier « final_v3_definitif_bis.xlsx » fait son apparition.

C’est précisément là que Power BI peut changer la donne. En se connectant à Excel, l’outil de Microsoft transforme des tableaux parfois difficiles à exploiter en indicateurs visuels, interactifs et actualisables. Encore faut-il structurer correctement ses données et comprendre la logique de cette connexion.

Voici comment connecter Power BI à Excel, préparer ses données et construire un reporting réellement efficace.

Pourquoi connecter Excel à Power BI ?

Excel reste excellent pour saisir, calculer et analyser rapidement des données. Power BI, lui, est conçu pour modéliser, croiser et visualiser des informations provenant de plusieurs sources. Les deux outils ne s’opposent donc pas : ils se complètent.

Imaginez Excel comme un carnet de notes bien organisé. Il permet de consigner les informations. Power BI agit plutôt comme une salle de contrôle : il rassemble les données, les met en relation et affiche les signaux importants au bon moment.

La connexion entre les deux outils permet notamment de :

• centraliser plusieurs fichiers Excel dans un même rapport ;
• automatiser la mise à jour des indicateurs ;
• croiser les ventes avec les clients, les produits ou les régions ;
• identifier rapidement les tendances et les écarts ;
• partager des tableaux de bord interactifs avec les équipes ;
• réduire les manipulations manuelles et les erreurs de reporting.

Un responsable commercial peut ainsi suivre son chiffre d’affaires par secteur, comparer les performances mensuelles et filtrer les résultats par commercial en quelques clics. Sans demander à quelqu’un de reconstruire le tableau chaque lundi matin.

Préparer correctement son fichier Excel

La qualité du tableau de bord dépend directement de la qualité des données importées. Power BI sait traiter des volumes importants, mais il ne peut pas deviner une structure incohérente. Un fichier Excel mal préparé donnera donc un rapport fragile, même avec les plus beaux graphiques du marché.

Avant l’importation, quelques règles simples sont à respecter :

• une ligne doit correspondre à un enregistrement ;
• une colonne doit correspondre à une information ;
• la première ligne doit contenir les noms des colonnes ;
• les cellules fusionnées doivent être évitées ;
• les lignes et colonnes vides doivent être supprimées ;
• les dates, nombres et textes doivent utiliser des formats cohérents ;
• les sous-totaux et totaux doivent être retirés de la table source.

Par exemple, une table de ventes pourra contenir les colonnes suivantes : Date, Numéro de commande, Client, Produit, Catégorie, Quantité, Prix unitaire, Montant, Commercial et Région.

Il est préférable de convertir la plage en véritable tableau Excel avec le raccourci Ctrl + T. Cette étape est importante : Power BI reconnaîtra plus facilement la structure et pourra intégrer les nouvelles lignes ajoutées lors des prochaines actualisations.

Donnez également un nom explicite à chaque tableau, comme Ventes, Clients ou Produits. Les noms « Tableau1 » et « Feuil2 » n’ont jamais aidé personne à maintenir un reporting dans la durée.

Connecter Power BI à un fichier Excel

La connexion à un fichier Excel s’effectue depuis Power BI Desktop. Voici le processus général :

• ouvrez Power BI Desktop ;
• cliquez sur Obtenir les données ;
• sélectionnez Excel ;
• choisissez le fichier à importer ;
• sélectionnez les feuilles ou tableaux souhaités ;
• cliquez sur Charger ou Transformer les données.

L’option Charger importe directement les données dans le modèle Power BI. Elle convient lorsque le fichier est déjà propre et correctement structuré.

L’option Transformer les données ouvre Power Query, l’espace dédié au nettoyage et à la préparation des données. Dans la plupart des projets professionnels, cette seconde option est préférable. Elle permet de vérifier les types de données, de supprimer les colonnes inutiles, de corriger les valeurs et de standardiser les informations avant leur utilisation.

Nettoyer et transformer les données avec Power Query

Power Query constitue l’un des principaux atouts de Power BI. Il permet d’automatiser des opérations qui seraient fastidieuses dans Excel : supprimer des lignes vides, remplacer des valeurs, scinder une colonne ou encore combiner plusieurs fichiers.

Supposons que les dates de commande soient parfois écrites sous la forme « 15/01/2024 » et parfois « Janvier 2024 ». Power BI aura des difficultés à analyser correctement cette colonne. Dans Power Query, vous pourrez convertir la colonne au format date, identifier les valeurs incorrectes et corriger la source.

Les transformations courantes comprennent notamment :

• la modification du type de données ;
• la suppression des doublons ;
• le filtrage des lignes inutiles ;
• le remplacement des valeurs nulles ou incohérentes ;
• la séparation d’une colonne en plusieurs champs ;
• la fusion de colonnes ;
• l’ajout de colonnes personnalisées ;
• la combinaison de fichiers ayant la même structure.

L’intérêt est que chaque transformation est mémorisée. Lorsqu’une nouvelle version du fichier est chargée, Power Query rejoue automatiquement les étapes définies. Vous ne répétez donc pas la même série de manipulations à chaque reporting.

Importer plusieurs fichiers Excel

Dans de nombreuses entreprises, les données sont réparties dans plusieurs fichiers : un fichier par mois, par agence ou par commercial. Les consolider manuellement est une activité chronophage et particulièrement propice aux oublis.

Power BI peut connecter un dossier contenant plusieurs fichiers Excel et les combiner automatiquement, à condition qu’ils respectent une structure similaire. Pour cela, utilisez Obtenir les données, puis choisissez le connecteur Dossier.

Power BI va afficher la liste des fichiers disponibles. Vous pourrez ensuite :

• filtrer les fichiers à conserver selon leur nom ou leur extension ;
• sélectionner un fichier exemple ;
• définir les transformations à appliquer ;
• combiner les données dans une table unique.

Cette méthode est particulièrement utile pour un suivi mensuel des ventes. Il suffit de déposer le nouveau fichier dans le dossier prévu à cet effet pour qu’il soit intégré lors de la prochaine actualisation.

Attention toutefois aux fichiers dont les colonnes changent de nom ou d’ordre chaque mois. La magie a ses limites : Power BI apprécie la régularité.

Relier plusieurs tables dans Power BI

Importer une seule table de ventes peut suffire pour un besoin simple. Mais dès que l’analyse devient plus avancée, il est préférable de séparer les informations en plusieurs tables.

Une structure classique peut comprendre :

• une table de faits contenant les transactions ;
• une table Clients contenant les informations clients ;
• une table Produits décrivant le catalogue ;
• une table Calendrier pour les analyses temporelles ;
• une table Commerciaux ou Régions pour les axes d’analyse.

Ces tables doivent être reliées grâce à des champs communs. Par exemple, la table Ventes peut contenir un identifiant client qui correspond à celui présent dans la table Clients.

Dans la vue Modèle de Power BI, vous pouvez créer ou vérifier ces relations. Le modèle en étoile est généralement recommandé : une table centrale de transactions est reliée à plusieurs tables de référence. Cette organisation facilite les calculs et améliore la lisibilité du modèle.

Il faut éviter de relier les tables uniquement sur des libellés susceptibles de varier, comme le nom d’un produit ou d’une ville. Un identifiant unique est plus fiable. « Société ABC », « Société ABC » avec un espace final et « société abc » ne devraient pas devenir trois clients différents simplement à cause d’une saisie approximative.

Créer des indicateurs avec DAX

Une fois les données connectées et les relations établies, Power BI permet de créer des mesures avec le langage DAX. Ces mesures servent à calculer des indicateurs dynamiques qui réagissent aux filtres du rapport.

Pour calculer le chiffre d’affaires, une mesure simple peut être utilisée :

Chiffre d’affaires = SUM(Ventes[Montant])

Pour analyser une marge, vous pourrez créer une mesure adaptée à la structure de vos données :

Marge = SUM(Ventes[Montant]) – SUM(Ventes[Coût])

L’intérêt d’une mesure DAX est qu’elle se recalcule selon le contexte. Le chiffre d’affaires affiché pourra ainsi correspondre à une région, une période, une catégorie de produits ou un commercial sélectionné par l’utilisateur.

Parmi les indicateurs fréquemment suivis, on retrouve :

• le chiffre d’affaires total ;
• le nombre de commandes ;
• le panier moyen ;
• la marge et le taux de marge ;
• la croissance par rapport à la période précédente ;
• le taux de conversion ;
• le chiffre d’affaires cumulé depuis le début de l’année.

Il est préférable de créer des mesures plutôt que de multiplier les colonnes calculées sans nécessité. Le modèle sera plus clair et les performances souvent meilleures.

Construire un tableau de bord utile

Power BI propose de nombreux visuels : graphiques en barres, courbes, cartes, matrices, indicateurs, segments et cartes géographiques. Mais la question n’est pas de savoir combien de graphiques vous pouvez placer sur une page. Elle consiste à déterminer quelles décisions le tableau de bord doit aider à prendre.

Un tableau de bord commercial efficace pourra présenter :

• le chiffre d’affaires réalisé et l’objectif ;
• l’évolution mensuelle ;
• la performance par commercial ;
• les produits les plus vendus ;
• les clients à fort potentiel ;
• les régions en retard ou en avance.

Utilisez des filtres simples et visibles : période, région, commercial ou catégorie. Limitez également le nombre de couleurs. Une couleur doit avoir une fonction : mettre en évidence une alerte, distinguer une catégorie ou signaler un écart.

Un bon dashboard se comprend rapidement. Si l’utilisateur doit suivre une formation de deux jours pour savoir où regarder, ce n’est probablement plus un tableau de bord, mais un escape game.

Actualiser les données Excel dans Power BI

Après la création du rapport, les données doivent pouvoir être actualisées. Depuis Power BI Desktop, le bouton Actualiser relance les connexions et applique à nouveau les transformations Power Query.

Pour partager le rapport en ligne, vous pouvez le publier dans le service Power BI. Selon l’emplacement du fichier Excel, plusieurs configurations sont possibles :

• un fichier stocké localement peut nécessiter une passerelle de données ;
• un fichier placé sur OneDrive ou SharePoint facilite généralement la synchronisation ;
• les autorisations d’accès doivent être correctement configurées ;
• la fréquence d’actualisation dépend de la licence et de l’environnement utilisé.

Avant de diffuser le rapport, vérifiez toujours la date de dernière actualisation, les éventuels messages d’erreur et le nombre de lignes importées. Un indicateur très élégant mais basé sur des données datant de trois mois reste un indicateur trompeur.

Les erreurs fréquentes à éviter

La première erreur consiste à utiliser Excel comme une base de données sans en respecter les principes. Les tableaux contenant des titres au milieu des lignes, des cellules fusionnées ou plusieurs informations dans une même cellule poseront rapidement problème.

La deuxième consiste à mélanger nettoyage, calculs et présentation dans le même fichier. Il est préférable de conserver une source relativement brute, d’effectuer les transformations dans Power Query et de construire les indicateurs dans le modèle Power BI.

Enfin, ne négligez pas la gouvernance. Définissez qui est responsable du fichier source, qui peut le modifier et quelle définition est retenue pour chaque KPI. Le terme « client actif », par exemple, peut désigner un client ayant acheté ce mois-ci, cette année ou au moins une fois depuis sa création. Sans définition commune, les réunions risquent de devenir un débat sémantique avec graphiques en arrière-plan.

Une méthode simple pour démarrer

Pour un premier projet, commencez avec un périmètre limité. Sélectionnez une source Excel fiable, trois à cinq indicateurs prioritaires et un public clairement identifié.

La démarche peut suivre ce fil conducteur :

• clarifier les questions auxquelles le rapport doit répondre ;
• nettoyer et structurer le fichier Excel ;
• importer les données dans Power BI ;
• contrôler les types et la qualité des champs ;
• créer les relations entre les tables ;
• construire les mesures DAX indispensables ;
• designer une page de synthèse lisible ;
• tester les résultats avec les utilisateurs métier ;
• organiser l’actualisation et les droits d’accès.

La connexion entre Power BI et Excel ne se résume donc pas à cliquer sur « Importer ». Elle repose sur une chaîne complète : une donnée bien structurée, des transformations maîtrisées, un modèle cohérent et des indicateurs alignés avec les besoins de l’entreprise.

Bien utilisée, cette combinaison permet de conserver la souplesse d’Excel tout en bénéficiant de la puissance analytique de Power BI. De quoi passer d’un reporting construit dans l’urgence à un véritable outil d’aide à la décision.