Power Query est intégré aux versions Windows d'Office 365, Excel 2016, Excel 2019 et est disponible en téléchargement gratuit dans les versions Windows d'Excel 2010 et Excel 2013. L'outil est conçu pour extraire, transformer et charger des données dans Excel à partir d'un variété de sources. La meilleure partie: Power Query se souvient de vos étapes et les lira lorsque vous souhaitez actualiser les données. Cela signifie que vous pouvez nettoyer les données le jour 1 dans 80% du temps normal, et vous pouvez nettoyer les données des jours 2 à 400 en cliquant simplement sur Actualiser.
Je dis cela à propos de nombreuses nouvelles fonctionnalités d'Excel, mais c'est vraiment la meilleure fonctionnalité pour frapper Excel dans 20 ans.
Je raconte une histoire dans mes séminaires en direct sur la façon dont Power Query a été inventé comme une béquille pour les clients de SQL Server Analysis Services qui ont été forcés d'utiliser Excel pour accéder à Power Pivot. Mais Power Query ne cesse de s'améliorer et chaque personne utilisant Excel devrait prendre le temps d'apprendre Power Query.
Obtenez Power Query
Vous disposez peut-être déjà de Power Query. Il se trouve dans le groupe Obtenir et transformer sous l'onglet Données.
Mais si vous êtes dans Excel 2010 ou Excel 2013, accédez à Internet et recherchez Télécharger Power Query. Vos commandes Power Query apparaîtront dans un onglet Power Query dédié dans le ruban.
Nettoyer les données la première fois dans Power Query
Pour vous donner un exemple de certains des avantages de Power Query, disons que vous obtenez le fichier ci-dessous chaque jour. La colonne A n'est pas remplie. Les trimestres passent au lieu de descendre dans la page.
Pour commencer, enregistrez ce classeur sur votre disque dur. Placez-le dans un endroit prévisible avec un nom que vous utiliserez pour ce fichier tous les jours.
Dans Excel, sélectionnez Obtenir des données, à partir d'un fichier, à partir d'un classeur.
Accédez au classeur. Dans le volet Aperçu, cliquez sur Sheet1. Au lieu de cliquer sur Charger, cliquez sur Modifier. Vous voyez maintenant le classeur dans une grille légèrement différente - la grille Power Query.
Vous devez maintenant corriger toutes les cellules vides de la colonne A. Si vous deviez le faire dans l'interface utilisateur d'Excel, la séquence de commandes peu maniable est Accueil, Rechercher et sélectionner, Aller à spécial, Blancs, Égal, Flèche vers le haut, Ctrl + Entrée .
Dans Power Query, sélectionnez Transformer, Remplir, Bas.
Toutes les valeurs nulles sont remplacées par la valeur ci-dessus. Avec Power Query, il faut trois clics au lieu de sept.
Problème suivant: les quartiers traversent au lieu de descendre. Dans Excel, vous pouvez résoudre ce problème avec un tableau croisé dynamique de plusieurs plages de consolidation. Cela nécessite 12 étapes et plus de 23 clics.
Dans Power Query, sélectionnez les deux colonnes qui ne sont pas des trimestres. Ouvrez la liste déroulante Unpivot Columns sur l'onglet Transform et choisissez Unpivot Other Columns, comme indiqué ci-dessous.
Cliquez avec le bouton droit de la souris sur la colonne Attribut nouvellement créée et renommez-la Quarter au lieu d'Attribut. Plus de vingt clics dans Excel deviennent cinq clics dans Power Query.
Maintenant, pour être honnête, toutes les étapes de nettoyage ne sont pas plus courtes dans Power Query que dans Excel. Supprimer une colonne signifie toujours faire un clic droit sur une colonne et choisir Supprimer la colonne. Mais pour être honnête, l'histoire ici ne concerne pas les gains de temps au premier jour.
Mais attendez: Power Query se souvient de toutes vos étapes
Regardez sur le côté droit de la fenêtre Power Query. Il existe une liste appelée Étapes appliquées. C'est une piste d'audit instantanée de toutes vos étapes. Cliquez sur n'importe quelle icône d'engrenage pour modifier vos choix à cette étape et faire en sorte que les modifications se répercutent dans les étapes futures. Cliquez sur n'importe quelle étape pour voir à quoi ressemblaient les données avant cette étape.
Lorsque vous avez terminé de nettoyer les données, cliquez sur Fermer et charger comme indiqué ci-dessous.
Pointe
Si vos données comportent plus de 1 048 576 lignes, vous pouvez utiliser la liste déroulante Fermer et charger pour charger les données directement dans le modèle de données Power Pivot, qui peut contenir 995 millions de lignes si vous disposez de suffisamment de mémoire installée sur l'ordinateur.
En quelques secondes, vos données transformées apparaissent dans Excel. Impressionnant.
La récompense: nettoyer les données demain en un seul clic
Mais encore une fois, l'histoire de Power Query ne concerne pas les gains de temps au jour 1. Lorsque vous sélectionnez les données renvoyées par Power Query, un panneau Requêtes et connexions apparaît sur le côté droit d'Excel, et il y a un bouton Actualiser. (Nous avons besoin d'un bouton Modifier ici, mais comme il n'y en a pas, vous devez cliquer avec le bouton droit sur la requête d'origine pour afficher ou apporter des modifications à la requête d'origine).
C'est amusant de nettoyer les données le premier jour. J'adore faire quelque chose de nouveau. Mais quand mon manager voit le rapport qui en résulte et dit «Magnifique. Pouvez-vous faire cela tous les jours? » Je déteste rapidement l'ennui de nettoyer le même ensemble de données chaque jour.
Donc, pour démontrer le jour 400 du nettoyage des données, j'ai complètement changé le fichier d'origine. Nouveaux produits, nouveaux clients, plus petits nombres, plus de lignes, comme indiqué ci-dessous. J'enregistre cette nouvelle version du fichier dans le même chemin et avec le même nom de fichier que le fichier d'origine.
Si j'ouvre le classeur de requête et clique sur Actualiser, dans quelques secondes, Power Query signale 92 lignes au lieu de 68 lignes.
Le nettoyage des données le jour 2, le jour 3, le jour, 4,… le jour 400,… le jour Infinity prend désormais deux clics.
Cet exemple ne fait qu'effleurer la surface de Power Query. Si vous passez deux heures avec le livre, M est pour (Data) Monkey de Ken Puls et Miguel Escobar, vous en apprendrez davantage sur d'autres fonctionnalités, telles que celles-ci:
- Combinaison de tous les fichiers Excel ou CSV d'un dossier dans une seule grille Excel
- Conversion d'une cellule avec Apple; Banane; Cerise; Aneth; Aubergine en cinq lignes dans Excel
- Effectuer une recherche dans un classeur de recherche lorsque vous importez des données dans Power Query
- Faire d'une seule requête une fonction qui peut être appliquée à chaque ligne dans Excel
Pour une description complète de Power Query, consultez M Is for (Data) Monkey par Ken Puls et Miguel Escobar. À la fin de 2019, la deuxième édition rebaptisée, Master Your Data, sera disponible.
Merci à Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser et Colin Michael pour la nomination de Power Query.
