Comment nettoyer et importer des données à l'aide de Power Query dans Excel

En 2010, Microsoft a ajouté un autre terme technique, Power Query, à la longue liste de jargon d'Excel, mais il n'est pas aussi complexe qu'il y paraît. En fait, une fois que vous avez commencé, la courbe d'apprentissage n'est pas trop abrupte, à condition que vous ayez au moins une compréhension de Microsoft Excel de niveau débutant.

Qu’est-ce que Power Query ?

Lors de son introduction dans Excel, Power Query était un complément, mais compte tenu de son utilité, il s’agit d’un outil natif depuis 2016.

En bref, Power Query a un objectif principal : vous faire gagner du temps lors de la manipulation des données. Plus précisément, il peut être utilisé pour nettoyer des données déjà dans Excel, importer et organiser des données provenant de nombreux endroits différents ou consolider divers fichiers avant de charger les données dans un emplacement spécifique de votre choix. Vous pouvez également actualiser les données pour vous assurer que vous disposez de la dernière version.

Les outils Power Query d'Excel se trouvent dans le groupe Obtenir et transformer des données de l'onglet Données.

Dans cet article, je vais vous montrer comment utiliser Power Query pour nettoyer vos données et comment importer et organiser des données à partir d'une autre feuille de calcul.

Utiliser Power Query pour nettoyer les données Excel

J'utilise toujours Power Query pour nettoyer les données de mes feuilles Excel. Supposons que vous ayez copié une liste d'adresses e-mail du champ CC d'un e-mail Outlook dans la cellule A1 et que vous souhaitiez convertir la liste en un tableau contenant les prénoms (ou titres), les noms de famille et les adresses e-mail. Bien que vous puissiez utiliser les fonctions Excel pour ce faire, c'est beaucoup plus facile (et une courbe d'apprentissage moins abrupte !) dans l'éditeur Power Query d'Excel.

Tout d’abord, ouvrez l’onglet « Données » sur le ruban et cliquez sur « À partir de la table/plage » dans le groupe Données.

Ensuite, dans la boîte de dialogue « Créer un tableau », assurez-vous que la ou les cellules correctes sont sélectionnées et indiquez à Excel si vos données contiennent des titres (dans mon cas, ce n'est pas le cas, je laisse donc cette case décochée) et cliquez sur « OK ».

Excel ouvrira ensuite l'éditeur Power Query, et c'est ici que vous pourrez faire beaucoup de choses étranges et merveilleuses, notamment organiser vos données.

La première étape consiste à diviser chaque adresse e-mail en sa propre ligne. Cliquez donc avec le bouton droit sur l'en-tête de la colonne, passez la souris sur « Diviser la colonne » et cliquez sur « Par délimiteur ». Vous pouvez choisir d'autres façons de diviser vos données, par exemple après un certain nombre de caractères ou entre des lettres majuscules. Dans mon cas, cependant, les adresses e-mail sont séparées par des points-virgules, donc l'option délimiteur fonctionne.

Dans la boîte de dialogue Diviser la colonne par délimiteur, sélectionnez « Point-virgule » dans la liste déroulante. Ensuite, cliquez sur « Options avancées » et cochez « Lignes », car vous souhaitez que les adresses e-mail soient divisées en lignes individuelles plutôt qu'en colonnes individuelles. Maintenant, cliquez sur « OK ».

Et cela a bien fonctionné : les adresses e-mail ont été divisées en lignes distinctes, Power Query utilisant le délimiteur point-virgule pour déterminer quand un e-mail se termine et quand le suivant commence.

Après avoir double-cliqué sur l'en-tête de la colonne et saisi Adresse emailvous souhaitez extraire le prénom (ou titre) et le nom dans des colonnes séparées. Il existe plusieurs façons de procéder, mais la meilleure méthode consiste à cliquer avec le bouton droit sur l'en-tête de colonne et à sélectionner « Ajouter une colonne à partir d'exemples ».

Maintenant, puisque le premier nom de ma liste est Capitaine, je vais le taper dans la colonne qui apparaît à droite de mon écran et appuyer sur Entrée. Et hop ! L’éditeur Power Query reconnaît un modèle et suggère les noms restants pour compléter ma colonne. Une fois que vous avez vérifié que vous êtes satisfait de la suggestion, cliquez sur « OK ».

Suivez ensuite le même processus pour le nom de famille et renommez les en-têtes de colonnes dans l’éditeur Power Query.

Vous pouvez maintenant réorganiser les colonnes en cliquant dessus et en les faisant glisser. Dans mon cas, je souhaite que la colonne d'adresse e-mail soit à droite des noms.

Avant de charger ce nouveau tableau dans votre feuille de calcul, donnez-lui un nom dans le volet Paramètres de requête.

Maintenant, cliquez sur le menu déroulant « Fermer et charger » dans le ruban et choisissez « Fermer et charger vers ».

Dans mon cas, je souhaite qu'il se charge sous forme de tableau dans la cellule A4 de la feuille de calcul existante. Voici donc les options que je choisirai dans la boîte de dialogue Importer des données avant de cliquer sur « OK ».

Si je dois apporter des modifications au tableau, je peux double-cliquer sur la requête dans le volet Requêtes et connexions.

De même, si je modifie la liste originale que j'ai copiée depuis Outlook (par exemple en ajoutant une autre adresse e-mail), je peux mettre à jour mon tableau Power Query en cliquant avec le bouton droit n'importe où dans le tableau et en cliquant sur « Actualiser ».

Utilisation de Power Query pour importer et réorganiser des données

Power Query peut également être utilisé pour importer des données à partir de divers emplacements, comme un PDF ou un site Web. Dans cet exemple, je vais vous montrer comment importer et manipuler des données à partir d'une autre feuille de calcul, ce qui est particulièrement utile si vous souhaitez extraire un tableau très spécifique de données à partir d'un vaste ensemble de données, ou si vous souhaitez réorganiser la façon dont vos données sont disposé.

Tout d'abord, ouvrez l'onglet « Données » et cliquez sur Obtenir des données > À partir d'un fichier > À partir d'un classeur Excel.

Ensuite, utilisez la fenêtre Importer des données pour localiser le fichier à partir duquel vous importez les données, puis cliquez sur « Importer ». Cela lancera la fenêtre du Navigateur, qui prévisualise les données que vous pouvez importer à partir de la feuille de calcul désignée. Dans mon cas, je clique sur « Feuille 1 », où se trouvent les données que je souhaite importer, puis je clique sur « Transformer les données ».

Dans l'éditeur Power Query qui se charge, je pourrais cliquer immédiatement sur « Enregistrer et charger » dans le coin supérieur gauche, mais il y a quelques modifications à apporter avant de le faire.

Tout d’abord, je dois promouvoir ma première ligne parmi les en-têtes de colonnes, afin que la première ligne de ma feuille de calcul soit la première ligne de données. Pratique, il existe un bouton qui vous permet de le faire en un instant : « Utiliser la première ligne comme en-têtes » dans le groupe Transformation de l'onglet Accueil.

Deuxièmement, je souhaite une nouvelle ligne de données pour chaque magasin et chaque mois, afin de pouvoir les analyser individuellement. À l'heure actuelle, mes deux premières colonnes sont bonnes telles quelles, je peux donc les sélectionner en cliquant sur chaque en-tête de colonne tout en maintenant la touche Ctrl enfoncée. Ensuite, je clique avec le bouton droit sur l'un des en-têtes de colonne, puis je clique sur « Annuler le pivotement des autres colonnes ».

Désormais, chaque boutique dispose de sa propre ligne pour chaque mois.

Enfin, avant de charger cela dans ma feuille de calcul, je dois ranger les en-têtes de mes colonnes. Si vous souhaitez faire de même, double-cliquez sur l'un des en-têtes pour les renommer, puis cliquez sur le symbole à gauche de chaque en-tête de colonne pour définir le type de données. Cela forcera Power Query à signaler tout problème avec les données, par exemple si l’une des cellules de la colonne Mois ne contient pas de mois.

Maintenant, après avoir nommé votre table dans le volet Paramètres de requête à droite de la fenêtre Power Query, cliquez sur Fermer et charger > Fermer et charger vers, et sélectionnez l'emplacement où vous souhaitez que la table apparaisse.

N'oubliez pas que l'un des principaux avantages de l'utilisation de Power Query par rapport au simple copier-coller des données est que si les données sources changent, vous pouvez mettre à jour les données remaniées dans votre nouvelle feuille de calcul. Pour vous assurer que vous disposez de la dernière version des données, cliquez simplement avec le bouton droit sur le nouveau tableau, puis cliquez sur « Actualiser ».

Vous pouvez également utiliser Power Query pour importer des tables depuis le Web. Par exemple, vous souhaiterez peut-être importer un classement susceptible de changer chaque week-end. Ainsi, plutôt que de copier et coller les données manuellement et que le classement devienne rapidement obsolète, l'utilisation de Power Query peut vous aider à vous assurer que vous disposez de la dernière version. des données.