Avec Excel, vous manipulez des cellules. Avec Power Query, des lignes et des colonnes. Alors, comment faire pour récupérer la valeur d’une seule cellule avec Power Query ?

Très souvent les fichiers sources contiennent des informations importantes qui ne sont pas disponibles dans une table :

l’en-tête du fichier qui contient la date de l’extraction
une cellule avec le nom de l’entrepôt ou du magasin
l’année budgétaire …

Vous avez alors besoin de récupérer ces informations pour vos transformations ou vos calculs.

Consolider plusieurs fichiers en conservant la date d’extraction ou le nom de l’entrepôt
Transformer le mois en date en ajoutant l’année
Convertir les montants en euros en utilisant le taux de change
Comparer le délai constaté au délai fournisseur moyen

Je vous explique en quelques étapes comment le faire sans VBA et sans écrire une seule ligne de code (ou presque).

Dans Power Query, vous pouvez identifier n’importe quelle cellule en utilisant une formule très simple. Il vous suffit de 3 informations :

le nom de l’étape de référence,
le numéro de la ligne
et l’en-tête de la colonne.

Et de respecter la syntaxe.

Dans cet exercice, notre objectif est d’ajouter une colonne avec la date de l’extraction des données à notre tableau de suivi des stocks.

Pour cela, nous avons besoin de récupérer la date qui se trouve dans la première cellule de notre tableau. Nous allons utiliser Power Query pour réaliser les transformations suivantes :

identifier la cellule contenant la date
ajouter une colonne personnalisée
récupérer la valeur de la cellule

Importer les données de votre fichier dans Power Query

La première étape consiste à charger les données de stocks du fichier Excel.

Créer un nouveau fichier Excel et lancer Power Query (Menu Données > Obtenir des données > Lancer l’éditeur Power Query).
Depuis le menu Accueil, sélectionnez Nouvelle requête > Nouvelle source > Fichier > Classeur Excel
Sélectionner le fichier et cliquer sur Importer
Dans la fenêtre Navigateur, sélectionner le premier onglet et cliquer sur OK

Identifier la cellule dont vous souhaitez récupérer la valeur

Lors de l’import des données, Power Query a appliqué automatiquement plusieurs transformations. Il a utilisé la première pour les en-têtes et il a modifié les types de données des colonnes. Nous allons commencer par supprimer ces étapes avant d’appliquer nos transformations.

Sélectionner la dernière étape - Type modifié - et cliquer sur la croix pour la supprimer
Renouveler cette opération pour l’étape précédente : En-têtes promus
Clic droit sur l’étape Navigation et sélectionner Insérer l’étape après
Renommer l’étape pour pouvoir vous y référer plus facilement
Identifier le numéro de ligne et l’en-tête de colonne de la cellule qui nous intéresse

/!\ Dans Power Query, la première ligne est égale à 0.

Récupérer la ligne pour les en-têtes

L’objectif de cette étape est d’obtenir un tableau avec uniquement les lignes et les colonnes qui nous intéressent

Dans le menu Accueil > Réduire les lignes, cliquez sur Supprimer les lignes > Supprimer les lignes du haut
Dans la fenêtre Supprimer les lignes du haut, saisissez 2 dans le champ Nombre de lignes
Dans le menu Accueil > Transformer, cliquer sur Utiliser la première ligne pour les en-têtes

Ajouter une colonne personnalisée avec la date d’extraction

Nous allons maintenant ajouter dans une nouvelle colonne la date de l’extraction de nos données.

C’est le moment d’utiliser les informations que nous avons identifiées à l’étape 2 : le nom de l’étape de référence : Base; le numéro de la ligne : 0; et l’en-tête de la colonne : Column1.

Dans le menu Ajouter une colonne > Général, cliquer sur Colonne personnalisée
Dans le champ Nouveau nom de colonne, Saisissez Date
Dans le champ Formule de la colonne personnalisée, Saisissez la formule ci-dessus
Cliquez sur OK
Dans le panneau Étapes d’une requête, renommer l’étape en Date Extraction ajoutée

Nous avons maintenant une nouvelle colonne Date qui contient pour chaque ligne le contenu de la cellule identifiée : “Extract SAP du 18/12/2022”.

Mettre en forme la nouvelle colonne Date

Quelques transformations supplémentaires pour mettre en forme notre nouvelle colonne et le tableau sera prêt.

Sélectionnez la colonne Date
Clic-droit et sélectionnez Remplacer les valeurs
Dans le champ Valeur à rechercher de la fenêtre, saisissez “Extract SAP du “
Garder vide le champ Remplacer par
Cliquez sur OK
Cliquez sur l'icône à gauche de l’entête et sélectionnez Date
Clic-droit et sélectionnez Déplacer > Au début

Et voilà ! Le tableau est prêt à être utilisé dans Excel

Chaque semaine, vous n’avez plus qu’à remplacer le fichier source avec le fichier de la nouvelle extraction. Et vos données se mettront à jour automatiquement et dynamiquement par un simple clic sur le bouton Actualiser.

Et maintenant, à vous de jouer ! Prenez vos données et suivez les étapes pas à pas pour construire votre propre fichier.

Gestion de stocks, approvisionnement, ventes, finances. Je suis curieux de connaître les cas d’usages que vous rencontrez dans votre quotidien. N'hésitez pas à me laisser un commentaire.

AUTRES ARTICLES DATA SUPPLY CHAIN