La ligne de fond : Apprenez comment cette fonctionnalité géniale d’Excel et de Power BI appelée Power Query vous aidera à automatiser le processus d’importation, de transformation et de nettoyage de vos données pour gagner une TONNE de temps dans votre travail.
Niveau de compétence : Débutant
Tutoriel vidéo
Télécharger les fichiers échantillons
Les fichiers CSV que j’utilise dans la vidéo sont disponibles en téléchargement ci-dessous. Vous devrez extraire les fichiers du fichier zip.
Fichiers de commande CSV pour Power Query Overview.zip (337.0 KB)
Introduction à Power Query
Dans ce tutoriel, je fournis une explication introductive de Power Query. Vous apprendrez pourquoi c’est mon nouvel outil Excel préféré pour travailler avec des données, et comment il peut aider à automatiser des processus et vous faire gagner du temps !
La machine à données de Power Query
Je regardais une émission de télévision sur la fabrication des choses, et ils expliquaient comment fonctionnait une machine à déposer dans une usine de pâtisserie.
Les bases d’une machine à déposer sont :
- Vous y ajoutez des ingrédients.
- Changez certains paramètres.
- Et elle crée comme par magie des pâtisseries (cookies, donuts, biscuits) qui sont prêtes à être cuites.
Une fois que les cadrans sont réglés, le processus peut être répété encore et encore pour faire des pâtisseries parfaites à chaque fois. Vous avez faim… ? 🙂
Power Query fonctionne de manière très similaire !
- Vous ajoutez vos sources de données (tableaux Excel, fichiers CSV, tableaux de base de données, pages web, etc.)
- Appuyez sur des boutons dans la fenêtre Power Query Editor pour transformer vos données.
- Sortir ces données vers votre feuille de calcul ou votre modèle de données (PowerPivot) qui est prêt pour les tableaux croisés dynamiques ou le reporting.
Power Query est comme une machine car une fois que vous avez configuré votre requête, le processus peut être répété en cliquant sur un bouton (rafraîchir) chaque fois que vos données changent.
Si vous avez utilisé des macros pour transformer vos données, vous pouvez considérer ceci comme une alternative beaucoup plus facile à VBA qui ne nécessite PAS de codage.
Tâches de données communes rendues faciles
Travaillez-vous avec des données qui ont été exportées d’un système d’enregistrement ? Il peut s’agir d’un grand livre, d’un système comptable, d’un ERP, d’un CRM, de Salesforce.com ou de tout système de reporting contenant des données.
Si c’est le cas, vous passez probablement beaucoup de temps à transformer ou à remodeler vos données pour créer des rapports, des tableaux croisés dynamiques ou des graphiques supplémentaires.
Ces transformations de données pourraient inclure des tâches comme :
- Supprimer des colonnes, des lignes, des blancs
- Convertir des types de données – texte, chiffres, dates
- Séparer ou fusionner des colonnes
- Trier & des colonnes de filtre
- Ajouter des colonnes calculées
- Agréger ou résumer des données
- Trouver & remplacer du texte
- Déplier des données à utiliser pour les tableaux croisés dynamiques
Certaines de ces tâches vous semblent-elles familières ? Si oui, alors elles vous semblent probablement aussi ennuyeuses, répétitives et chronophages 🙂 Croyez-moi, j’ai passé la majeure partie de ma carrière à effectuer ces tâches et à essayer de trouver des moyens plus rapides de les réaliser.
Heureusement, Power Query dispose de boutons qui automatisent toutes ces tâches !
Vue d’ensemble du ruban Power Query
Depuis Excel 2016 pour Windows, Power Query a été entièrement intégré à Excel. Il se trouve désormais sur l’onglet Données du ruban dans le groupe Obtenir & Transformer.
Dans Excel 2010 et 2013 pour Windows, Power Query est un module complémentaire gratuit. Une fois installé, l’onglet Power Query sera visible dans le ruban Excel.
Vous utilisez les boutons de l’onglet Données ou Power Query pour obtenir vos données sources. Encore une fois, vos données pourraient être stockées dans des fichiers Excel, des fichiers csv, Access, une base de données SQL server, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, des sites Web, et plus encore.
Une fois que vous avez spécifié d’où proviennent vos données, vous utilisez ensuite la fenêtre de l’éditeur Power Query pour effectuer des transformations sur les données.
Les boutons de la fenêtre Power Query Editor vous permettent de transformer vos données.
Réfléchissez à certaines de ces tâches que vous effectuez de manière répétée en parcourant les boutons de l’image ci-dessus. Chaque fois que vous appuyez sur un bouton, vos actions (étapes) sont enregistrées, et vous pouvez rapidement réappliquer les étapes lorsque vous recevez de nouvelles données en actualisant la requête.
Après avoir terminé vos étapes, vous pouvez sortir les données dans un tableau de votre classeur Excel en cliquant sur le bouton Fermer & Charger.
Vous pouvez également modifier les requêtes existantes et rafraîchir vos tableaux de sortie avec les modifications ou les données mises à jour.
Exemples de transformation de données
Voici quelques exemples de ce que Power Query peut faire avec vos données.
Unpivoter les données pour les tableaux croisés dynamiques
Ma fonctionnalité préférée de Power Query est sa capacité à Unpivoter les données. Il s’agit d’une technique utilisée pour que vos données soient prêtes pour la source d’un tableau croisé dynamique. On parle également de normalisation de vos données pour les obtenir dans un format tabulaire.
Les données pourraient commencer par ressembler à ce qui suit.
Et vous voulez que le résultat final ressemble à ceci .
Power Query peut le faire en cliquant sur quelques boutons, et préparer vos données pour les utiliser dans un tableau croisé dynamique.
Voici un article et une vidéo sur exactement comment dépivoter vos données avec Power Query.
Voyez mon article sur la façon de structurer vos données sources pour un tableau croisé dynamique si vous ne savez pas pourquoi vos données doivent ressembler à cela pour un tableau croisé dynamique.
Append (Combine) Tables with Power Query
La fonctionnalité Append de Power Query vous permet de combiner plusieurs tables (les empiler verticalement) pour créer une grande table. Elle peut le faire avec plusieurs tableaux dans un fichier, ou elle peut tirer des données d’un tas de fichiers/sources différents.
Disons que vous avez un dossier qui contient des fichiers CSV ou Excel avec des données de rapport pour chaque mois. Jetez tous ces fichiers dans la machine Power Query, et elle crachera un joli tableau que vous pourrez ensuite utiliser pour créer des tableaux croisés dynamiques et des graphiques.
Si les données de ces rapports doivent également être transformées (supprimer des lignes, diviser des colonnes, dé-pivoter, etc.), alors Power Query peut gérer cela dans le même processus.
Une fois qu’il est configuré, tout ce que vous avez à faire est d’appuyer sur le bouton de rafraîchissement chaque mois lorsqu’un nouveau fichier est ajouté au dossier et les lignes seront ajoutées à votre tableau de sortie.
Comme c’est génial ! 🙂
Fusionner des tableaux – Une alternative VLOOKUP
Power Query a la capacité de fusionner ou de joindre des tableaux. Cela peut être utilisé comme une alternative aux formules VLOOKUP ou INDEX/MATCH.
Disons que vous avez ce tableau de données d’enregistrements de ventes, et que vous utilisez un VLOOKUP pour apporter des informations sur le produit en fonction du nom du produit vendu. Les informations sur votre groupe de produits se trouvent dans une autre table sur une autre feuille ou un autre classeur.
Utiliser les formules VLOOKUP est génial, mais cela peut souvent signifier ajouter des milliers de formules à votre classeur. Ce qui augmente la taille du fichier et le temps de calcul.
Power Query permet de fusionner très rapidement et facilement deux tableaux en quelques clics. Il utilise essentiellement des jointures SQL, donc vous pouvez même faire des fusions plus avancées comme des jointures internes, externes, gauches, droites, complètes et anti jointures.
Créer des fonctions personnalisées
Power Query a été conçu pour que vous n’ayez PAS besoin de savoir coder pour l’utiliser. Il est très facile à utiliser car vous pouvez simplement cliquer sur des boutons et appliquer des filtres comme vous le feriez normalement dans Excel.
Cependant, Power Query peut être programmé pour créer des fonctions personnalisées. Cela vous donne un potentiel apparemment illimité pour transformer vos données d’à peu près toutes les manières possibles.
Il est basé sur le langage M, et la plupart des fonctions sont très similaires à l’écriture d’une formule dans Excel. Cela le rend également plus convivial et plus facile à apprendre le code.
Ce nouveau langage et cet ensemble de fonctions signifient qu’il y a beaucoup à apprendre, mais je considère que c’est la partie amusante et stimulante. De plus, les employeurs du futur rechercheront certainement des employés ayant des compétences en Power Query.
Power Query enregistre vos étapes & Automatise les processus
Power Query facilite non seulement toutes ces tâches, mais il enregistre également vos étapes afin que vous n’ayez PAS à les refaire. Cela vous fera gagner beaucoup de temps si vous préparez les mêmes données tous les jours, toutes les semaines ou tous les mois.
Il gère également assez bien les erreurs. Si la structure de vos données source change, Power Query vous dira à quelle étape elle s’est cassée et vous permettra de la corriger. Cela facilite la maintenance et vous n’avez pas à refaire complètement votre processus lorsque quelque chose change.
Vous pouvez utiliser Power Query pour que vos données soient prêtes à être utilisées dans des tableaux croisés dynamiques, des graphiques et des rapports de tableau de bord. Il s’agit d’une étape essentielle dans le processus de synthèse et d’analyse des données.
La machine Power Query & Power BI
Bien qu’elle ne puisse pas exactement fabriquer des biscuits, Power Query est un outil assez génial ! Il vous fera gagner une tonne de temps lors de la transformation de vos données.
Power Query n’est qu’une pièce de la suite de produits Power BI (Business Intelligence) de Microsoft.
Si nous reprenons l’analogie de la fabrication de biscuits dans une usine, vous pouvez considérer Power Query comme la première étape de la chaîne de montage. Une fois les biscuits formés, il faut ensuite les cuire (tableaux croisés dynamiques, PowerPivot), puis les emballer pour les présenter (Power View, Power Map, graphiques, tableaux de bord, etc.)
Vous pouvez considérer Excel comme le bâtiment de l’usine qui abrite tous ces outils. Ce qui est passionnant, c’est que l’avenir d’Excel est très prometteur ! Toutes ces avancées technologiques nous aideront à donner un sens à nos données de nouvelles façons, à nous faire gagner du temps et à impressionner nos patrons 🙂
Comment puis-je obtenir Power Query?
L’autre partie intéressante est que Power Query est maintenant intégré à Excel à partir d’Excel 2016 pour Windows. Si vous êtes sur Excel 2010 ou 2013 alors Power Query est un add-in gratuit.
J’ai une page dédiée qui vous aidera à déterminer si vous avez la bonne version d’Excel pour obtenir Power Query. Elle fournit également des instructions d’installation complètes et le lien de téléchargement.
Guide complet d’installation de Power Query
Pour vous donner une idée de l’importance de cet outil, Power Query a été entièrement intégré à Excel dans Excel 2016 pour Windows, et se trouve dans l’onglet Données du ruban.
Il est également connu sous le nom de Get & Transform, bien que le terme Power Query soit le plus courant.
Ressources supplémentaires
Cet article a fourni un aperçu des bases de Power Query qui devrait vous aider à comprendre certaines des principales fonctionnalités. Power Query a une tonne de fonctionnalités et il y a certainement beaucoup à apprendre.
Je partagerai d’autres articles et vidéos » comment faire » dans les semaines à venir. Voici quelques ressources qui vous aideront à démarrer.
Comment dépivoter vos données avec Power Query + Tutoriel vidéo
Séminaire de formation gratuit sur les Power Tools
En ce moment, j’organise un séminaire de formation gratuit sur tous les Power Tools d’Excel. Cela inclut Power Query, Power Pivot, Power BI, les tableaux croisés dynamiques, les macros & VBA, et plus encore.
Ce séminaire s’intitule The Modern Excel Blueprint. Pendant le webinaire, j’explique ce que sont ces outils et comment ils peuvent s’intégrer dans votre flux de travail.
Vous apprendrez également comment devenir le héros Excel de votre organisation, cette fille ou ce gars sur qui tout le monde compte pour obtenir de l’aide sur Excel et réaliser des projets amusants.
Le webinaire est organisé à plusieurs jours et heures. Veuillez cliquer sur le lien ci-dessous pour vous inscrire et réserver votre place.
Cliquez ici pour vous inscrire au webinaire gratuit