Requête de puissance: le Guide du Débutant Définitif 2021

Bonjour, bienvenue dans le guide du débutant pour la requête de puissance par UpSlide.
Vous êtes-vous déjà demandé ce qu’est une requête de puissance? Comment ça marche ? Si oui, vous êtes au bon endroit!
Pauline, notre experte en Power Query de Finance 3.1, nous a donné tous ses meilleurs trucs et conseils pour écrire cet article.
Ici, vous apprendrez les bases du logiciel et pourrez même vous entraîner un peu! Prêts?

Si vous n’avez pas le temps de le lire maintenant, téléchargez la version PDF et profitez-en plus tard!

Qu’est-ce que la requête Power ? Quand dois-je l’utiliser?

Power Query est un complément Excel qui permet à ses utilisateurs d’importer, de nettoyer et de consolider des données. Vous pouvez utiliser Power Query lorsque:

• Traitement de grandes quantités de données (où Excel est limité à un million de lignes)
• Importation de données de différentes sources (par exemple Excel, CSV, SharePoint, SQL et Salesforce)
• Manipulation, nettoyage et combinaison de plusieurs tables, fichiers ou même dossiers!

Power Query est un outil convivial et puissant, même pour les débutants, car vous n’avez pas besoin d’expérience en codage pour commencer. De plus, cela ne change pas la source de données d’origine. Le résultat est comme un instantané de données remodelées apportées à Excel. Qu’y a-t-il de si génial à ce sujet? Eh bien, puisque les données source ne sont pas directement importées, cela n’alourdit pas votre fichier Excel (c’est-à-dire qu’il ne ralentit pas votre ordinateur!).

Pour résumer, Power Query est un outil d’analyse de données rendant les processus ETL (Extract-Transform-Load) à la fois plus simples et efficaces.

Quelle est la différence entre la requête de puissance et le Pivot de puissance?

Power Pivot est souvent associé à Power Query car il s’agit d’un complément Microsoft Office qui facilite l’analyse des données. Fait intéressant, cependant, ces deux outils peuvent fonctionner en tandem l’un avec l’autre! Power Pivot est simplement la prochaine étape de la transformation des données: il permet aux utilisateurs de créer des modèles de données à partir de tables préalablement nettoyées par Power Query.

Bien que similaire, Power Pivot est davantage utilisé pour l’analyse de données, les calculs liés au temps et la génération de liens. Simplement dit:

• Power Query vous permet de consolider et de retraiter automatiquement des fichiers (c’est-à-dire la standardisation des processus de reporting ou la consolidation de plusieurs fichiers avec le même format).
• Power Pivot vous permet d’analyser des bases de données de plusieurs millions de lignes en extrayant des informations agrégées selon les règles définies par votre entreprise.

Quelle est la différence entre Power Query et Power BI ?

La technologie de requête sous-jacente de Power Query est également incluse dans Power BI.

L’interface du logiciel de bureau Power BI vous permet de bénéficier des trois technologies (Power Query, Power Pivot et Power View). Avec Power BI, vous pouvez effectuer toutes les étapes de la chaîne d’analyse des données, de l’extraction des données à la création de visuels sur un tableau de bord interactif que vous pouvez partager et analyser.

> Lisez notre article sur la façon d’exporter Power BI vers Excel

Pourquoi Power Query est-il une excellente alternative à Excel VBA?

Power Query peut facilement remplacer VBA (Visual Basic pour les applications) car il vous permet de:

• Traitez vos tableaux simplement en cliquant sur des boutons. Aucune compétence de codage nécessaire!
• Visualisez vos opérations étape par étape sans exécuter une seule macro.
• Modifiez facilement l’ordre des étapes des différentes étapes par glisser-déposer! En outre, vous pouvez ajouter et supprimer des étapes inutiles par un simple clic.

( voir la partie 2 – Section3: Comment démarrer avec Power Query?)

Cependant, Power Query ne peut pas remplacer VBA lorsqu’il s’agit d’interagir avec l’interface d’Excel, d’automatiser les processus, de générer de nouveaux fichiers, d’envoyer des e-mails, etc.

Microsoft Power Query est-il gratuit ? Existe-t-il une requête Power pour Mac ?

OUI. Power Query est une extension gratuite d’Excel. C’est un autre avantage principal du complément.
(Lisez la question suivante pour savoir comment l’obtenir sur votre bureau Excel)

Power Query pour Mac? OUI, grande nouvelle: l’éditeur de requêtes est désormais compatible avec Excel pour Mac! Mais l’intégration est assez limitée pour le moment car vous ne pouvez modifier que le chemin du fichier et actualiser les requêtes existantes.

Partie 2: Commencez avec Power Query!

Comment télécharger la requête Power?

Power Query est disponible pour Excel depuis 2010.

Voici comment ajouter une requête de puissance en fonction de votre version Excel*:

• Bonne nouvelle, vous n’avez pas besoin de télécharger Power Query pour Excel 2019, 2016 ou dans Office 365. Vous pouvez le trouver dans le ruban « Données » sous le bouton « Get& Transform ».
• Pour Excel 2013 et 2010, vous devez télécharger le complément Power Query. Il apparaîtra dans un nouvel onglet dédié nommé « Power Query » (dans Excel 2010, vous devez avoir une licence Professional Plus avec Software Assurance).

* Voici comment vérifier votre version d’Excel:

Allez dans le fichier > Compte > À propos d’Excel. Ensuite, une fenêtre apparaîtra. Lisez les deux premiers chiffres entre parenthèses. Dans l’exemple ci-dessous, la version Excel est 2016.

Pour en savoir plus, voici un autre article détaillé sur la façon d’installer et d’activer Power Query dans Excel.

Comment activer la requête Power dans Excel?

Selon votre version d’Excel (voir la question ci-dessus: Où se trouve la requête Power dans Excel?) il vous suffit de cliquer sur le bouton nommé « Obtenir des données » dans la section « Obtenir & Transformation » de l’onglet « Données » pour Excel 2016 et au-delà. Pour les versions d’Excel antérieures à 2013 incluses, accédez simplement au ruban Power Query.

Veuillez noter que lorsque vous entrez dans l’interface Power Query, vous ne pourrez plus interagir avec Excel. Si vous devez revenir à Excel, vous devrez fermer l’éditeur Power Query.

Comment fonctionne la requête Power ?

Dans Excel, vous n’avez accès aux fonctionnalités suivantes que lorsque vous cliquez sur « Requêtes & Connexions »:

Ruban de données: pour rechercher des sources de données (ruban « Power Query » pour Excel 2010 et 2013).Volet Connexions

Requêtes & : pour avoir un aperçu rapide de toutes les connexions (requêtes) que vous avez effectuées.

Une fois que vous avez lancé Power Query, vous atterrissez sur l’éditeur Power Query qui est composé de 4 éléments distincts:

Ruban principal: vous permet de créer des requêtes à partir de zéro grâce à ses boutons (si vous connaissez le langage M, vous pouvez également accéder à l’Éditeur avancé pour les modifier manuellement).

Requêtes: pour garder une trace de toutes les connexions aux sources de données et aux flux.

Aperçu de la table de données : pour visualiser le résultat de vos données après une étape spécifique.

Paramètres de requête (Étapes appliquées) : pour parcourir toutes les étapes que vous avez effectuées. Power Query enregistre automatiquement toutes les transformations effectuées.

• Dans la section 4, vous apprendrez comment Power Query fonctionne essentiellement en transformant une table de données.
• Dans la section 5, cela devient encore plus intéressant car vous découvrirez comment procéder les données de diverses sources et fusionner les requêtes.

Comment démarrer avec Power Query?

Imaginez la situation suivante:

Vous êtes le contrôleur financier d’une entreprise de technologie vendant des ordinateurs et des smartphones. Actuellement, vous opérez dans 3 pays : la France, le Royaume-Uni et la Chine. Chaque mois, vous produisez un rapport consolidé pour votre manager, avec des KPI tels que le chiffre d’affaires total, le chiffre d’affaires par pays ou le chiffre d’affaires par produit. Pour ce faire, chaque responsable de pays – Dennis, Douglas et Karen – vous envoie un fichier.

Problème: chaque fichier répertorie les ventes dans la dénomination propre du pays. Il est trop long de les convertir en euros, puis de les regrouper dans le rapport final.

Et si vous pouviez automatiser le processus et calculer votre rapport en quelques minutes en utilisant uniquement la requête Power?

Ce ne serait pas génial? Eh bien, avec Power Query, vous pouvez! Commençons par préparer votre première table 🙂

Tout d’abord, vous avez besoin de données. Téléchargez notre exemple Power Query et enregistrez-le sur votre serveur local.

Ensuite, suivez ces étapes faciles:

• Décompressez le fichier et enregistrez-le sur votre serveur local.
• Ouvrez maintenant une nouvelle feuille de calcul dans Excel et accédez à l’onglet Données > Obtenez les données > Du fichier > Du Classeur, comme indiqué ci-dessous.

( si vous avez Excel version 2013 ou 2010, accédez simplement à l’onglet Power Query)

• Accédez au chemin où vous avez enregistré les fichiers décompressés que vous venez de télécharger et sélectionnez le fichier Excel nommé « Taux de change ».

• Cliquez sur Transformer les données.

Vous avez peut-être également remarqué que certaines étapes appliquées sont déjà apparues dans le volet Paramètres de la requête. Ceux-ci se font automatiquement.

Maintenant, pratiquons un peu, en commençant par nettoyer la table de données:

• Supprimez les 3 lignes du haut, allez dans Supprimer les lignes > Supprimez les lignes du haut et tapez « 3 ». Vous verrez instantanément la transformation dans l’aperçu de la table de données ainsi que dans les étapes appliquées (Paramètres de requête).

• Débarrassez-vous des lignes vides : Supprimez les lignes > Supprimez les lignes vides (comme indiqué ci-dessous).

• Faites la promotion de votre première ligne en tant qu’en-tête : cliquez sur « Utiliser la première ligne comme en-têtes ». Comme vous l’avez peut-être remarqué, Power Query a automatiquement ajouté une étape pour modifier le type de certaines données (les valeurs de la première colonne sont maintenant formatées en dates).

• Supprimer les colonnes vides: cliquez sur « Choisir les colonnes » et décochez les colonnes 7, 8, 9 et 10.

Tu te débrouilles bien ! Trois étapes de plus et vous serez prêt!

• Dépoussiérez les colonnes: Sélectionnez la colonne « Taux de change », puis dans l’onglet Transformer, allez dans Colonnes Unpivot > Unpivot Autres colonnes (vous pouvez également sélectionner les cinq autres colonnes et sélectionner « Colonnes Unpivot »).

• Renommez les colonnes: cliquez sur les cellules d’en-tête, entrez « Date » dans la première, « Devise » dans la seconde et « Taux » dans la troisième.

• Appliquer un filtre: comme dans Excel, cliquez sur le menu déroulant de la colonne « Date » et sélectionnez « Dec-18 ».

• Dans l’onglet Accueil, cliquez sur Fermer et Charger pour charger la table finale dans une nouvelle feuille.

Bravo! Vous avez façonné votre première table de données.

Allons plus loin avec Power Query

(Ré)Imaginez la situation suivante :

Grâce à vos compétences en Power Query, vous êtes désormais contrôleur financier SENIOR de la firme technologique. Maintenant, vous devez consolider les données de décembre 2018 que les gestionnaires de pays vous ont envoyées.

Problème: chaque fichier répertorie les ventes dans la dénomination propre du pays. Il est fastidieux de les convertir en euros, encore plus de les consolider ensuite dans le rapport final.

Et si vous pouviez automatiser le processus et calculer votre rapport en quelques minutes uniquement à l’aide de Power Query?

Maintenant, nous allons voir comment consolider différentes sources de données et les fusionner avec le tableau que nous avons préparé dans la section précédente.

Tout d’abord, importons l’ensemble du dossier, y compris les trois fichiers sources différents:

• Si vous êtes toujours dans Power Query, accédez au nouveau dossier source > File >.

( Si vous revenez sur Excel, accédez à Get Data > À partir du dossier)

• Sélectionnez le dossier « Sales_201812 » que vous avez précédemment téléchargé et enregistré. Après cela, cliquez sur OK.

Une nouvelle fenêtre devrait apparaître.

Ensuite, vous devez fusionner les requêtes:

• Sélectionnez « Combiner & Modifier » dans le menu déroulant (en bas de la nouvelle fenêtre).

• Sélectionnez l’exemple de tableau « Dec-18 » (veuillez noter que toutes les feuilles doivent être nommées de la même manière).

• Vous pouvez vérifier dans la première colonne que toutes les sources sont incluses (cliquez sur le menu déroulant), puis cliquez sur OK.

Vous êtes maintenant dans l’éditeur Power Query.

• Allez dans l’onglet Transformation > Remplacer les valeurs.

• Remplacez maintenant « € » par « EUR ».

• Accédez à l’onglet Accueil > Fusionner les requêtes.

Une nouvelle fenêtre apparaît:

1. Sélectionnez la colonne « Devise ».
2. Sélectionnez le fichier « Taux de change » dans le menu déroulant.
3. Sélectionnez la colonne « Devise ».
4. Cliquez sur OK.

• Maintenant, vous devez afficher les données de taux de change que vous venez de récupérer:
  1. Sélectionnez la colonne « Taux de change ».
  2. Cliquez sur le bouton Développer.
  3. Cochez uniquement « Taux ».
  4. Décochez « Utiliser l’original … ».
  5. Cliquez sur OK.

Félicitations! Vous venez de charger une autre source de données, de fusionner des requêtes et de récupérer le taux de change correspondant. Plus de VLOOKUP ou d’INDEX + MATCH!

Pour finir, nous allons créer une colonne « Prix unitaire EUR » pour convertir tous les prix en Euros:

• Accédez à l’onglet Ajouter une colonne > Colonne personnalisée.

• Une nouvelle fenêtre apparaît. Écrivons la formule du Prix en Euros:
  1. Nommez-le « Prix unitaire EUR ».
  2. Double-cliquez sur « Prix Unitaire Monnaie Locale » (ou sélectionnez-le puis cliquez sur Insérer).
  3. Tapez « / ».
  4. Double-cliquez sur « Taux ».
  5. Cliquez sur OK.

• Une fois que la colonne apparaît, cliquez sur son symbole de type (juste avant le nom) et sélectionnez « Nombre décimal ».

Nous devons faire de même pour générer la colonne « Revenus »:

• Accédez à l’onglet Ajouter une colonne > Colonne personnalisée.
• Une nouvelle fenêtre apparaît. Tapez la formule de revenu:
  1. Nommez-la « Revenu ».
  2. Double-cliquez sur « Prix unitaire EUR » (ou sélectionnez-le puis cliquez sur Insérer).
  3. Tapez « * ».
  4. Double-cliquez sur « Quantité ».
  5. Cliquez sur OK.

• Encore une fois, changez le type de colonne en « Nombre décimal ».

Pour finir, exportons-le vers une nouvelle table de données dans Excel!

• Allez à l’onglet Accueil > Fermer & Charger > Fermer & Charger.

Vous pouvez désormais bénéficier pleinement des fonctionnalités d’Excel pour analyser les données, créer un tableau croisé dynamique, etc.

Wow! Si vous êtes arrivé à la fin, félicitations! Vous savez maintenant comment fonctionne Power Query.

Maintenant c’est votre tour!

Je veux vous le remettre: avez-vous compris toutes les questions ci-dessus sur Power Query?
Avez-vous téléchargé nos fichiers de didacticiels et suivez les instructions ci-dessus pour l’essayer par vous-même?
Faites-le nous savoir en laissant un commentaire rapide ci-dessous dès maintenant!

Pour en savoir plus sur Power Query :