Un fichier de ventes par mois, une feuille par agence : ajouter les requêtes une à une oblige à modifier le rapport à chaque nouveau fichier. En combinant le contenu d'un dossier, ou toutes les feuilles d'un classeur, vous écrivez la transformation une seule fois : chaque nouveau fichier déposé dans le dossier est intégré à la prochaine actualisation.

Deux méthodes produisent le même résultat :

Dans les deux cas, la transformation mise au point sur un fichier exemple est transformée en fonction, puis appliquée à chaque fichier de la liste. Les fichiers doivent donc avoir la même structure : mêmes feuilles ou tableaux, mêmes colonnes.

Après Combiner les fichiers, le volet Requêtes contient un groupe d'assistance avec :

[Capture : volet Requêtes après Combiner les fichiers, groupe d'assistance déplié avec l'exemple de fichier, le paramètre, la fonction et Transformer l'exemple de fichier]

Les fichiers de l'exemple s'appellent DataVente_Janvier.xlsx, DataVente_Février.xlsx, etc. Ils sont rangés dans un dossier 2019 et contiennent tous une feuille Feuil1.

1. Dans Power BI Desktop, Accueil > Obtenir les données > Plus > Dossier, puis Se connecter.

2. Cliquez sur Parcourir, sélectionnez le dossier, puis OK. La liste des fichiers s'affiche (colonnes Content, Name, Extension, Folder Path...).

3. Cliquez sur Transformer les données plutôt que sur Combiner : vous pourrez filtrer les fichiers avant de les combiner.

4. Filtrez les fichiers à intégrer, par exemple sur la colonne Name avec Filtres textuels > Commence par « DataVente_ », et sur Extension égale à « .xlsx ».

5. Cliquez sur le bouton Combiner les fichiers dans l'en-tête de la colonne Content.

[Capture : requête Dossier filtrée, bouton Combiner les fichiers dans l'en-tête de la colonne Content mis en évidence]

6. Dans la boîte Combiner les fichiers, choisissez l'exemple de fichier, sélectionnez Feuil1, puis OK.

[Capture : boîte Combiner les fichiers, Feuil1 sélectionnée dans le volet de gauche, aperçu à droite]

7. La requête finale contient une colonne Source.Name avec le nom de chaque fichier, puis les colonnes de Feuil1. Pour modifier le traitement (supprimer des lignes d'en-tête, renommer des colonnes), modifiez Transformer l'exemple de fichier, et non la requête finale.

L'exemple utilise le fichier sample02-combine-fichiers\data_sheets\Ventes_2020.xlsx du kit pédagogique, qui contient une feuille par mois.

1. Connectez-vous au classeur, cochez une feuille (Janvier), puis Transformer les données. Renommez la requête reqExemple.

2. Créez un paramètre (Accueil > Gérer les paramètres > Nouveau paramètre) nommé paramNomFeuille, de type Texte, avec Janvier comme Valeur actuelle.

3. Sélectionnez l'étape Navigation de reqExemple et, dans la barre de formule, remplacez "Janvier" par paramNomFeuille (sans guillemets).

[Capture : barre de formule de l'étape Navigation, paramNomFeuille à la place de "Janvier"]

4. Appliquez à reqExemple toutes les transformations souhaitées, puis clic droit > Créer une fonction, nommée fcExemple.

5. Dupliquez reqExemple, renommez la copie Data et supprimez toutes ses étapes sauf Source : vous obtenez la liste des feuilles du classeur (colonnes Name, Data, Item, Kind, Hidden).

6. Filtrez la colonne Kind sur « Sheet » si le classeur contient aussi des tableaux, puis Ajouter une colonne > Appeler une fonction personnalisée, fonction fcExemple, paramètre paramNomFeuille = colonne Name.

7. Développez la nouvelle colonne sans préfixe, renommez Name en Mois, supprimez les colonnes Data, Item, Kind et Hidden, puis Transformer > Détecter le type de données sur toutes les colonnes.

8. Décochez Activer le chargement sur reqExemple, puis Accueil > Fermer et appliquer.

Pour vérifier, copiez une feuille du classeur, nommez-la Avril, modifiez quelques chiffres, enregistrez, puis Accueil > Actualiser dans Power BI Desktop : Avril apparaît.

Cette méthode reprend le principe précédent avec un paramètre qui contient le chemin complet d'un fichier.

1. Copiez l'un des fichiers et renommez la copie Modele_DataVente.xlsx, de préférence dans un dossier « Modèles » à part, pour qu'elle ne soit pas combinée avec les autres.

2. Créez un paramètre paramCheminFichier de type Texte, avec comme valeur actuelle le chemin complet du fichier modèle. Astuce : dans l'Explorateur de fichiers, Maj + clic droit sur le fichier > Copier en tant que chemin d'accès, puis supprimez les guillemets après le collage.

3. Connectez-vous au fichier modèle (Nouvelle source > Classeur Excel), cochez Feuil1, puis OK. Double-cliquez sur l'étape Source, cliquez sur le bouton de type à gauche de Chemin d'accès au fichier, choisissez Paramètre, puis paramCheminFichier.

4. Renommez la requête reqModele, appliquez les transformations souhaitées, puis clic droit > Créer une fonction, nommée fcModele.

5. Créez une requête Dossier vers le dossier des fichiers, cliquez sur Transformer les données et nommez-la Data. Filtrez les fichiers à intégrer.

6. Sélectionnez Folder Path puis Name (dans cet ordre), puis Ajouter une colonne > Fusionner les colonnes, sans séparateur, nouvelle colonne CheminComplet.

7. Ajouter une colonne > Appeler une fonction personnalisée, fonction fcModele, paramCheminFichier = CheminComplet. La colonne affiche Table sur chaque ligne ; Error signale un fichier sur lequel reqModele ne fonctionne pas.

8. Développez la colonne sans préfixe en ne cochant que les colonnes utiles.

9. Récupérez l'année, dernier dossier du chemin : sélectionnez Folder Path, puis Ajouter une colonne > Extraire > Texte entre les délimiteurs, début \, fin \, et dans Options avancées, recherche du délimiteur de début à partir de la fin de l'entrée et du délimiteur de fin à partir du délimiteur de début vers le début de l'entrée. Renommez la colonne Année.

10. Récupérez le mois : sur Name, Ajouter une colonne > Extraire > Texte entre les délimiteurs, début _, fin .. Renommez la colonne Mois.

11. Construisez une date : Ajouter une colonne > Colonne personnalisée, nommée DatePeriodeVente, avec la formule ci-dessous, puis typez-la en Date.

12. Supprimez Année, Mois et les colonnes techniques du dossier (sélectionnez les colonnes à garder, puis clic droit > Supprimer les autres colonnes). Décochez Activer le chargement sur reqModele.

Tout nouveau fichier déposé dans le dossier ou dans un sous-dossier sera intégré à la prochaine actualisation.

Exo 2.2 | Parc automobile (page Mise en pratique), deuxième partie : combinaison des fichiers d'un dossier.