Paramétrage des chemins de données dans Power Query

Si vous avez déjà commencé à utiliser les outils du complément gratuit Power Query dans Microsoft Excel, vous rencontrerez très bientôt un problème hautement spécialisé, mais très fréquent et ennuyeux, associé à la rupture constante des liens vers les données source. L'essence du problème est que si dans votre requête vous faites référence à des fichiers ou des dossiers externes, Power Query code en dur le chemin absolu vers eux dans le texte de la requête. Tout fonctionne bien sur votre ordinateur, mais si vous décidez d'envoyer un fichier avec une demande à vos collègues, ils seront déçus, car. ils ont un chemin différent vers les données source sur leur ordinateur, et notre requête ne fonctionnera pas.

Que faire dans une telle situation ? Examinons ce cas plus en détail avec l'exemple suivant.

Formulation du problème

Supposons que nous ayons dans le dossier E:Les rapports de ventes se trouve le dossier Top 100 produits.xls, qui est un téléchargement à partir de notre base de données d'entreprise ou de notre système ERP (1C, SAP, etc.). Ce fichier contient des informations sur les articles de base les plus populaires et ressemble à ceci :

Il est probablement clair dès le départ qu'il est presque impossible de travailler avec Excel sous cette forme : des lignes vides à travers une avec des données, des cellules fusionnées, des colonnes supplémentaires, un en-tête à plusieurs niveaux, etc. interféreront.

Par conséquent, à côté de ce fichier dans le même dossier, nous créons un autre nouveau fichier Gestionnaire.xlsx, dans lequel nous allons créer une requête Power Query qui chargera des données laides à partir du fichier de téléchargement source Top 100 produits.xls, et mettez-les dans l'ordre :

Faire une requête vers un fichier externe

Ouvrir le fichier Gestionnaire.xlsx, sélectionnez dans l'onglet Données Command Obtenir des données - à partir d'un fichier - à partir d'un classeur Excel (Données - Obtenir des données - À partir d'un fichier - À partir d'Excel), puis spécifiez l'emplacement du fichier source et la feuille dont nous avons besoin. Les données sélectionnées seront chargées dans l'éditeur Power Query :

Ramenons-les à la normale :

1. Supprimer les lignes vides avec Accueil — Supprimer des lignes — Supprimer des lignes vides (Accueil - Supprimer les lignes - Supprimer les lignes vides).
2. Supprimez les 4 premières lignes inutiles jusqu'à Accueil — Supprimer les lignes — Supprimer les lignes du haut (Accueil - Supprimer les lignes - Supprimer les lignes supérieures).
3. Remontez la première ligne jusqu'à l'en-tête du tableau avec le bouton Utiliser la première ligne comme en-tête languette Accueil (Accueil - Utiliser la première ligne comme en-tête).
4. Séparez l'article à cinq chiffres du nom du produit dans la deuxième colonne à l'aide de la commande colonne fractionnée languette De La Carrosserie (Transformer - Fractionner la colonne).
5. Supprimez les colonnes inutiles et renommez les en-têtes des autres pour une meilleure visibilité.

En conséquence, nous devrions obtenir l'image suivante, beaucoup plus agréable :

Il reste à télécharger ce tableau anobli de nouveau à la feuille dans notre dossier Gestionnaire.xlsx l'équipe fermer et télécharger (Accueil — Fermer&Charger) languette Accueil:

Trouver le chemin d'accès à un fichier dans une requête

Voyons maintenant à quoi ressemble notre requête "sous le capot", dans le langage interne intégré à Power Query avec le nom concis "M". Pour cela, revenez à notre requête en double-cliquant dessus dans le volet de droite Demandes et connexions et sur l'onglet Avis '; '; ; Éditeur avancé (Affichage — Éditeur avancé):

Dans la fenêtre qui s'ouvre, la deuxième ligne révèle immédiatement un chemin codé en dur vers notre fichier de téléchargement d'origine. Si nous pouvons remplacer cette chaîne de texte par un paramètre, une variable ou un lien vers une cellule de feuille Excel où ce chemin est pré-écrit, nous pouvons facilement le modifier ultérieurement.

Ajouter une table intelligente avec un chemin de fichier

Fermons Power Query pour l'instant et revenons à notre fichier Gestionnaire.xlsx. Ajoutons une nouvelle feuille vide et faisons dessus un petit tableau "intelligent", dans la seule cellule duquel sera écrit le chemin complet vers notre fichier de données source :

Pour créer un tableau intelligent à partir d'une plage régulière, vous pouvez utiliser le raccourci clavier Ctrl+T ou bouton Formater sous forme de tableau languette Accueil (Accueil — Format sous forme de tableau). L'en-tête de colonne (cellule A1) peut être absolument n'importe quoi. Notez également que pour plus de clarté, j'ai donné un nom à la table Paramètres languette Constructeur (Conception).

Copier un chemin depuis Explorer ou même le saisir manuellement n'est bien sûr pas particulièrement difficile, mais il est préférable de minimiser le facteur humain et de déterminer le chemin, si possible, automatiquement. Cela peut être mis en œuvre à l'aide de la fonction de feuille de calcul Excel standard CELLULE (CELLULE), qui peut donner un tas d'informations utiles sur la cellule spécifiée comme argument - y compris le chemin d'accès au fichier actuel :

Si nous supposons que le fichier de données source se trouve toujours dans le même dossier que notre processeur, le chemin dont nous avons besoin peut être formé par la formule suivante :

=GAUCHE(CELLULE(“nom_fichier”);TROUVER(“[“;CELLULE(“nom_fichier”))-1)&”Top 100 produits.xls”

ou en version anglaise :

=GAUCHE(CELLULE(«nomfichier»);CHERCHE(«[«;CELLULE(«nomfichier»))-1)&»Топ-100 товаров.xls»

… où est la fonction LEVSIMV (LA GAUCHE) prend un morceau de texte du lien complet jusqu'au crochet d'ouverture (c'est-à-dire le chemin vers le dossier actuel), puis le nom et l'extension de notre fichier de données source y sont collés.

Paramétrer le chemin dans la requête

La dernière et la plus importante touche reste - pour écrire le chemin vers le fichier source dans la demande Top 100 produits.xls, se référant à la cellule A2 de notre tableau "intelligent" créé Paramètres.

Pour ce faire, revenons à la requête Power Query et ouvrons-la à nouveau Éditeur avancé languette Avis (Affichage — Éditeur avancé). Au lieu d'un chemin de chaîne de texte entre guillemets "E:Rapports de ventesTop 100 produits.xlsx" Introduisons la structure suivante :

Excel.CurrentWorkbook(){[Name="Paramètres"]}[Contenu]sept {}[Chemin d'accès aux données source]

Voyons en quoi il consiste :

• Excel.CurrentWorkbook() est une fonction du langage M permettant d'accéder au contenu du fichier en cours
• {[Name="Paramètres"]}[Contenu] – c'est un paramètre de raffinement à la fonction précédente, indiquant que l'on veut récupérer le contenu de la table « intelligente » Paramètres
• [Chemin d'accès aux données source] est le nom de la colonne de la table Paramètresauquel nous nous référons
  
• sept {} est le numéro de ligne dans le tableau Paramètresà partir de laquelle nous voulons prendre des données. Le plafond ne compte pas et la numérotation commence à zéro et non à un.

C'est tout, en fait.

Il reste à cliquer sur Finition et vérifier comment notre demande fonctionne. Désormais, lors de l'envoi de l'intégralité du dossier contenant les deux fichiers vers un autre PC, la requête restera opérationnelle et déterminera automatiquement le chemin d'accès aux données.

• Qu'est-ce que Power Query et pourquoi est-il nécessaire lorsque vous travaillez dans Microsoft Excel
• Comment importer un extrait de texte flottant dans Power Query
• Reconception d'un tableau croisé XNUMXD en tableau plat avec Power Query