Support formation Rapports Power BI
Support formation Rapports Power BI
2 | Transformer ses données (Power Query)

icon picker
Combiner les fichiers d’un dossier

Astuces

⁠
Filter Tables before Expanding in Power Query
⁠
⁠
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.
info

En résumé

Créer un fichier Excel “modèle”, en faisant une copie d’un des fichiers.
Créer un paramètre avec comme Valeur actuelle le chemin complet vers le fichier modèle.
Créer une requête Query vers ce fichier modèle, en utilisant le paramètre comme source de la requête. Appliquer toutes les transformations souhaitées (devront pouvoir s’appliquer à tous les fichiers du dossier).
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 qui contient le chemin complet (FolderPath + Name).
Ajouter une colonne > Appeler la fonction personnalisée, sélectionner la fonction et compléter l’argument par la colonne du chemin complet.
Dans la nouvelle colonne créée, déployer les champs.

Étape 1 | Se connecter au fichier modèle

Dans le dossier contenant les fichiers à combiner, copier / coller un des fichiers, et le renommer en Modele_NomDuFichier.xlsx.
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.
Depuis Power Query : onglet Accueil > Nouvelle source > Excel puis Se connecter.
Sélectionner le fichier modèle.
On sélectionne le Modele_DataVente.xlsx.
Dans la fenêtre Navigateur, cocher la feuille du classeur et cliquer sur OK.
On sélectionne dans notre exemple Feuil1.
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.

Étape 2 | Créer un paramètre

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êtes
Excel 2013 et < : Accueil > PowerQuery > Lancer l’éditeur
Excel 2016 et > : Accueil > Données > Obtenir les données > Lancer Power Query
Dans Power Query, Accueil > Gérer les paramètres
Clic sur le lien Nouveau
Dans 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.
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.
Clic sur OK.*

Étape 3 | Transformer une requête en fonction

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.
Query crée 2 dossiers pour organiser les requêtes et paramètres.

Étape 4 | Se connecter au dossier

Depuis Power Query : onglet Accueil > Nouvelle source > Dossier puis Se connecter
Dans 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_

É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.
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.
Nous pouvons maintenant appeler la fonction personnalisée.
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.

É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.
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ée
Rechercher 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.

Récupérer le mois

Le mois se trouve dans le nom du fichier, colonne Name, entre le « _ » et le « . » (le point).
Depuis Power Query : clic dans la colonne Name
Onglet Ajouter une commande > Extraire > Texte entre les délimiteurs
Délimiteur du début : _ [souligné touche 8]; Délimiteur de fin : . [point]
Renommer la nouvelle colonne Texte entre les délimiteurs en Mois.

Fabriquer une colonne date

Nous allons utiliser la fonction Date.From, équivalent de la fonction DATE.VAL d’Excel.
Onglet Ajouter une commande > Colonne personnalisée
Dans Nouveau nom de la colonne, saisissez DatePeriodeVente
Dans la zone Formule de colonne personnalisée, obtenez
de la façon suivante :
Saisir
Double-clic sur Mois dans la liste Colonnes disponibles
Saisir
Double-clic sur Année dans la liste Colonnes disponibles.
Saisir
4. Appliquer le type Date (Transformer > Type de données : Date)
5. Clic sur OK.

Étape 7 | Finalisation

Depuis Power Query : clic droit dans la colonne Année > Supprimer
Clic droit dans la colonne Mois > Supprimer
Sé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.
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.
Astuces
En résumé
Étape 1 | Se connecter au fichier modèle
Étape 2 | Créer un paramètre
Étape 3 | Transformer une requête en fonction
Étape 4 | Se connecter au dossier
Étape 5 | Appliquer la fonction
Étape 6 | Récupérer l’année et le mois
Récupérer l’année
Récupérer le mois
Fabriquer une colonne date
Étape 7 | Finalisation
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.