Intro : Power Query est un outil moderne et simple d’Excel et de Power BI, mais peu connu de beaucoup de gens (notamment sur Excel). Il permet de faire puis d’automatiser l’extraction et la transformation de vos données, avec une approche no-code très agréable à prendre en mains et pourtant très rapidement puissante. Voilà comment bien commencer avec cet outil.‍

‍

Pourtant, même si Power Query est devenu aujourd’hui l’un des outils les plus rentables d’Excel, peu de gens ont déjà pris le temps de s’y mettre. Pour beaucoup, les pratiques habituelles chronophages mais confortables sont difficiles à remettre en question, face à l’urgence professionnelle du quotidien.

‍

Le but de cet article est de vous donner 5 principes clés pour bien débuter sur Power Query, et de moderniser vos pratiques d’Excel et de Power BI.

‍

#1 | Power Query, qu’est-ce que c’est ?

‍

Power Query est un outil disponible à la fois dans Excel et dans Power BI, qui permet de simplifier et d’automatiser l’extraction et la transformation de données depuis différentes sources. Ces sources peuvent être des classeurs Excel, des documents txt ou csv, mais aussi des pages web (y compris des documents Google Sheets ou Google Docs) ou des API.

‍

Extraire, transformer et charger ses données

‍

C’est un outil de type ETL (Extract, Transform, Load), donc il n’a pour objectif que l’extraction et la transformation des données. Power Query n’est pas fait pour gérer les calculs analytiques, ni les visuels, ni les interactions, ni l’export vers d’autres sources.

‍

Contrairement aux fonctions ou à VBA, qui existent depuis très longtemps dans Excel et qui combinent de nombreuses utilités, Power Query a un rôle très précis et très bien défini. C’est donc un outil spécialisé, ce qui lui permet d’être excellent sur un périmètre réduit, plutôt que d’être moyen sur un périmètre très vaste.

‍

Le seul et unique objectif de Power Query dans un classeur Excel (ou un document Power BI) est celui-ci : extraire et transformer les données avant (afin) de pouvoir les analyser.

‍

Sans codes ni formules

‍

L’un des avantages majeurs de Power Query (par rapport aux fonctions ou au langage VBA et aux macros) c’est que c’est un outil no-code, idéal pour les débutants comme pour les experts. Power Query permet de faire de la programmation visuelle dans un éditeur simple et convivial, sans investissement initial important.

‍

Le principe est celui-ci :

• L’utilisateur clique simplement sur une action simple à réaliser sur ses données
• Power Query réalise l’action et affiche le résultat à l’utilisateur
• Cette action est enregistrée en tant que code dans un programme (le plus souvent nommé « requête »)
• Plus l’utilisateur ajoute d’actions successives, plus le programme est final est complexe, même si les actions prises séparément sont simples
• Quand toutes les actions nécessaires ont été effectuées, l’utilisateur ferme Power Query et le programme est enregistré et peut-être réutilisé en boucle pour effectuer la même série d’actions sur de nouvelles données

‍

Pas de code à saisir manuellement. Pas d’apprentissage théorique chronophage. Pas de documentation interminable. L’utilisateur fait. Power Query enregistre dans un programme. L’utilisateur peut relancer le programme en un clic pour l’activer à nouveau.

‍

Un outil moderne et puissant

‍

Power Query est donc un outil spécialisé (ETL) et no-code, ce qui pourrait le faire paraitre limité face à d’autres outils moins simplifiés mais plus polyvalents.

‍

Par exemple :

• Les fonctions dans Excel par exemple (SOMME, SI, RECHERCHEX…) sont en réalité un langage de programmation - avec certaines spécificités - mais pouvant effectuer beaucoup d’opérations.
• Le langage VBA (et les macros) elles aussi sont beaucoup plus polyvalents que VBA.
• Le langage Python, aujourd’hui disponible directement dans Excel, est lui aussi un langage ultra polyvalent permettant de nombreuses opérations très différentes

‍

Mais c’est bien la spécialisation de Power Query qui en fait un outil à la fois moderne est puissant.

‍

Contrairement aux trois exemples cités précédemment, Power Query se focalise sur un nombre réduits d’opérations possibles, seulement celles liées à l’extraction et à la transformation des données. Ce qui lui permet d’avoir moins de fonctionnalités, donc de mieux les organisées, dans un éditeur plus convivial et très simple à apprendre, y compris pour les débutants.

‍

Et Power Query s’inscrit dans un ensemble d’outils complémentaires, au sein d’Excel comme de Power BI. Ce sont ces autres outils qui vont venir permettre d’effectuer les autres opérations utilises à la complétion ou à l’analyse de données. Power Query est donc moins un outil-à-tout-faire qu’un outil spécialisé complété par d’autres outils, eux aussi spécialisés, comme les TCD, Power Pivot et les graphiques. Cette manière de combiner différents outils spécialisés complémentaires, c’est ça l’approche moderne d’Excel et de Power BI aujourd’hui.

‍

#2 | Pourquoi (et quand) utiliser Power Query

‍

Alors, puisque Power Query est spécialisé dans l’extraction et la transformation des données, qui se combine avec d’autres outils complémentaires (dans Excel et dans Power BI), il est utile de bien comprendre pourquoi et comment l’utiliser.

‍

D’abord le pourquoi.

‍

Pourquoi utiliser Power Query ?

‍

Depuis quelques années, en France comme à l’étranger, dans toutes les entreprises et dans tous les métiers :

• Les volumes de données ont largement augmenté
• Les données sont maintenant « facilement » accessibles dans des outils spécialisés (donc fini la saisie et les copier-coller)
• Et les décisions doivent être prises plus rapidement dans un environnement où tout va plus vite

‍

Toutes ces contraintes font que beaucoup de gens dans beaucoup de métiers s’épuisent à produire les informations dont ils ont pourtant besoin, au rythme où ils en ont besoin. Les rapports annuels ou trimestriels, qui nécessitent une semaine ou même un mois pour mettre les chiffres à jour, ne correspondent plus aux impératifs actuels.

‍

L’alternative ce n’est pas de saisir plus vite. De copier-coller plus vite. D’utiliser plus de raccourcis clavier ou d’utiliser l’IA. La seule alternative efficace c’est d’automatiser les opérations répétitives et chronophages, comme la mise à jour des données, pour concentrer tous les efforts sur l’analyse et la prise de décisions.

‍

Power Query est donc un outil très lié aux nouveaux enjeux des individus et des organisations aujourd’hui : décider mieux, plus vite, en se débarrassant grâces aux nouvelles technologies de ce qui n’a pas de « valeur humaine ajoutée » et qui peut être automatisé.

‍

Quand utiliser Power Query ?

‍

D’abord, dès que c’est logique et possible.

‍

C’est logique quand il n’y a pas de « valeur humaine ajoutée » dans certains processus. Ce qui donc dans votre quotidien professionnel requiert moins votre expertise que simplement votre temps. Si n’importe qui, même débutant (à condition d’être concentré et rigoureux), produirait les mêmes résultats avec les mêmes informations… : c’est cela qu’il est logique d’automatiser avec Power Query.

‍

C’est possible d’automatiser avec Power Query quand il y a des données qui nécessitent d’être extraites et/ou transformer pour les analyser, depuis des sources numériques, et que ces données sont des valeurs alphanumériques et factuelles. Power Query n’est pas fait pour analyser les images ou les verbatims (un avis sur une formation en texte libre par exemple).

‍

Mais dès que les données existent dans des sources alphanumériques normales : Power Query.

‍

Automatiser, c’est donc remplacer du temps humain sans valeur ajoutée par du temps machine sans valeur ajoutée, en divisant le temps d’exécution par 10 000 (ce nombre est fictif, mais c’est l’idée).

‍

Prenez le temps, pensez aux manipulations que vous faites de manière récurrente à chaque fois que vous voulez mettre à jour l’un de vos outils de pilotage.

‍

Par exemple :

• Extraire les données du mois dans un nouveau fichier CSV
• Copier-coller les données dans Excel
• Fractionner les colonnes
• Supprimer les colonnes inutiles
• Modifier l’ordre des colonnes
• Mettre certaines valeurs en majuscules
• Supprimer certains caractères étranges

‍

Toutes ces actions, rapides avec l’habitude, vous prennent du temps chaque mois. Et si elles ne vous prenaient pas de temps, vous actualiseriez vos outils tous les jours et pas tous les mois.

‍

Et bien c’est exactement ce genre de petites actions chronophages, récurrentes, sans valeur ajoutée, que Power Query va pour permettre d’automatiser pour les reproduire très simplement en un clic. Autant de fois que vous voulez.

‍

Power Query devient alors la première étape d’une combinaison d’outils (que ce soit dans Excel ou dans Power BI) dont le but est de produire des indicateurs à analyser pour prendre une ou plusieurs décisions. Sur Excel, la combinaison standard c’est : Power Query > Power Pivot > TCD > Graphiques & autres visualisations.

‍

#3 | Les concepts clés de Power Query

‍

Power Query n’est pas aussi permissif que les feuilles, les cellules et les fonctions dans Excel. Il a ses propres objets, qui ont eux-mêmes des propriétés, qui permettent de faire certaines actions spécifique. Voici quelques éléments à bien avoir à l’esprit pour bien débuter sur Power Query.

‍

Les outils "ETL"

‍

C’est le type d’outils auquel appartient Power Query. Ces outils sont spécialisés dans l’extraction, la transformation et le chargement des données.

‍

Les requêtes

‍

C’est l’élément principal de Power Query. Toute action automatisée est intégrée à un programme, nommé « requête » qui va appliquer chaque étape dans l’ordre. Lorsqu’un utilisateur créé une requête, il ajoute successivement un certain nombre d’étapes à la suite l’une de l’autre. Cette succession, c’est un programme. Et c’est ce programme qui peut être actualisé (donc relancé) en un clic quand l’utilisateur le décide ou en fonction de certains déclencheurs.

‍

Les paramètres

‍

C’est un élément essentiel qui se combine aux requêtes pour variabiliser certains éléments comme des URL, des identifiants ou des informations individuelles. Lorsque Power Query est utilisé dans Excel, ces paramètres sont moins importants que dans Power BI. Mais ils restent un concept important de Power Query.

‍

Les tables, colonnes et éléments

‍

Il n’y a pas de feuille dans Power Query. Ni de ligne. Ces objets existent dans Excel, et c’est comme ça que beaucoup d’utilisateurs pensent que les données sont et doivent être stockées. Mais c’est une approche obsolète de la data.

‍

Mais les 3 objets principaux de Power Query sont les suivants :

• La table : elle contient des colonnes nommées
• La colonne : elle contient des éléments disposés à différents index (positions)
• L’élément : il contient soit une valeur, soit un objet entier (une liste, un record, voire même une table)

‍

C’est important de comprendre ce qu’implique cette hiérarchie : il n’est pas possible de sélectionner une ligne, car ce n’est pas un objet au sens de Power Query. Il n’est pas possible non plus de sélectionner une « cellule » comme dans Excel et d’y saisir ou modifier une valeur, car ne se sont pas des objets « powerquerisable ».

‍

Les types de données

‍

Chaque colonne doit avoir un type spécifique de données. Une colonne, à la fin d’une requête, ne doit pas contenir à la fois des dates et des textes, ni des dates et des nombres. Certains types sont assez bien connus de tout le monde, mais d’autres, comme les durées, les binaires, les datetimezone et records doivent être appropriés dans leurs spécificités sur Power Query.

‍

Les actions (et les fonctions)

‍

L’objectif d’une requête, c’est de programmer des actions à faire sur des données, en fonction des objets précédemment cités (tables, colonnes, valeurs…).

‍

Par exemple :

• Supprimer une colonne d’une table en fonction de son nom
• Remplacer les accents des valeurs d’une colonne
• Transposer les lignes et les colonnes d’une table

‍

Il est important de comprendre que ces actions, créées en no-code avec de simples clics, sont directement enregistrées par Power Query en tant que fonctions et formules dans le code propre à Power Query : le langage M.

‍

A chaque fois qu’un utilisateur effectue une action sur Power Query, c’est une ou plusieurs lignes de code en langage M qui sont créées et ajoutées à la requête.

‍

Les tables (bis), les records, listes et valeurs

‍

Dans Excel, une cellule ne peut contenir qu’une valeur alphanumérique (cette information est fausse au niveau expert, notamment avec Python, mais vraie dans 99,9% des cas).

‍

Mais dans Power Query, cette contrainte n’existe plus du tout. Une colonne d’une table peut contenir dans chacun de ses éléments une autre table (donc plusieurs colonnes complètes).

‍

Bien qu’il en existe d’autres, les 4 principaux types de données en plusieurs dimensions pouvant être contenus dans une table sont :

• D’autres tables : plusieurs colonnes nommées de plusieurs enregistrements
• Des records : plusieurs listes de valeurs regroupées par titre
• Des listes : plusieurs valeurs, sans titre
• Des valeurs simples : une seule dimension, comme le mot « bonjour »

‍

Les fonctions personnalisées

‍

C’est l’un des éléments assez difficile à appréhender de Power Query pour commencer : certaines tables peuvent être transformées en fonctions personnalisées afin de les utiliser plusieurs fois facilement.

‍

Pour être vraiment utiles, ces fonctions personnalisées peuvent variabiliser certains arguments. C’est assez technique. D’autres articles aborderont spécifiquement les fonctions personnalisées.

‍

Le langage M

‍

C’est le langage derrière Power Query, qui permet de d’encrypter dans des requêtes les actions humaines des utilisateurs faites par de simples clics. Dans beaucoup de cas - au moins pour commencer - il ne faut pas y prêter attention. Mais… à un moment où un autre, comprendre le langage M permettra de dépasser les limitations de l’utilisation no-code de Power Query.

‍

#4 | Les pratiques essentielles

‍

Faisons simple. Power Query est un outil intuitif, no-code, presque « auto-pédagogique ». Pour l’apprendre, rien de mieux que de commencer par le tester sur de mini projets de plus en plus ambitieux.

‍

10 conseils pour ses premières requêtes

‍

Voici cependant quelques conseils pour bien commencer ses premières requêtes :

• Bien choisir ses sources initiales
• Standardiser les emplacements (chemins d’accès, URL) des sources pour qu’ils ne changent pas
• Supprimer les colonnes inutiles et filtrer les lignes inutiles
• Renommer ses étapes avec des noms précis (et commenter les étapes complexes)
• Afficher la qualité et la distribution des colonnes (seulement sur Windows)
• Renommer et typer les colonnes
• Pivoter/dépivoter les colonnes afin d’obtenir une structure data standard (règle du 1 pour 1)
• Ajouter des colonnes ou fusionner des requêtes pour enrichir les données des colonnes initiales
• Charger les données dans le modèle de données (sur Windows, pour les habitués de Power Pivot)

‍

3 vidéos pour bien commencer

‍

Voici quelques vidéos pour bien commencer :

• Vidéo 1 : bientôt disponible
• Vidéo 2 : bientôt disponible
• Vidéo 3 : bientôt disponible

‍

#5 | Power Query & le langage M

‍

Power Query est un outil no-code entièrement lié à un langage de programmation : le langage M.

‍

Bien qu’il ne soit pas nécessaire de se focaliser sur le langage M au début de son apprentissage de Power Query, il ne faut pas non plus à l’inverse fuir indéfiniment ce langage. Rapidement, il est important d’afficher la barre de formule pour voir le code généré par ses propres actions et comprendre la syntaxe du langage M.

‍

5 syntaxes à comprendre

‍

Voici 5 syntaxes « simples » qui vous permettront de comprendre les bases du langage M :

• Mettre les prénoms de la colonne Prénom en nom propre (première lettre de chaque mot en majuscule) := Table.TransformColumns(#"Type modifié",{{"Prénom", Text.Proper, type text}})
• Mettre les noms de la colonne Nom en majuscules := Table.TransformColumns(#"Type modifié",{{"Nom", Text.Upper, type text}})
• Extraire les années de la colonne Date := Table.TransformColumns(#"Type modifié",{{"Date", Date.Year, Int64.Type}})
• Déterminer les derniers jours du mois de la colonne Date := Table.TransformColumns(#"Date analysée",{{"Fusionné", Date.EndOfMonth, type date}})
• Fusionner les textes des colonnes Mois et Année en une seule colonne, nommée Date := Table.CombineColumns(#"Majuscule à chaque mot",{"Mois", "Année"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Date")

‍

6 éléments importants du langage M

‍

Et quelques conseils supplémentaires pour comprendre la syntaxe du langage M :

• Les instructions let … in permettent de créer des variables et de sélectionner un résultat parmi ces variables
• Les instructions each _ permettent de transformer chaque élément d’une colonne de manière individuelle
• Les symboles permettent à la fois de sélectionner des colonnes et de créer des records
• Les { } symboles permettent à la fois de sélectionner des éléments d’un objet en fonction de son index et de créer des listes
• Les instructions if … then … else permettent de tester une condition et d’attribuer des résultats en fonction des résultats de la condition
• Les instructions try … otherwise permettent de tester un résultat et préparer un résultat alternatif si le résultat principal est une erreur

‍

Power Query, l’outil ETL de Microsoft, est un outil intuitif et puissant, spécialisé dans l’extraction et la transformation de données afin d’automatiser les opérations répétitives et chronophages. C’est un atout majeur d’une utilisation moderne d’Excel, comme Power Pivot, les TCD et les graphiques et autres visualisations.

‍

Pour bien débuter avec cet outil, il est important de comprendre son rôle dans l’écosystème Microsoft d’Excel et de Power BI, mais aussi ce qu’il implique et ce qu’il offre comme opportunités d’une manière plus large, en actualisant les données directement depuis les sources initiales, sans temps et sans erreur.

‍

L’ensemble des explications, des idées et des ressources de cet article ont pour but de simplifier les débuts de chacun avec Power Query. Rapidement, de nouveaux problèmes viendront, en même temps que de nouvelles solutions. Dans un cycle perpétuel vers l’excellence.

‍

Si vous souhaitez être accompagné dans cette découverte par l’un de nos formateurs/experts, afin d’accélérer votre apprentissage, quel que soit votre niveau, jetez un coup d’œil à nos formations individuelles sur-mesure, adaptables à votre rythme et à vos objectifs.

‍

‍