Microsoft Power Query !
Cet article présente l’utilisation possible de Power Query sur un exemple volontairement simple pour vous permettre de comprendre les possibilités ouvertes par cet éditeur de requêtes. Nous vous invitons à lire l’exemple qui suit, puis à vous familiariser avec cet utilitaire en téléchargeant le fichier d’exemple et en suivant la vidéo en bas de cet article.
En deux phrases, qu’est-ce Power Query ?
C’est un éditeur de requêtes puissant intégré à Microsoft Excel (2010, 2013 et 2016) en tant que complément, à installer où à activer en fonction de votre version (télécharger ici votre version). Il permet de se connecter à différentes sources de données (Excel, CSV, Access, SQL, Oracle…), de les transformer, de les croiser et de les charger directement dans un tableau Excel.
A qui s’adresse Power Query ?
Cet utilitaire de Microsoft s’adresse à toutes les personnes manipulant des données sur Excel et souhaitant simplifier leur travail dans les étapes de récupération et de préparation des données. Par exemple, vous avez besoin de consolider des fichiers Excel, texte, ou encore de vous connecter à une base de données pour réaliser des analyses ou des tableaux de bords, Power Query va vous aider et vous faire gagner du temps !
Pour mieux comprendre, voici un exemple
Contexte de l’exemple
Réalisation d’une analyse financière à partir de l’extraction des balances comptables des différentes entités d’une société. Le logiciel comptable permet d’extraire un fichier Excel avec autant d’onglets contenant les balances que d’entités. Ainsi le fichier comporte 39 onglets et l’objectif sera de consolider l’ensemble en 1 base unique.
Objectif
Consolider rapidement les 39 entités (onglets du fichier) pour ensuite produire une analyse du compte de résultat.
Etape 1 : Récupérer les balances
Une fois les balances extraites du logiciel de comptabilité, nous pouvons commencer à préparer notre récupération en ouvrant un nouveau classeur Excel.
Depuis le ruban d’Excel, accéder à l’onglet « Power Query » :
Si vous avez besoin d’aide pour activer le menu Power Query, regardez ici.
Ensuite cliquer sur le menu « A partir d’un fichier \ A partir d’Excel », sélectionner le fichier à récupérer puis cliquer sur « OK ».
L’interface suivante liste tous les onglets contenus dans le classeur. Cliquer sur le répertoire comme dans l’exemple ci-dessous pour récupérer tous les onglets du classeur puis cliquer sur “Modifier” .
Le fait de cliquer sur “Modifier” permet d’expliquer à Power Query comment intégrer les onglets et quels retraitements (calcul, suppression de lignes/colonnes, format…) réaliser avant l’intégration dans Excel.
L’interface d’édition de Power Query s’ouvre et affiche la fenêtre suivante :
Ici, l’interface liste les différents onglets qui sont interprétés comme des “tables de données” par Power Query et seront donc utilisés comme source d’information pour les requêtes.
Cliquer sur les flèches “Gauche/Droite” de la colonne “Data” et demander à l’application de “Développer” les données puis, cliquer sur “Ok”.
Automatiquement, l’application va charger tous les onglets du classeur en un seul est unique tableau ! Il reste à expliquer les retraitements que l’on souhaite appliquer aux données.
Etape 2 : Définir la première ligne comme entête
Dans notre exemple, la première ligne des onglets contient des entêtes de colonnes. Nous allons donc demander à Power Query de les utiliser comme entête via le menu suivant :
Etape 3 : Ajouter un calcul en utilisant deux colonnes
Ensuite, pour réaliser notre analyse de balance, nous avons besoin de calculer le solde de la balance (Crédit – Débit). Nous ajoutons une colonne qui va directement le calculer. Pour cela, aller dans le menu “Ajouter une colonne” et cliquer sur “Colonne personnalisée”, ensuite vous êtes guidé par l’interface pour rédiger la formule.
Dans notre cas [SOLDECREDIT] – [SOLDEDEBIT] :
Etape 4 : Supprimer des colonnes
Pour supprimer des colonnes, rien de plus simple, sélectionner les colonnes…
… puis cliquer sur le bouton “Supprimer les colonnes” comme dans Excel !
Une fois les calculs intermédiaires et les retraitements réalisés, il est possible de supprimer les colonnes (intermédiaires) qui ont servies sans perturber le résultat final ! Ceci permet d’alléger la lecture du tableau qui sera récupéré en conservant uniquement les colonnes nécessaires à l’utilisateur.
Etape 5 : filtrer les lignes
Ici nous filtrons les lignes pour ne prendre que les comptes commençant par “6” et “7”, ces derniers correspondant aux comptes du compte de résultat.
Comme dans Excel, cliquer sur la flèche à côté de la colonne “Compte” et indiquer votre filtre :
A noter.
Au fur et à mesure que l’on réalise des opérations, le volet droit de l’application s’enrichi des étapes à appliquer pour créer la requête. Il est possible à tout moment de supprimer une étape, de la modifier et de changer son ordre.
Si la source de données change. Il est possible en un simple clic, d’appliquer les mêmes étapes pour actualiser vos données !
C’est ici une des forces de Power Query.
Une fois que vos retraitements, calculs… sont terminés, cliquer sur “Fermer et charger”
Dans le classeur Excel, un onglet s’est automatiquement ajouté avec la création d’un tableau qui affiche uniquement les éléments de la requête, issus des 39 onglets initiaux.
En conclusion, ceci est un exemple rapide qui permet de voir simplement le potentiel de cet outil intégré par Microsoft à Excel et qu’on retrouve aussi dans Microsoft Power BI.
vidéo explicative de l’exemple
Rappel du contexte de l’exemple
Réalisation d’une analyse financière à partir de l’extraction des balances comptables des différentes entités d’une société. Le logiciel comptable permet d’extraire un fichier Excel avec autant d’onglets contenant les balances que d’entités. Ainsi le fichier comporte 39 onglets et l’objectif sera de consolider l’ensemble en 1 base unique.
Rappel de l’objectif
Consolider rapidement les 39 entités (onglets du fichier) pour ensuite produire une analyse du compte de résultat.