Récupérer et transformer des données web avec Excel et Power Query

(Last Updated On: )

Grace à l’add-in PowerQuery pour Excel 2013 il était déjà possible de récupérer des données depuis une multitude de sources de données, allant du fichier texte, les bases de données à internet, puis de transformer ces données pour une exploitation plus aisée dans Excel ou dans son add-in intégré de Business Intelligence PowerPivot.

Désormais que PowerQuery est intégré à Excel 2016 (Menu Données -> Récupérer et Transformer), je trouve qu’il est encore plus simple de récupérer des données externes pour les utiliser dans un classeur Excel !

Dans cet article, je vous montre comment récupérer une table de données depuis internet, et la transformer pour pouvoir l’analyser à l’aide de tableaux croisés dynamiques.

J’ai par ailleurs utilisé cette technique pour récupérer la liste des fonctions Excel et leur traduction en anglais sur internet, et créer ensuite un jeu de données exploitables avec un TCD.

Table of Contents

Regarder la vidéo

Voici en vidéo les étapes pour récupérer et transformer des données depuis internet avec Récupérer et Transformer de Excel 2016 (add-in PowerQuery sur la version d’Excel 2013).

Données internet récupérées

Donc, voici une capture d’écran de la table que nous allons importer :

Importation et transformation des données

La récupération et la transformation des données sous Excel suit ces quelques grandes étapes:

Connecter une ou des sources de données
Transformer les données
Combiner les données entre elles
Restituer ou partager l’information obtenue

Ouvrez Excel, cliquez sur l’onglet Données -> Récupérer et Transformer -> Nouvelle requête -> À partir d’autres sources -> À partir du Web.
–
Dans la boîte de dialogue A partir du Web, collez l’URL ci-dessous et cliquez sur OK.
http://ec.europa.eu/eurostat/tgm/table.do?tab=table&init=1&language=en&pcode=tsdtr100&plugin=1
–
Dans la boîte de dialogue Navigateur, sélectionnez les deux tables Table 0 et Table 1, et cliquez sur Modifier pour nettoyer les données dans l’éditeur de requête avant qu’elles ne soit chargées dans votre classeur.
–

–
L’éditeur de requête apparaît avec les deux tables importées.
–
D’après l’image ci-dessous, la table 0 représente la colonne des pays européens et la table 1 représente les valeurs d’indice par année et par pays.
–
Il va donc falloir réunir ces deux tables pour obtenir la table de données d’origine. Pour cela, on créé une colonne d’index pour chaque table, contenant un numéro croissant par ligne (de 1 à 38 ici). Ces colonnes d’index vont servir à joindre les deux tables en faisant correspondre les numéros d’index entre eux. Voici les étapes à suivre.
–
Sélectionner la première table, aller dans Ajouter une colonne -> cliquez sur Colonne d’index.
Sélectionner la deuxième table, aller dans Ajouter une colonne -> cliquez sur Colonne d’index.
–
Combiner les deux tables : Aller dans Accueil -> Combiner -> Fusionner des requêtes
Sélectionnez les deux tables, puis sélectionnez les colonnes d’index. Cliquez sur OK pour valider.
–
On obtient une nouvelle colonne dans la première table. En réalité, cette colonne contient la deuxième table en entier, mais il reste à sélectionner les colonnes de la deuxième tables qui nous intéressent.
Ici on sélectionne uniquement la colonne des pays en cliquant sur l’icône de la nouvelle colonne:
–
Ensuite, on renomme les colonnes avec l’année correspondante, sachant que dans la table d’origine, l’année 2010 contient l’indice 100,0 pour chaque ligne. Puis on supprime les colonnes d’index et les colonnes des années antérieures à 2005.
–
Enfin, pour pouvoir exploiter ces données à l’aide de tableaux croisés dynamique, il faut décroiser les données de la table. Pour cela, sélectionnez la colonne des pays puis allez dans le menu Transformer -> Supprimer le tableau croisé dynamique des autres colonnes :
–

Pour plus d’explication sur cette étape, j’ai écrit un article sur comment décroiser des données dans Excel.
–
On obtient une table avec trois colonnes. Renommez les colonnes comme suivant : Pays, Année et Indice.
–
La table est prête pour Excel. Allez dans Accueil -> cliquez sur Fermer et charger.
–
Notez que la table transformée ainsi que la première table (Table 0) sont chargées dans le classeur Excel. Or cette dernière ne nous est plus utile dans Excel, on va donc la masquer. Pour cela, dans le panneau de droite listant les requêtes du classeur, faites un clic droit sur la Table 0, puis Charger dans, et cocher Créer uniquement la connexion.
–
Enfin, vous pouvez créer un tableau croisé dynamique avec ces données (en ayant convertit si nécessaire les valeurs texte en nombre), ainsi qu’un graphique croisé dynamique pour analyser ces données.

Et voilà, la récupération et la transformation de ces données issues d’internet est terminée !

J’espère que vous avez apprécié cet article. Si c’est le cas n’hésitez pas à laisser un commentaire et partager 😉

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seuls votre adresse e-mail et nom vous seront demandés dans la prochaine étape. Vous recevrez le lien de téléchargement par e-mail.