Apprendre Power Query : Pourquoi Excel est limité !

Apprendre Power Query : Pourquoi Excel est limité !

Apprendre Power Query devient une nécessité pour aller plus loin avec Excel. Ce composant a fait son apparition avec la version 2013 d’Excel et depuis 2016 est intégré nativement à Excel. Il remplace peu à peu certaines fonctionnalités historiques d’Excel.

Qu’est-ce que Power Query ? Pourquoi faut-il l’apprendre ? En quoi est-ce qu’Excel est limité ? Et pourquoi Power Query va vous permettre d’aller plus loin ? Par où commencer ? Et après ? Toutes ces questions seront traitées dans cet article. Il ne tient qu’à vous de le lire et d’aller plus loin avec les outils à votre disposition !

Cet article se veut une introduction au monde de Power Query. Je vais donc utiliser des analogies et non les termes techniques exacts que vous aurez le temps d’apprendre le moment venu.

Qu’est-ce que Power Query ?

Power Query permet de réaliser des opérations en masse sur vos données. Vous allez pouvoir intégrer plusieurs sources de données et les manipuler à souhait. Voilà.

Si ma description de Power Query s’arrêtait là, on pourrait penser à Excel. Ce n’est pas suffisant pour vous donner envie de l’apprendre ? C’est effectivement un peu léger comme description…

Power Query offre la possibilité de manier les données comme un cuisinier manie ses ingrédients. Vous pourrez découper, séparer, réunir, ajouter des données supplémentaires et cuisiner vos données pour préparer vos rapports.

Cela ressemble à Excel d’une certaine manière, et en même temps, c’est très différent. Véritablement différent.

En plus de réaliser ces opérations en masse, vous pourrez créer un modèle de données à partir d’un simple tableau. Afin que cela ait un peu de sens, vous devez disposer de grands tableaux (en nombre de lignes et en nombre de colonnes). Ainsi, vous pourrez commencer à réellement extraire le nectar que représente Power Query et vous aurez bientôt envie de l’apprendre !

Qu’est-ce qu’une opération en masse ?

Partons d’Excel que vous connaissez pour distinguer les opérations en masse des opérations que je nommerai unitaires.

Lorsque vous insérez une fonction dans une cellule d’Excel aussi simple qu’une addition « =A1+B1 », vous vous attendez à ce qu’en tout temps, la cellule contenant cette opération vous donne le résultat de l’addition.

Hormis la situation d’exception décrite un peu plus bas, c’est effectivement toujours le cas dans Excel.

Lors d’une opération en masse, ceci n’est plus vrai.

Considérez Power Query comme une chaîne automatisée d’une usine. Vous placez la matière première (ici les données) au début de la chaîne. L’usine va réaliser tous les traitements et vous donner le produit fini.

Si vous changez une pièce après que le produit soit sorti de la chaîne, cela ne change pas le résultat. Il faut jeter le produit et relancer la machine avec toutes les matières premières.

Cela ressemble énormément à ce que les macros permettent de faire : automatiser certains traitements. C’est vrai que cela y ressemble. Mais la programmation avec les macros est la voie du passé. Les risques de sécurité liés à l’utilisation des macros deviennent trop grands. Les macros seront bannies dans les prochaines années. Alors ne soyez pas en retard, tournez-vous vers les manières contemporaines de traiter les données : apprenez Power Query, Power BI et les fonctions Lambda !

Exception pour la MAJ des données automatiques dans Excel

Je fais une aparté ici concernant la mise à jour automatique des données dans Excel. Combien de fois ai-je failli devenir fou devant Excel lorsque le résultat ne correspondait pas à la formule… J’avais oublié que l’on pouvait désactiver l’actualisation automatique des calculs !

Apprendre Power Query - Option de calcul - Manuel
Option de calcul – Manuel

Cette option est très utile lorsque le temps d’actualisation de votre classeur est très long. C’est le cas lorsque vous avez beaucoup de formules ou qu’elles ne sont pas optimisées.

Quels types d’opérations peut-on réaliser avec Power Query ?

À partir de maintenant, vous devez penser à vos données comme un ensemble. Les opérations s’appliquent à l’ensemble uniquement.

Les personnes qui ont cet article ont aussi lu :  La fonction LAMBDA : La révolution est en marche !

Ainsi, l’opération « Ajouter » ne correspond à aucune addition. Si vous pensez « ensemble de données », « Ajouter » signifiera « Mettre plus de données dans mon ensemble ». Dans l’article, comment fusionner plusieurs fichiers Excel, je le précise déjà dans le scénario d’utilisation de Power Query.

Les opérations principales de Power Query

  • Obtenir des données – Intégrer des données venant d’Excel ou d’ailleurs
  • Ajouter des données – Mettre à la suite des ensembles de données de même structure (Pour les connaisseurs : UNION en SQL)
  • Fusionner des données : Enrichir les données d’un ensemble avec les données d’un autre ensemble (Pour les connaisseurs JOIN en SQL)
  • Ajouter / Supprimer des colonnes – Différentes modalités d’ajout existent (colonne calculée, colonne d’index automatique de ligne par exemple)
  • Supprimer des lignes selon vos critères (suppression des doublons, suppression de toutes les lignes sauf les doublons par exemple)
  • Transposer vos données – Une opération qui existe sous forme de fonction dans Excel 365 désormais
  • Pivoter une ou plusieurs colonnes – Cette opération ne devrait pas beaucoup vous parler à ce stade (et rien à voir avec les Pivot Table (soit le terme anglais de Tableau Croisé Dynamique)).

De nouveau, à ce stade, vous vous demandez encore quel est l’intérêt d’apprendre Power Query ? Excel sait fait tout cela… C’est vrai en partie seulement (je vous mets au défi de pivoter plusieurs colonnes sans programmation…).

Mais, car il y a un mais, Excel ne sait pas automatiser (sans programmation) l’exécution de ces opérations ! Quand est-ce cela est nécessaire ? Lorsque vous recevez des fichiers de vos fournisseurs, collègues ou clients à analyser.

Illustrons ces transformations à partir d’un fichier fourni par l’Université Johns Hopkins sur le COVID-19. Mais juste avant, encore un mot sur la place que prend Power Query au sein d’Excel.

L’obtention des données externes s’appuie déjà sur Power Query

Lorsque vous utilisez les fonctionnalités présentes dans l’onglet Données > Obtenir des données, Excel utilise Power Query.

Apprendre Power Query - Obtenir des données
Apprendre Power Query – Obtenir des données

Vous pourrez par exemple importer les données qui se trouvent dans un tableau d’un fichier PDF !

La combinaison des requêtes – Ajouter ou fusionner – utilise Power Query aussi

Dans l’article sur comment fusionner plusieurs fichiers Excel en un seul, je vous ai déjà présenté cette possibilité de mise en commun des données de plusieurs tableaux de données (voire de fichiers). C’est encore Power Query qui est utilisé ici.

Apprendre Power Query - Combiner des requêtes
Apprendre Power Query – Combiner des requêtes

L’import de données CSV dans Excel – Power Query a chassé l’ancien assistant d’importation de texte

L’ancien composant permettant d’importer les données des fichiers textes ou CSV disparaît au profit de Power Query une fois de plus.

Vous devrez ajouter manuellement la commande dans le ruban (encore présente mais masquée) pour retrouver cet assistant :

Apprendre Power Query - Assistant importation de texte
Apprendre Power Query – Assistant importation de texte

Désormais, vous aurez l’interface de Power Query :

Apprendre Power Query - Assistant importation de fichier CSV
Apprendre Power Query – Assistant importation de fichier CSV

L’importation des données depuis le web – Power Query est partout !

Les utilisateurs les plus avancés connaissaient cette fonction d’import des données web (on appelle cela le web scraping). Là encore, terminé la vieille interface :

Apprendre Power Query - Assistant importation depuis web - Ancienne version
Apprendre Power Query – Assistant importation depuis web – Ancienne version

Power Query est venu également remplacer ce composant, voyez par vous-même la nouvelle interface :

Apprendre Power Query - Assistant importation depuis web
Apprendre Power Query – Assistant importation depuis web

Power Query est partout où vous aurez à interagir avec Excel et l’extérieur d’Excel. Mais ceci n’est que la partie émergée de l’iceberg. La puissance de Power Query est bien ailleurs.

En quoi est-ce qu’Excel est limité ? Et pourquoi apprendre Power Query va vous permettre d’aller plus loin ?

La finalité d’Excel et ses limites

Excel peut être utilisé selon deux principes opposés :

  1. Excel est l’application de saisie de vos données, puis vous utilisez son potentiel pour les analyser (tableau croisé dynamique, graphiques, fonctions, etc.)
  2. Vous utilisez Excel uniquement pour ses capacité d’analyse de vos données qui ont été exportées depuis d’autres applications

Dans les deux cas, c’est dans la partie analyse que vous risquez d’atteindre les limites d’Excel.

Excel, comme n’importe quel logiciel, possède des limites techniques. Celles-ci sont d’ailleurs documentées par Microsoft.

Depuis que Microsoft a étendu le nombre de lignes et de colonnes d’Excel, ces limites techniques sont rarement atteintes.

Les autres limites sont celles que vous allez introduire dans des formules complexes, le nombre de tableaux croisés dynamiques à actualiser. Toutes ces formules à appliquer et ces TCD à rafraîchir vont mettre de plus en plus de temps.

Je vous avertis, pour côtoyer les limites d’Excel, il faut avoir soit beaucoup de données à traiter, soit beaucoup d’analyses différentes à réaliser.

Avertissement sur Excel en mode application de gestion

A titre personnel, je ne vous recommanderai jamais d’utiliser Excel pour en faire une application de gestion (gestion des stocks, gestion de la facturation, etc.).

De nombreuses raisons motivent cette prise de position :

  1. Toutes vos données sont stockées dans un simple fichier. En une manipulation, vos données peuvent être altérées ou détruites.
  2. Malgré la capacité de coédition, travailler à plusieurs sur un fichier Excel « Gestion du stock » vous posera des difficultés ou des erreurs.
  3. Pour ajouter des fonctionnalités, vous devez travailler sur le fichier contenant les dernières données… au risque de les altérer.
  4. Aucune gestion des droits d’accès ou de modification possible (eh non, la fonctionnalité de protection des feuilles/classeurs ne sécurise rien. Il est très simple de la supprimer sans connaître le mot de passe de protection).
Les personnes qui ont cet article ont aussi lu :  Apprendre Excel : Des premiers pas aux premiers résultats

Votre fichier Excel devient très lent et met du temps à rafraîchir les données ?

Vous êtes devenu un expert en RECHERCHEV() – entre nous, si vous êtes devenu cet expert, vous êtes passé soit à RECHERCHEX(), soit à INDEX() + EQUIVX() – et en avez mis partout ? Et vous êtes encore étonné que ce soit lent ?

Par exemple, vous avez un premier tableau rempli de références produits et dans un second tableau les caractéristiques détaillées de ces derniers. À l’aide de plusieurs RECHERCHEV(), vous avez ajouté des colonnes « Détails produits ».

Autre exemple, vous adorez les TCD (c’est mon cas…), et en avez insérés 5, 6, 10 ? Lors de la mise à jour des données, votre feuille met beaucoup de temps à vous redonner la main…

Alors comment optimiser vos fichiers et en tirer toutes les analyses souhaitées ?

Power Query pourrait-il vous y aider ?

Par où commencer pour apprendre Power Query ? Et après ?

Apprendre Power Query par l’exemple : Analyse des données COVID-19

Il est temps de prendre les rênes de Power Query pour commencer à l’apprendre. Comme indiqué plus haut, je vous propose d’utiliser les données de l’Université Johns Hopkins sur la COVID-19.

Les données sont publiées dans le repository GitHub de l’Université. Voici le lien vers le fichier CSV que je vais utiliser (pensez à faire un clic-droit puis « Enregistrer sous… »).

Option 2 : Vous pouvez directement référencer l’adresse web du fichier CSV qui est mis à jour quotidiennement. Ainsi, vous n’aurez qu’à rafraîchir les données (et disposer d’une connexion à Internet) pour avoir les données actualisées.

Étape n°1 : Importer votre fichier CSV à l’aide Power Query – À l’aide du fichier CSV

Une fois enregistrée, rendez-vous sur le ruban « Données » > « À partir d’un fichier texte/CSV » > Sélectionnez le fichier CSV depuis votre dossier > Cliquez sur Importer

Importation CSV Donnees COVID-19
Importation CSV Données COVID-19

Étape n°1 bis : Importer votre fichier CSV directement depuis sa source web

Cette option est intéressante pour avoir les données actualisées quotidiennement depuis votre fichier Excel. En effet, l’adresse du fichier ne change pas d’un jour à l’autre. Cela rend son importation web très simple et très efficace.

Petit avertissement tout de même : dans la mesure où Excel va réaliser des requêtes web, vous aurez un avertissement de sécurité à chaque ouverture du fichier. Pour vous qui avez conçu le fichier et défini la source de données, pas de soucis. En revanche, si vous envoyez le fichier à une autre personne, celle-ci pourrait (devrait !) légitimement se méfier.

Rendez-vous sur le ruban « Données » puis « À partir du web » et coller l’adresse complète du fichier CSV : https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

Importation CSV Donnees COVID-19 Web
Importation CSV Données COVID-19 Web

Étape n°1 (ter) : Se connecter au contenu web

Une étape supplémentaire pour l’option connexion au contenu web directement, l’authentification. Dans le cas présent, rien à configurer, l’accès est libre et anonyme (au sens d’authentification).

Ici, vous n’avez qu’à vérifier que c’est bien « Anonyme » qui est sélectionné à gauche puis à cliquer sur « Se connecter ».

Importation CSV Donnees COVID-19 Web - Se connecter
Importation CSV Données COVID-19 Web – Se connecter

Étape n°2 : Transformer les données dans Power Query

Cette fois vous y êtes presque ! Après cette étape, vous vous trouverez dans l’interface de Power Query et pourrez commencer à apprendre à l’utiliser !

Ici, vous allez cliquer sur « Transformer les données » (ceci est valable dans les deux scénarios : import depuis un fichier CSV ou à partir du web) :

Importation CSV Donnees COVID-19 Web - Transformer les donnees
Importation CSV Données COVID-19 Web – Transformer les données

Étape n°3 : Découvrir l’interface de Power Query

Pour commencer à apprendre Power Query, il vous sera nécessaire de comprendre la nouvelle interface qui se présente à vous.

Bien évidemment, tous les boutons et menus ont leur utilité, toutefois, il faut bien commencer par quelque part.

Bienvenue dans la cuisine des données ! L’analogie culinaire est intéressante à plusieurs titres :

  1. Les requêtes (1) : C’est l’équivalent du menu, c’est ce que vous pourrez consommer depuis Excel. A ce stade, vous n’avez qu’une seule requête, c’est celle de l’import du fichier CSV. Vous pourrez toutefois la renommer pour lui donner un nom compréhensible.
  2. Les opérations sur les colonnes et les lignes (2) ou les opérations de transformation (3) : Ce sont vos ustensiles de cuisine pour préparer vos données.
  3. Les étapes appliquées (4) : C’est votre recette. Elles seront appliquées à chaque appel de rafraîchissement des données.
Apprendre Power Query - Decouvrir interface
Apprendre Power Query – Découvrir l’interface

Étape n°4 : Avoir une idée de la transformation que vous souhaitez réaliser

Pour continuer l’analogie avec la cuisine, vous ne commencez pas à préparer vos ingrédients sans savoir quel plat vous préparez ! Ici, c’est la même chose.

Pour réussir votre premier tableau croisé dynamique, je vous avais conseillé de dessiner votre tableau de synthèse avant de manipuler Excel. Je vais ici réitérer ce conseil.

Ayez une idée de ce que vous recherchez avant de manipuler Power Query.

Dans le cas présent, l’Université Johns Hopkins place une nouvelle colonne pour chaque jour. En général, les relevés de données se présentent en ligne. D’ailleurs, Excel possède plus de lignes que de colonnes pour cette même raison.

Je vous propose donc un premier scénario d’analyse : pivoter les données de l’Université.

À ce stade, les explications à base de captures d’écran devenant trop compliquées, j’ai réaliser un enregistrement vidéo explicatif.

Et après ?

Power Query est un tremplin vers Power BI (regardez comment importer l’historique des actions du CAC 40 à l’aide d’Excel et de Power BI). Vous aurez exactement la même interface et les mêmes fonctionnalités. Car une fois que vous aurez appris à utiliser le langage M (et aussi un peu de DAX), vous pourrez faire le saut de l’exploration des données.

Tel un tremplin à plusieurs étages, vous accéderez d’Excel à Power Query puis à Power BI.

Est-ce que cette introduction et cet exemple vous ont intéressé ? Faites-le moi savoir dans les commentaires de cet article, dans les commentaires de la vidéo YouTube ou simplement par mail via la page contact !

En attendant, si vous êtes intéressé par une formation sur Power Query, je vous propose de vous pré-inscrire sur ma page formation.

Partager l'article
  • Avec plaisir pour les éclaircissements. En ce qui concerne Power BI. Sachez que la version Power BI Desktop s’utilise sur un PC et n’a pas besoin d’être installé sur un serveur. Il est gratuit et les limites de cette version sont tout à fait acceptables pour débuter.
    Les fonctions manquantes sont tout ce qui est de la collaboration, de l’envoi automatisé et d’autres capacités utiles dans une grande structure.

    Pour un usage autonome, vous avez tout à disposition : https://powerbi.microsoft.com/fr-fr/desktop/

  • Hacquart Valentin dit :

    Merci beaucoup pour ces éclaircissements, je vais essayer de regarder pour le language M, le soucis est que powerBI n’est pas disponible dans l’entreprise dans laquelle je travaille 🙂

  • Bonjour Valentin,
    Comme de nombreux outils, Power Query s’apprivoise… Microsoft a pris l’habitude dans la plupart de ses outils de proposer des fonctionnalités utilisables via des pas-à-pas simples et fonctionnels.
    Cette modalité fonctionne toujours très bien tant que rien ne change comme vous l’indiquez. Il se trouve que Power Query dispose d’un mode « Editeur avancé » et là, tu peux reprendre la main dans les opérations réalisées. Simplement, tu vas devoir apprendre un nouveau langage, le langage M.
    Ainsi, tu pourras déterminer comment rendre dynamique le nom de vos colonnes et comment réagir en cas de présence de nouvelles colonnes.

    T’expliquer tout cela dans un commentaire est une mission impossible.
    Enfin, si la finalité est toujours de faire des rapports de vos données, la question du choix d’Excel peut se poser. Tu pourrais alors te tourner vers Power BI (qui s’appuie sur la même base Power Query).
    Là encore, c’est tout une autre histoire de prise en main et d’utilisation.

  • Valentin dit :

    Bonjour Charles,
    Je trouve votre article intéressant. Ayant un besoin quotidien d’Excel, j’utilise à la fois les fonctions d’Excel et VBA. Cela fais 1 moi que j’ai essayé de me mettre sur Power Query et je trouve quelques limites, qui sont peut être dû à mon manque de connaissance.
    Par exemple, lorsque vous avez intégré vos données venant d’un fichier Excel, qu’ensuite vous rajouter une colonne par la suite, si vous voulez mettre à jour le fichier Excel, cela indiquera un message d’erreur…
    Un autre problème aussi, lorsqu’on combine des requêtes (données venant de plusieurs fichier Excel), si on rajoute une colonne à l’une des 2 requêtes, cela mettre un message d’erreur, et il faudra à la fois refaire la combinaison, et tous les TCD qui auront disparus…
    Y a t-il une astuce? (parce que les besoins clients changent constamment, et si on doit tout modifier, cela fait perdre beaucoup de temps !)
    Merci d’avance pour ta réponse 🙂

  • Merci Hamidul. Microsoft est en train de passer le potentiel d’Excel au niveau supérieur. En particulier avec un abonnement à Excel 365. Restez connecté au blog pour recevoir les prochains articles.

  • Merci Nathalie. Ça se voit que j’aime cuisiner ? :p

  • Je suis arrivée un peu par hasard sur ton article. Mais bien que je ne sois pas vraiment dans le domaine des tableurs je l’ai trouvé très bien écrit et très intéressant. Je découvre POWER QUERY qui semble vaste et hyper précis. Merci ! Par ailleurs j’ai aimé tes différentes comparaisons avec la cuisine 😉. Bonne journée ! Nathalie

  • Merci Charles pour cet article extrêmement complet !
    Je pense que de maîtriser et de pouvoir exploiter les données est une compétence clé. Et effectivement pourquoi ne pas se servir de cet outil que nous avons tous sur un PC : Excel.

  • Moi qui trouvait d’jà Excel hyper puissant, je suis sur les fesses en lisant qu’on peut encore dépasser cette dimension !! Merci pour cette découverte. PS : j’apprécie beaucoup la façon dont vous illustrez vos explications

  • >