Skip to content
Créer un fichier Excel “modèle”, en faisant une copie d’un des fichiers.Créer une requête Query vers ce fichier modèle, en utilisant le paramètre comme source de la requête. Créer toutes les transformations souhaitées (devront pouvoir s’appliquer à tous les fichiers du dossier).Créer un paramètre paramCheminCompletFichierExcel avec comme Valeur actuelle le chemin complet vers le fichier modèle. Remplacer le chemin vers le modèle (étape Source) par le paramètre.Créer une fonction à partir de cette requête.Créer une requête de type Dossier qui pointe vers le dossier contenant les fichiers à fusionner.Filtrer les fichiers du dossier pour ne conserver que les fichiers qui devront être fusionnés ensuite.Créer une colonne CheminComplet qui contient FolderPath & Name. Ajouter une colonne > Appeler la fonction personnalisée, sélectionner la fonction et compléter l’argument par la colonne CheminComplet.Dans la nouvelle colonne créée, déployer les champs.Récupérer des informations depuis la colonne Name (l’année par exemple)
Dans le dossier contenant les fichiers à combiner, copier / coller un des fichiers, et le renommer en Modele_NomDuFichier.xlsx.Depuis Power Query : onglet Accueil > Nouvelle source > Excel puis Se connecter.Sélectionner le fichier modèle.Dans la fenêtre Navigateur, cocher la feuille du classeur et cliquer sur OK.Dans la liste des étapes de la requêtes (volet à droite), double-cliquer sur l’étape Source.Dans Chemin du fichier, clic sur le bouton "abc" et sélectionner Paramètre. Sélectionner le paramètre paramCheminCompletFichierExcel.Clic sur OK.Renommer la requête : dans le volet Requêtes à gauche, sélectionner la requête puis F2. Saisir le nom reqModele.Apporter toutes les transformations souhaitées sur ce fichier. Ces mêmes modifications seront appliquées à tous les fichiers du dossier.
Lancer Power BI (ou Excel)Enregistrer le rapport vide qui s’affiche à l’ouverture (par exemple SuiviData.pbix ou SuiviData.xlsx).Power BI : Onglet Accueil > Modifier les requêtesDans Power Query, Accueil > Gérer les paramètresClic sur le lien NouveauDans la zone Nom, saisir paramCheminCompletFichierExcel (ou tout autre nom pour cette variable)Dans la liste Type, sélectionner Texte.Dans la zone Valeur actuelle, copier/coller le chemin complet vers l’un des fichiers du dossier, par exemple C:\Mes données\DataVente\2019\ DataVente_Janvier.xlsx.Clic sur OK.*
Depuis Power Query : dans le volet Requêtes, sélectionner la requête reqModele puis clic droit > Créer une fonction.Dans la fenêtre Créer une fonction qui s’affiche, saisir fcModele dans Nom de la fonction et clic sur OK.
Depuis Power Query : onglet Accueil > Nouvelle source > Dossier puis Se connecterDans Chemin du fichier, cliquer sur Parcourir. Sélectionner le fichier puis OK puis OK.Dans la fenêtre suivante qui s’affiche, cliquer sur le bouton Transformer les données / Modifier (SURTOUT PAS Combiner et modifier).Dans le volet Requêtes, renommer la requête en Data. Ce sera notre requête finale, qui contiendra les données de tous les fichiers.{option] Vous pouvez filtrer les fichiers du dossier pour ne conserver que ceux à intégrer. Par exemple, vous pouvez filtrer par la colonne Name sur Commence par et saisir DataVente_
Depuis Power Query : sélectionner la requête Data.Sélectionner la colonne Folder Path, puis appuyer sur CTRL et sélectionner la colonne Name (dans cette ordre)Onglet Ajouter une colonne > Fusionner les colonnes.Ne pas indiquer de séparateur. Indiquer le nom CheminComplet puis cliquer sur OK.Onglet Ajouter une colonne > Appeler une fonction personnalisée.Dans la liste Requête de fonction, sélectionner la fonction fcModele. La zone Nouveau nom de colonne se remplit avec le nom de la fonction.Dans la liste paramCheminCompletFichierExcel, sélectionner CheminComplet.Clic sur OK.Une nouvelle colonne fcModele apparaît avec le mot Table dans toutes les cellules. Si le mot Error apparait, c’est que la reqModele ne fonctionne pas sur l’un des fichiers.Clic sur le bouton en forme de "flèche gauche & droite", en haut à droite du nom de la colonne fnFichierVente.Dans la liste qui s’affiche, décocher en bas Utiliser le nom de la colonne d’origine comme préfixe.Décocher les cases des colonnes que l’on ne souhaite pas.Clic sur OK.
Depuis Power Query : clic dans la colonne Folder Path.Onglet Ajouter une commande > Extraire > Texte entre les délimiteurs.Délimiteur du début : \ ; Délimiteur de fin : \ Dans Options avancées :Recherche le délimiteur du début : À partir de la fin de l’entréeRechercher le délimiteur de fin : à partir du délimiteur du début vers le début de l’entrée.Clic sur OK.Renommer la nouvelle colonne Texte entre les délimiteurs en Année.
Depuis Power Query : clic dans la colonne NameOnglet Ajouter une commande > Extraire > Texte entre les délimiteursDélimiteur du début : _ [souligné touche 8]; Délimiteur de fin : . [point]Renommer la nouvelle colonne Texte entre les délimiteurs en Mois.
Onglet Ajouter une commande > Colonne personnaliséeDans Nouveau nom de la colonne, saisissez DatePeriodeVenteDans la zone Formule de colonne personnalisée, obtenez Saisir Double-clic sur Mois dans la liste Colonnes disponiblesSaisirDouble-clic sur Année dans la liste Colonnes disponibles.Saisir
Depuis Power Query : clic droit dans la colonne Année > SupprimerClic droit dans la colonne Mois > SupprimerSélectionner les colonnes à conserver, en partant de la fin : DatePeriodeVente et les colonnes des fichiers sources que l’on souhaite conserver.Clic droit sur la sélection > Supprimer les autres colonnes.
Support formation Rapports Power BI
- Pages
Combiner automatiquement les fichiers d’un dossier
Astuces
Nous souhaitons agréger tous les fichiers Excel contenu dans un dossier.
Dans notre exemple, les fichiers sont nommés DataVente_Janvier.xlsx, DataVente_Février.xlsx, etc. et le dossier se nomme C:\Mes données\DataVente\2019. Chaque classeur contient un onglet nommé à l’identique dans tous autres fichiers, par exemple Feuil1.
En résumé
Étape 1 | Se connecter au fichier modèle
Dans notre exemple, le fichier DataVente_Janvier.xlsx est copié / collé puis renommé Modele_DataVente.xlsx.
Ce fichier peut être déplacé dans un dossier “modèles” que vous pouvez créer.
On sélectionne le Modele_DataVente.xlsx.
On sélectionne dans notre exemple Feuil1.
Étape 2 | Créer un paramètre
Excel 2013 et < : Accueil > PowerQuery > Lancer l’éditeur
Excel 2016 et > : Accueil > Données > Obtenir les données > Lancer Power Query
Astuce : pour récupérer le chemin complet d’un fichier, dans l’Explorateur de fichiers, Shift + clic droit sur un fichier > Copier en tant que chemin d’accès. Supprimer les guillemets après avoir collé le chemin.
Étape 3 | Transformer une requête en fonction
Query crée 2 dossiers pour organiser les requêtes et paramètres.
Étape 4 | Se connecter au dossier
Étape 5 | Appliquer la fonction
Nous devons créer une colonne qui contient le chemin complet vers chaque fichier Excel. La requête Data affiche dans une colonne le chemin (Folder Path) et dans une autre colonne le nom (Name). Il nous faut fusionner ces 2 colonnes dans une 3e colonne.
Nous pouvons maintenant appeler la fonction personnalisée.
Étape 6 | Récupérer l’année et le mois
Le nom des fichiers contient le nom du mois sous la forme de texte et le nom du dossier contient l’année.
Récupérer l’année
L’année est présente à la fin du chemin des dossiers, dans la colonne Folder Path. Nous allons récupérer les 4 derniers caractères.
La requête Vente est sélectionnée.
Récupérer le mois
Le mois se trouve dans le nom du fichier, colonne Name, entre le « _ » et le « . » (le point).
Fabriquer une colonne date
Nous allons utiliser la fonction Date.From, équivalent de la fonction DATE.VAL d’Excel.
de la façon suivante :
4. Appliquer le type Date (Transformer > Type de données : Date)
5. Clic sur OK.
Étape 7 | Finalisation
Quand vous placez un nouveau fichier dans l’un des dossiers ou sous-dossier, son contenu sera automatiquement « aspiré » par la requête, et les statistiques de vente seront mis à jour.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.