« Mon fichier Excel fait plusieurs millions de lignes, je ne pourrai jamais m’en servir comme ça. Il me faut un outil spécialisé qui supporte une grosse quantité de data ! »

J’ai récemment entendu une phrase qui ressemblait à ça. Alors, faut-il jeter Excel à la poubelle ? Et si vous évitiez de passer du temps, de l’argent et des efforts pour trouver un nouvel outil et apprendre à l’utiliser ? Excel inclut depuis quelques années déjà trois outils qui permettent de pousser le célèbre tableur encore plus loin : Power Query, Power Pivot et Power View.

Après l’article de Loïc Leprat sur les 3 bonnes pratiques Excel qui lui ont changé la vie, focus aujourd’hui sur Power Query.

A l’ère de la data, peut-on encore utiliser Excel ?

Si vous avez déjà essayé d’ouvrir un fichier avec plusieurs millions de ligne sur Excel, vous avez peut-être été bloqué par la limite de l’outil à 1 048 576 de lignes. Cela ne doit pas nécessairement constituer un obstacle insurmontable grâce au complément Power Query. Il est disponible par défaut sur Excel depuis la version 2016.

Power Query, c’est quoi ?

De quoi s’agit-il ? D’après Microsoft, « Power Query est un moteur de transformation et de préparation des données. [Il] est fourni avec une interface graphique pour obtenir des données à partir de sources et un éditeur de Power Query pour appliquer des transformations. » (1)

Quelques avantages et inconvénients selon moi, avant d’aller plus loin :

Avantages

• Pas besoin de charger toutes les data pour travailler
• Permet de mener une démarche reproductible et auditable
• Pas besoin d’outil spécialisé supplémentaire
• Permet de bénéficier de tous les avantages d’Excel (manipulation, exploration facile, formules etc.)
• Import de data depuis internet (requêtes d’API ou simple page web) ou divers formats
• En cas de besoin, une communauté en ligne avec des problèmes similaires au votre
• Compatible avec Power BI, Azure Data Lake, etc.

Inconvénients

• Limité quand même par la puissance de calcul et la mémoire de son ordinateur
• Pas disponible en ligne sur Office 365 pour le moment
• Quelques fonctionnalités ne sont disponibles qu’en éditant un peu de code

Mise en pratique

Alors comment faire concrètement ? Identifiez un fichier Excel que vous connaissez pour une exploration avec Power Query.

Créer une connexion à son fichier

Créer une connexion

Depuis l’onglet « Données », allez dans « Obtenir des données » > « A partir d’un fichier » et choisissez le bon format. Par exemple « A partir d’un classeur » pour un fichier xslx.

Naviguer vers le fichier que vous souhaitez explorer, sélectionnez-le et validez. Choisissez ensuite le bon onglet et le bon tableau avant de cliquer sur « Transformer les données ».

A gauche : fichier, onglets, tableaux ; à droite : aperçu du tableau sélectionné

Une fois que vous cliquez sur « Transformer les données », vous ouvrez l’interface graphique de Power Query qui permet de manipuler les data sans les ouvrir pour l’instant, quelle que soit leur taille ! Je prends l’exemple des résultats d’une enquête de satisfaction.

Interface de Power Query

Au milieu, vous trouvez les colonnes et un échantillon des 1000 premières valeurs. A droite, les étapes de chargement et de transformation de vos data. Le ruban en haut donne les actions les plus courantes. Vous pouvez également interagir avec les en-têtes de colonnes ou les cellules.

Les manipulations de base

Les fonctions les plus communément utilisées de Power Query entrent dans 5 catégories.

• Agir sur les colonnes : conserver, supprimer, fractionner, ajouter. Il suffit de sélectionner les colonnes et de faire un clic droit dessus
  

  Supprimer une colonne qui ne m’intéresse pas

• Agir sur les lignes : filtrer, trier. Comme sur Excel, cliquer sur la petite flèche à droite de l’en-tête de colonne. C’est notamment utile si vous avez devant vous un fichier Excel qui a trop de lignes. Vous n’avez peut-être pas besoin d’afficher toutes ces lignes en même temps, mais seulement celles qui vous intéressent en filtrant sur la bonne valeur.
  

  Filtrer des lignes pour conserver les valeurs qui m’intéressent et réduire le nombre de lignes

• Modifier les valeurs : remplacer, remplir, supprimer. Cliquer sur un en-tête de colonne, aller dans le ruban et choisir « Transformer » > « Remplacer les valeurs »
  

  Remplacer des valeurs pour transformer des textes en nombres

• Faire des calculs : statistiques, consolidations. Choisir une colonne de nombres, aller dans le ruban et choisir « Transformer » > « Statistiques »
  

  Statistiques permet de décrire ses data sans passer par Excel

• Enregistrer et exporter son travail. Aller dans « Accueil » puis « Fermer et charger » pour enregistrer sa requête. Power Query va alors appliquer toutes les transformations que vous avez faites et charger les data dans un onglet Excel.
  

  Enregistrer son travail pour le retrouver dans Excel

• Rafraîchir les données. Si le fichier source a changé, pas de panique ! Tant que les colonnes sont les mêmes, vous pouvez cliquer sur le tableau que vous avez créé dans Excel, dans « Données » > « Actualiser tout » mettra à jour le tableau !
  

  Rafraîchir les données permet d’éviter de refaire le travail

Conclusion

Je vous ai présenté les fonctionnalités les plus utilisées de Power Query. Essayez-le avec des data que vous connaissez, et pensez-y si vous faites face à un fichier Excel très volumineux et que vous ne savez pas par quel bout le prendre.

Nous sommes loin d’avoir fait le tour des possibilités offertes par Power Query : croisement de data, dépivoter les colonnes, requêtes d’API, chargement de txt ou depuis le web etc. A vous d’explorer l’outil et ses compagnons : Power Pivot et Power View. J’en parlerai dans un article à venir.

(1) : https://docs.microsoft.com/fr-fr/power-query/power-query-what-is-power-query