février 25, 2020Lire l'article
Chaque mois, un nouvel export arrive : ventes_janvier.xlsx, ventes_fevrier.xlsx, ventes_mars.xlsx… Vous avez connecté le premier à Power BI, puis le deuxième, puis vous avez commencé à les assembler à la main. Au dixième export, vous vous demandez si c'est vraiment la bonne méthode pour importer des données Excel.
Ce n'est pas la bonne. Rangez tous vos exports dans un même dossier, et connectez Power BI au dossier plutôt qu'aux fichiers. Power Query les empile tout seul, et le mois prochain il vous suffira de déposer le nouvel export dans le dossier et de cliquer sur Actualiser.
Petite introduction à Power BI pour ceux qui arrivent d'Excel. Les deux outils de Microsoft se complètent : Excel reste imbattable pour saisir, calculer et bricoler un tableau ; Power BI sert à créer des rapports et un tableau de bord interactif sur un volume de données qu'une feuille de calcul ne tient plus. Ceux qui construisent déjà des tableaux croisés dynamiques dans Excel avec Power Pivot connaissent d'ailleurs le principe : on charge les données dans un modèle de données, puis on le visualise. Dans Power BI, ce modèle s'appelle le modèle sémantique Power BI, et les mesures s'écrivent en DAX, comme dans Power Pivot.
Rien ne vous oblige à choisir : l'utilisation de Power BI avec Excel est le cas d'école, et utiliser Excel avec Power BI est même la situation la plus courante. Les données naissent dans Excel, chez les opérationnels, et Power BI les met en forme pour la direction. Une fois les données dans Power BI, les rapports dans Power BI se rafraîchissent seuls, sans que personne ne rouvre un classeur. Encore faut-il que l'import tienne dans la durée : les données Excel changent tous les mois, et c'est tout le sujet de cet article.
« Excel vs Power BI », extrait du livre Microsoft Power BI en images, p. 21.
Dix exports, ce sont dix connexions, dix requêtes à nettoyer à l'identique, puis une onzième pour les assembler. Si vous corrigez une étape, il faut la corriger dix fois. Et surtout, rien n'est automatique : chaque nouveau mois demande de rouvrir l'éditeur de requêtes. Un rapport Power BI qui réclame une intervention technique tous les mois finit toujours par ne plus être à jour.
En vidéo : Combiner les fichiers Excel d'un dossier en 1 clic sur Power Query
Dans le volet de gauche, un groupe de requêtes est apparu : Exemple de fichier, Paramètre, Transformer l'exemple de fichier, et une fonction. Ne les supprimez pas : c'est la mécanique qui applique le même traitement à chaque export.
Une seule mérite votre attention : Transformer l'exemple de fichier. Tout ce que vous faites dans cette requête (supprimer les trois lignes d'en-tête, retirer la ligne de total, promouvoir les en-têtes) sera rejoué sur chacun des fichiers du dossier. Les transformations qui concernent l'ensemble, une fois les fichiers empilés, se font dans la requête finale. C'est là que vous finissez de transformer des données avant qu'elles n'arrivent directement dans Power BI.
Souvent, l'export ne contient aucune colonne de date : le mois n'est écrit que dans le nom de fichier. La colonne Source.Name vous le donne. Avec Ajouter une colonne > Colonne à partir d'exemples, tapez « janvier » en face de « ventes_janvier.xlsx » : la règle est déduite pour toutes les lignes. Le plus propre reste de nommer vos fichiers pour Power BI avec une date lisible, du type ventes_2026-01.xlsx, que vous convertirez ensuite en vraie date.
Le connecteur Dossier répond à une situation précise : chaque mois, un nouveau fichier arrive. Si, chez vous, c'est le même classeur qui s'enrichit chaque mois d'une nouvelle colonne, la solution est différente.
« Concepts à ne pas confondre : Dossier vs Fichier incrémentiel », extrait du même livre, p. 81.
Dans ce second cas, lisez plutôt notre article [LIEN INTERNE : J'ai une colonne par mois, impossible de créer des visuels]. Et si vos mois sont répartis dans les onglets d'un même classeur, la logique est encore différente : on se connecte au classeur entier, puis on combine ses feuilles.
Le fichier fantôme « ~$ ». Quand un collègue a un export ouvert dans Excel, Windows crée un fichier temporaire caché, du type ~ventesmars.xlsx.Leconnecteurtentedelelireetl′actualisationeˊchoue.Avantdecombiner,filtrezlacolonneNamepourexclurecequicommencepar« », et la colonne Extension pour ne garder que .xlsx.
Les sous-dossiers. Le connecteur lit aussi tous les sous-dossiers. Si vous y avez glissé un répertoire Archives avec d'anciennes versions, elles seront chargées, et vos chiffres doublés. Filtrez la colonne Folder Path.
Des noms de feuille différents. Si la feuille s'appelle « Janvier » dans un export et « Février » dans le suivant, le connecteur cherche partout la feuille du modèle et ne la trouve pas. Imposez un nom de feuille fixe, ou mieux, un tableau structuré portant toujours le même nom.
Des colonnes renommées. L'empilement se fait sur le nom des colonnes. Si « Montant » devient « Montant HT » en avril, vous aurez deux colonnes à moitié vides. Mettez-vous d'accord sur un format d'export stable.
La ligne de total de chaque export. Si chaque export se termine par un total, il sera empilé avec le reste et comptera dans vos sommes. Supprimez-le dans Transformer l'exemple de fichier.
Le chemin du dossier. Un chemin du type C:\Users\Augustin\… ne fonctionnera ni sur le poste d'un collègue, ni pour une actualisation planifiée dans le service Power BI. Si le rapport doit être partagé, placez le dossier sur SharePoint et utilisez le connecteur Dossier SharePoint : Power BI Service pourra alors actualiser seul, et un flux Power Automate peut même déposer les exports dans le dossier à votre place.
En vidéo : Combiner des fichiers SharePoint avec Power Query
Cela fonctionne-t-il avec des fichiers CSV ? Oui, exactement de la même manière, et même un peu plus simplement puisqu'il n'y a pas de feuille à choisir.
Combien de fichiers peut-on combiner ? Des centaines sans difficulté. Le temps d'actualisation augmente avec le volume, mais la méthode reste la même pour 10 exports ou pour 500.
Que se passe-t-il si je remplace un export par une version corrigée ? Power BI relit tout le dossier à chaque actualisation. La version corrigée remplace l'ancienne, à condition de ne pas laisser les deux dans le dossier.
Power BI et Excel : peut-on faire l'inverse et analyser les données de Power BI dans Excel ? Oui : depuis Power BI Service, le bouton Analyser dans Excel ouvre un classeur connecté au modèle sémantique dans Power BI. Vous retrouvez vos mesures DAX dans un tableau croisé dynamique, sans dupliquer les données dans Excel.
