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 !

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.

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.

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.

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 :

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

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 :

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

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).

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

É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

É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 ».

É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) :

É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.

É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.