Support Microsoft Power BI

Explore

Support Microsoft Power BI

2 | Transformer ses données (Power Query)

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.

Étape 1 | 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 2 | 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.

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, dans notre exemple Feuil1 et cliquer sur OK.

On sélectionne 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 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

Étape 1 | Créer un paramètre

Étape 2 | Se connecter au fichier modèle

É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

Gallery

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.