Tutoriel Excel Power Query (Get et Transform)
Si vous faites partie de ces personnes qui travaillent beaucoup avec des données, vous pouvez être n’importe qui ( comptable , RH, analyste de données, etc.), PowerQuery peut être votre outil puissant.
Permettez-moi d’aller droit au but, Power Query est l’une des compétences avancées d’Excel que vous devez acquérir et dans ce didacticiel, vous explorerez en détail PowerQuery et apprendrez à transformer des données avec.
Commençons.
Qu’est-ce qu’Excel Power Query
Power Query est un complément Excel que vous pouvez utiliser pour ETL. Cela signifie que vous pouvez extraire des données de différentes sources, les transformer , puis les charger dans la feuille de calcul. Vous pouvez dire que POWER QUERY est une machine de nettoyage de données car il dispose de toutes les options pour transformer les données. Il est en temps réel et enregistre toutes les étapes que vous effectuez.
Pourquoi devriez-vous utiliser Power Query (Avantages) ?
Si vous avez cette question en tête, voici ma réponse pour vous :
- Différentes sources de données : vous pouvez charger des données dans un éditeur de requêtes puissant à partir de différentes sources de données, telles que CSV , TXT, JSON, etc.
- Transformez facilement les données : Normalement, vous utilisez des formules et des tableaux croisés dynamiques pour les transformations de données, mais avec POWER QUERY, vous pouvez faire beaucoup de choses en quelques clics.
- C’est en temps réel : écrivez une requête une fois et vous pouvez l’actualiser à chaque fois qu’il y a un changement dans les données, et cela transformera les nouvelles données que vous avez mises à jour.
Permettez-moi de partager un exemple :
Imaginez que vous ayez 100 fichiers Excel contenant des données de 100 villes et que votre patron veuille maintenant que vous créiez un rapport avec toutes les données de ces 100 fichiers. OKAY, si vous décidez d’ouvrir chaque fichier manuellement et de copier et coller les données de ces fichiers et que vous avez besoin d’au moins une heure pour cela.
Mais avec la requête de puissance, vous pouvez le faire en quelques minutes. Vous vous sentez excité? Bien.
Plus loin dans ce didacticiel, vous apprendrez à utiliser Power Query avec de nombreux exemples, mais vous devez d’abord comprendre son concept.
Le concept de requête de puissance
Pour apprendre power query, il faut comprendre son concept qui fonctionne en 3 étapes :
1. Obtenir des données
Power query vous permet d’obtenir des données à partir de différentes sources telles que le Web, CSV, des fichiers texte, plusieurs classeurs à partir d’un dossier et de nombreuses autres sources où nous pouvons stocker des données.
2. Transformer les données
Après avoir obtenu des données dans la requête de puissance, vous disposez de tout un tas d’options que vous pouvez utiliser pour les transformer et les nettoyer. Il crée des requêtes pour toutes les étapes que vous effectuez (dans une séquence une étape après l’autre).
3. Charger les données
À partir de l’éditeur de requêtes avancées, vous pouvez charger les données transformées dans la feuille de calcul, ou vous pouvez créer directement un tableau croisé dynamique ou un graphique croisé dynamique ou créer une connexion de données uniquement.
Où est Power Query (Comment l’installer) ?
Ci-dessous, vous pouvez voir comment installer l’accès à la requête de puissance dans les différentes versions de Microsoft Excel.
Excel 2007
Si vous utilisez Excel 2007, je suis désolé que PQ ne soit pas disponible pour cette version, vous devez donc mettre à niveau vers la latest version d’Excel (Excel pour Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).
Excel 2010 et Excel 2013
Pour 2010 et 2013, vous devez installer un complément séparément que vous pouvez télécharger à partir de ce lien et une fois installé, vous obtiendrez un nouvel onglet dans le ruban Excel, comme ci-dessous :
- Tout d’abord, téléchargez le complément à partir d’ici (site Web officiel de Microsoft).
- Une fois le fichier téléchargé, ouvrez-le et suivez les instructions.
- Après cela, vous obtiendrez automatiquement l’onglet « Power Query » sur votre ruban Excel.
Si d’une manière ou d’une autre cet onglet « POWER QUERY » n’apparaît pas, il n’y a pas lieu de s’en inquiéter. Vous pouvez l’ajouter à l’aide de l’option Compléments COM.
- Accédez à l’onglet Fichier ➜ Options ➜ Compléments.
- Dans les options « Add-In », sélectionnez « COM Add-ins » et cliquez sur GO.
- Après cela, cochez la case « Microsoft Power Query pour Excel ».
- À la fin, cliquez sur OK.
Excel 2016, 2019, Office 365
Si vous utilisez Excel 2016, Excel 2019, ou si vous avez un abonnement OFFICE 365, il est déjà là sur l’onglet Données , en tant que groupe nommé « GET & TRANSFORM » (j’aime ce nom, et vous ?).
Excel Mac
Si vous utilisez Excel sur Mac, je crains qu’il n’y ait pas de complément de requête de puissance pour cela et vous ne pouvez actualiser qu’une requête existante , mais vous ne pouvez pas en créer une nouvelle ni même modifier une requête ( LINK ).
Éditeur de requête de puissance
Power Query possède son propre éditeur dans lequel vous pouvez obtenir les données, effectuer toutes les étapes pour créer des requêtes, puis les charger dans la feuille de calcul. Pour ouvrir l’éditeur de requête de puissance, vous devez accéder à l’onglet Données et dans Obtenir et transformer ➜ Obtenir des données ➜ Lancer l’éditeur de requête de puissance.
Vous trouverez ci-dessous un premier aperçu de l’éditeur que vous obtiendrez lorsque vous l’ouvrirez.
Maintenant, explorons chaque section en détail :
1. Ruban
Regardons tous les onglets disponibles :
- Fichier : à partir de l’onglet Fichier, vous pouvez charger les données, supprimer l’éditeur et ouvrir les paramètres de la requête.
- Accueil : Dans l’onglet ACCUEIL, vous avez des options pour gérer les données chargées, comme, supprimer et déplacer des colonnes et des lignes.
- Transformer : Cet onglet contient toutes les options dont vous avez besoin pour transformer et nettoyer les données, comme fusionner des colonnes, transposer, etc.
- Ajouter une colonne : Ici, vous avez la possibilité d’ajouter de nouvelles colonnes aux données que vous avez dans l’éditeur avancé.
- Affichage : à partir de cet onglet, vous pouvez apporter des modifications à l’affichage de l’éditeur de requête avancé et aux données chargées.
2. Étapes appliquées
Sur le côté droit de l’éditeur, vous avez un volet de paramétrage de requête qui inclut le nom de la requête et toutes les étapes appliquées dans une séquence.
Lorsque vous faites un clic droit sur une étape, vous avez une liste d’options que vous pouvez effectuer, comme, renommer, supprimer, modifier, monter ou descendre, etc. et lorsque vous cliquez sur une étape, l’éditeur vous amènera à la transformation fait sur cette étape.
Regardez ci-dessous où vous avez appliqué les cinq étapes au total et lorsque je clique sur la 4ème étape, cela me conduit à la transformation de l’étape quatre où le nom des colonnes n’a pas changé.
3. Requêtes
Le volet des requêtes sur le côté gauche répertorie toutes les requêtes que vous avez actuellement dans le classeur. C’est essentiellement un endroit où vous pouvez gérer toutes les requêtes.
Lorsque vous faites un clic droit sur un nom de requête, vous pouvez voir toutes les options que vous pouvez utiliser (copier, supprimer, dupliquer, etc.)
Vous pouvez également créer une nouvelle requête en cliquant simplement avec le bouton droit de la souris sur l’espace vide du volet des requêtes, puis en sélectionnant l’option pour la source de données.
4. Barre de formule
Comme je l’ai dit, chaque fois que vous appliquez une étape dans l’éditeur, il génère du code M pour cette étape, et vous pouvez voir ce code dans la barre de formule. Vous pouvez simplement cliquer sur la barre de formule pour modifier le code.
Une fois que vous avez appris à utiliser le code M, vous pouvez également créer une étape en écrivant le code et en cliquant simplement sur le bouton « FX » pour entrer une étape personnalisée.
5. Aperçu des données
La zone d’aperçu des données ressemble à une feuille de calcul Excel, mais elle est légèrement différente d’une feuille de calcul normale dans laquelle vous pouvez modifier directement une cellule ou des données. Lorsque vous chargez des données dans l’éditeur (nous le ferons dans un moment), il affiche toutes les colonnes avec les en-têtes avec le nom des colonnes, puis les lignes avec les données.
En haut de chaque colonne, vous pouvez voir le type de données des données de la colonne. Lorsque vous chargez des données dans l’éditeur, la requête avancée applique automatiquement le bon type de données (presque à chaque fois) à chaque colonne.
Vous pouvez cliquer sur le bouton en haut à gauche de l’en-tête de colonne pour modifier le type de données appliqué à la colonne. Il contient une liste de tous les types de données à partir desquels vous pouvez.
Et sur le côté gauche de l’en-tête de colonne, vous avez le bouton de filtre que vous pouvez utiliser pour filtrer les valeurs de la colonne. Remarque : Lorsque vous filtrez les valeurs d’une colonne, la requête avancée la considère comme une seule étape et la répertorie dans les étapes appliquées.
Si vous faites un clic droit sur l’en-tête de la colonne, vous pouvez voir qu’il y a un menu qui comprend une liste des options que vous pouvez utiliser pour transformer les données et utiliser l’une des options et PQ le stocke comme une étape dans le processus appliqué. pas.
Sources de données pour Power Query
La meilleure partie de la requête de puissance est que vous avez la possibilité d’obtenir des données à partir de plusieurs sources et de transformer ces données, puis de les charger dans la feuille de calcul. Lorsque vous cliquez sur Get Data dans GET & TRANSFORM, vous pouvez voir la liste complète des sources de données que vous pouvez charger dans l’éditeur.
Examinons maintenant certaines des sources de données :
- À partir de la table/plage : avec cette option, vous pouvez charger des données dans l’éditeur de requêtes avancées directement à partir de la feuille de calcul active.
- À partir du classeur : À partir d’un autre classeur que vous avez sur votre ordinateur. Il vous suffit de localiser ce fichier à l’aide d’une boîte de dialogue ouverte et il obtiendra automatiquement les données de ce fichier.
- À partir de texte/CSV : obtenez des données à partir d’un fichier texte ou d’un fichier séparé par des virgules, puis vous pouvez les charger dans la feuille de calcul.
- À partir du dossier : Il prend tous les fichiers du dossier et en charge les données dans l’éditeur de requête puissant. (Voir ceci : Combiner des fichiers Excel à partir d’un dossier ).
- À partir du Web : Avec cette option, vous obtenez des données à partir d’une adresse Web, imaginez que vous avez un fichier qui est stocké sur le Web ou que vous avez une page Web à partir de laquelle vous devez obtenir les données.
Comment charger des données dans Power Query Editor
Apprenons maintenant à charger des données dans l’éditeur de requête puissant. Ici vous avez une liste de noms d’étudiants et leurs scores ( LIEN ).
Vous allez charger des données directement à partir de la feuille de calcul, vous devez donc d’abord ouvrir le fichier, puis suivre les étapes ci-dessous :
- Tout d’abord, appliquez un tableau Excel aux données (même si vous ne le faites pas, Excel le fera pour vous avant de charger les données dans l’éditeur PQ).
- Maintenant, sélectionnez une cellule dans le tableau et cliquez sur « Depuis le tableau/plage » (onglet Données Get & Transform).
- Une fois que vous avez cliqué sur le bouton, Excel confirme la plage de données pour lui appliquer un tableau Excel.
- À ce stade, vous avez les données dans l’éditeur de requête de puissance, et cela ressemble à quelque chose comme ci-dessous.
- Içi vous pouvez voir:
- Dans la barre de formule, PQ a généré le code M pour la table que vous venez de charger dans l’éditeur.
- Sur le côté gauche de l’éditeur, vous avez le volet des requêtes où vous avez la liste des requêtes.
- Sur le côté droit, dans les paramètres de la requête, vous avez la section intitulée « Étapes appliquées » où vous avez toutes les étapes répertoriées. Remarque : Vous devez penser que vous n’avez effectué aucun « Type modifié », mais il y a une étape appelée « Type modifié » qui s’y trouve. Laissez-moi vous dire l’ INTELLIGENCE de POWER QUERY lorsque vous chargez des données dans l’éditeur, il vérifie et applique automatiquement les types de données corrects pour toutes les colonnes.
Exemples de Power Query (trucs et astuces)
Vous pouvez apprendre à effectuer certaines des tâches de base que vous effectuez normalement avec des formules fonctionnelles dans Excel, mais avec PowerQuery, vous pouvez le faire en quelques clics :
1. Remplacer les valeurs
Vous avez une liste de valeurs et vous souhaitez remplacer une ou plusieurs valeurs par autre chose. Eh bien, avec l’aide de la requête de puissance , vous pouvez créer une requête et remplacer ces valeurs, en un rien de temps.
Dans la liste ci-dessous, vous devez remplacer mon nom « Puneet » par « Punit ».
- Tout d’abord, modifiez la liste dans l’éditeur de requête avancé.
- Après cela, dans l’éditeur de requête de puissance, allez dans « Transformer l’onglet » et cliquez sur « Remplacer les valeurs ».
- Maintenant, dans « Valeur à trouver », entrez « Puneet » et dans « Remplacer par », entrez « Punit » et après cela, cliquez sur OK.
- Une fois que vous avez cliqué sur OK, toutes les valeurs sont remplacées par les nouvelles valeurs et maintenant, cliquez sur « Fermer et charger » pour charger les données dans la feuille de calcul.
2. Trier les données
Tout comme le tri normal, vous pouvez trier les données en utilisant PowerQuery et j’utilise la même liste de noms que celle que vous avez utilisée dans l’exemple ci-dessus.
- Tout d’abord, chargez les données dans l’éditeur de requête puissant.
- Dans l’onglet Accueil, vous disposez de deux boutons de tri (Croissant et Décroissant).
- Cliquez sur l’un de ces boutons pour trier.
3. Supprimer des colonnes
Disons que vous avez des données quelque part et que vous devez en supprimer certaines colonnes. Le fait est que vous devez supprimer ces colonnes chaque fois que vous ajoutez de nouvelles données, n’est-ce pas ? Mais, la requête de puissance peut prendre soin de cela.
- Sélectionnez la colonne ou plusieurs colonnes que vous souhaitez supprimer.
- Maintenant, faites un clic droit et sélectionnez « Supprimer ».
Astuce rapide : Il existe également une option pour « Supprimer d’autres colonnes » où vous pouvez supprimer toutes les colonnes non sélectionnées.
4. Colonne divisée
Tout comme l’option de texte en colonne, vous avez « Split Column » dans la requête de puissance. Laissez-moi vous dire comment cela fonctionne.
- Sélectionnez la colonne et accédez à l’onglet Accueil ➜ Transformer ➜ Diviser la colonne ➜ Par délimiteur.
- Sélectionnez la coutume dans la liste déroulante et entrez « – » dedans.
- Maintenant, vous avez ici trois options différentes pour diviser une colonne.
- Délimiteur le plus à gauche
- Délimiteur le plus à droite
- Chaque occurrence du délimiteur
Si vous n’avez qu’un seul délimiteur dans une cellule, les trois fonctionneront de la même manière, mais si vous avez plus d’un délimiteur, vous devez choisir en conséquence.
5. Renommer une colonne
Vous pouvez simplement renommer une colonne par un clic droit puis cliquer sur « Renommer ».
Petite astuce : Supposons que vous ayez une requête pour renommer une colonne et que quelqu’un d’autre la renomme par erreur. Vous pouvez restaurer ce nom d’un simple clic.
6. Colonne en double
Dans Power Query, il existe une option simple pour créer une colonne dupliquée. Il vous suffit de faire un clic droit sur la colonne pour laquelle vous souhaitez créer une colonne dupliquée puis de cliquer sur « Dupliquer la colonne ».
7. Transposer la colonne ou la ligne
Dans la requête de puissance, la transposition est un jeu d’enfant. Oui, juste un clic.
- Une fois que vous avez chargé les données dans l’éditeur de requêtes avancées, il vous suffit de sélectionner la ou les colonnes ou les lignes.
- Allez dans l’onglet Transformer ➜ Tableau ➜ Transposer.
8. Remplacer/supprimer les erreurs
Normalement, pour remplacer ou supprimer des erreurs dans Excel, vous pouvez utiliser l’option Rechercher et remplacer ou un code VBA. Mais dans PowerQuery, c’est beaucoup plus facile. Regardez la colonne ci-dessous où vous avez des erreurs et vous pouvez les supprimer et les remplacer.
Lorsque vous faites un clic droit sur la colonne, vous aurez les deux options.
- Remplacer les erreurs
- Supprimer les erreurs
9. Changer le type de données
Vous avez des données dans une colonne mais elles ne sont pas au bon format. Ainsi, chaque fois que vous devez changer son format.
- Tout d’abord, modifiez les données dans l’éditeur de requête puissant.
- Après cela, sélectionnez la colonne et accédez à l’onglet Transformer.
- Maintenant, à partir du type de données, sélectionnez « Date » comme type.
10. Ajouter une colonne à partir d’exemples
Dans la requête avancée, il existe une option pour ajouter un exemple de colonne qui n’est pas réellement un exemple lié à la colonne actuelle.
Laisse moi te donner un exemple:
Imaginez que vous ayez besoin de noms de jours à partir d’une colonne de date. Au lieu d’utiliser une formule ou toute autre option, vous pouvez utiliser, vous pouvez utiliser « Ajouter une colonne à partir d’exemples ».
Voici comment procéder :
- Faites un clic droit sur une colonne et cliquez sur « Ajouter une colonne à partir d’exemples ».
- Ici, vous obtiendrez une colonne vide. Cliquez sur la première cellule de la colonne pour obtenir la liste des valeurs que vous pouvez insérer.
- Sélectionnez « Nom du jour de la semaine à partir de la date » et cliquez sur OK.
Boom! votre nouvelle colonne est ici.
11. Changer de cas
Vous disposez des options suivantes pour modifier la casse du texte dans PowerQuery.
- Minuscule
- Haut de casse
- Capitaliser chaque mot
Vous pouvez le faire en cliquant avec le bouton droit sur une colonne et en sélectionnant l’une des trois options ci-dessus. Ou, allez dans l’onglet Transformer ➜ Colonne de texte ➜ Format.
12. Taillez et nettoyez
Pour effacer des données ou supprimer des espaces indésirables , vous pouvez utiliser les options TRIM et CLEAN dans PowerQuery. Les étapes sont simples :
- Faites un clic droit sur une colonne ou sélectionnez toutes les colonnes si vous avez plusieurs colonnes.
- Accédez à l’onglet Transformer ➜ Colonne de texte ➜ Format.
- TRIM : pour supprimer les espaces blancs de fin et de début d’une cellule.
- NETTOYER : pour supprimer les caractères non imprimables d’une cellule.
13. Ajouter un préfixe/suffixe
Vous avez donc une liste de valeurs et à partir de cette liste, vous souhaitez ajouter un préfixe/suffixe dans chaque cellule. Dans Excel, vous pouvez utiliser la méthode de concaténation, mais dans PowerQuery, il existe une option simple à utiliser pour les deux.
- Tout d’abord, sélectionnez la colonne dans laquelle vous devez ajouter un préfixe/suffixe.
- Ensuite, allez dans l’onglet Transformer ➜ Colonne de texte ➜ Format ➜ Ajouter un préfixe/ Ajouter un suffixe.
- Une fois que vous avez cliqué sur l’une des options, vous obtiendrez une boîte de dialogue pour saisir du texte.
- Et après avoir saisi le texte, cliquez sur OK.
14. Extraire les valeurs
Si vous êtes un connaisseur en formules, alors je suis sûr que vous êtes d’accord avec moi que l’extraction de texte ou de nombre d’une cellule nécessite de combiner différentes fonctions. Mais PowerQuery a résolu bon nombre de ces problèmes. Vous avez sept façons d’extraire des valeurs d’une cellule.
15. Uniquement la date ou l’heure
Il arrive souvent que vous ayez la date et l’heure, toutes deux dans une seule cellule, mais que vous ayez besoin de l’une d’entre elles.
- Sélectionnez la colonne où vous avez la date et l’heure combinées.
- Si tu veux:
- Date : Clic droit ➜ Transformer ➜ Date uniquement.
- Heure : Clic droit ➜ Transformer ➜ Heure uniquement.
16. Combiner la date et l’heure
Vous savez maintenant comment séparer la date et l’heure. Mais la prochaine, vous devez savoir comment les combiner.
- Tout d’abord, sélectionnez la colonne de date et cliquez sur l’option « Date uniquement ».
- Après cela, sélectionnez les deux colonnes (Date et heure) et accédez à l’onglet de transformation et à partir du groupe « Colonne de date et d’heure », accédez à Date et cliquez sur « Combiner la date et l’heure ».
17. Chiffres arrondis
Voici les options suivantes que vous avez pour arrondir les nombres.
- Arrondir à l’inférieur : Pour arrondir un nombre à l’inférieur.
- Arrondir : Pour arrondir un nombre.
- Arrondi : vous pouvez choisir jusqu’à quel degré vous pouvez arrondir mes décimales.
Voici les étapes :
- Sélectionnez la colonne et faites un clic droit ➜ Transform ➜ Round .
- Arrondir à l’inférieur : Pour arrondir un nombre à l’inférieur.
- Arrondir : Pour arrondir un nombre.
- Arrondi : vous pouvez choisir jusqu’à quel degré vous pouvez arrondir mes décimales.
Remarque : Lorsque vous sélectionnez l’option « #3 Round », vous devez entrer le nombre de décimales à arrondir.
18. Calculs
Il existe des options que vous pouvez utiliser pour effectuer des calculs (beaucoup). Vous pouvez trouver toutes ces options dans l’onglet Transformer (dans le groupe Colonne numérique).
- Basique
- Statistiques
- Scientifique
- Trigonométrie
- Arrondi
- Information
Pour effectuer l’un de ces calculs, vous devez sélectionner la colonne, puis l’option.
19. Grouper par
Supposons que vous disposiez d’un grand ensemble de données et que vous souhaitiez créer un tableau récapitulatif. Voici ce que vous devez faire :
- Dans l’onglet Transformer, cliquez sur le bouton ‘Grouper par’ et vous obtiendrez une boîte de dialogue.
- Maintenant, à partir de cette boîte de dialogue, sélectionnez la colonne avec laquelle vous souhaitez grouper et après cela, ajoutez un nom, sélectionnez l’opération et la colonne où vous avez des valeurs.
- À la fin, cliquez sur OK.
Remarque : Il existe également des options avancées dans l’option « Regrouper par » que vous pouvez utiliser pour créer un tableau de groupe à plusieurs niveaux.
20. Supprimer les valeurs négatives
Dans l’un de mes articles de blog, j’ai répertorié sept méthodes pour supprimer les signes négatifs et la requête de puissance en fait partie. Faites un clic droit sur une colonne et accédez à l’option de transformation, puis cliquez sur « Valeur absolue ».
Cela supprime instantanément tous les signes négatifs des valeurs.
Comment charger des données dans la feuille de calcul
Une fois que vous avez transformé vos données, vous pouvez les charger dans la feuille de calcul et les utiliser pour une analyse plus approfondie. Sur l’onglet d’accueil, il y a un bouton appelé « Fermer et charger » lorsque vous cliquez dessus, vous obtenez une liste déroulante qui propose des options supplémentaires :
- Fermer et charger
- Fermer et charger dans
- Une fois que vous avez cliqué sur le bouton, il affichera les options suivantes :
- Sélectionnez la manière dont vous souhaitez afficher ces données dans votre feuille de calcul.
- Tableau
- Rapport de tableau croisé dynamique :
- Tableau croisé dynamique
- Créer uniquement une connexion
- Où voulez-vous mettre les données ?
- Feuille de travail existante
- Nouvelle feuille de travail.
- Ajoutez ces données au modèle de données.
- Sélectionnez simplement l’option de table et la nouvelle feuille de calcul et ne cochez pas le modèle de données et cliquez sur OK.
- Au moment où vous cliquez sur OK, il ajoute une nouvelle feuille de calcul avec les données.
Plus d’exemples à apprendre
Actualiser automatiquement une requête
De tous les exemples que j’ai cités ici, celui-ci est le plus important. Lorsque vous créez une requête, vous pouvez l’actualiser automatiquement (vous pouvez définir une minuterie).
Et voici les étapes :
- Dans l’onglet Données, cliquez sur « Requêtes et connexions » et vous obtiendrez le volet Requêtes et connexion sur le côté droit de la fenêtre.
- Maintenant, faites un clic droit sur la requête et cochez « Actualiser toutes les » et entrez les minutes.
Comment utiliser une formule et une fonction dans Power Query
Tout comme vous pouvez utiliser des fonctions et des formules dans une feuille de calcul Excel, la requête avancée possède sa propre liste de fonctions que vous pouvez utiliser. Les bases de la fonction et des formules dans PowerQuery sont les mêmes que les fonctions de feuille de calcul d’Excel.
Dans PQ, vous devez ajouter une nouvelle colonne personnalisée pour ajouter une fonction ou une formule.
Prenons un exemple : Dans les données ci-dessous (déjà dans l’éditeur PQ) vous avez le prénom et le nom ( DOWNLOAD LINK ) .
Imaginez que vous deviez fusionner les deux noms et créer une colonne pour le nom complet. Dans ce cas, vous pouvez entrer une formule simple pour concaténer les noms des deux colonnes.
- Tout d’abord, allez dans l’onglet Ajouter une colonne et cliquez sur « Colonne personnalisée ».
- Maintenant, dans la boîte de dialogue de la colonne personnalisée, entrez le nom de la nouvelle colonne « Nom complet » ou tout ce que vous voulez nommer la nouvelle colonne.
-
- La formule de colonne personnalisée est l’endroit où vous devez entrer la formule. Entrez donc la formule ci-dessous :
[First Name]&" "&[Last Name]
- Lorsque vous entrez une formule dans la « formule de colonne personnalisée », PQ vérifie la formule que vous avez entrée et affiche un message « Aucune erreur de syntaxe n’a été détectée » et s’il y a une erreur, il affichera un message d’erreur basé sur le type de la erreur.
- Une fois que vous avez entré la formule et que cette formule ne contient aucune erreur, appuyez simplement sur OK.
- Vous avez maintenant une nouvelle colonne à la fin des données qui contient des valeurs de deux colonnes (prénom et nom de famille).
Comment utiliser une fonction dans Power Query
De la même manière, vous pouvez également utiliser une fonction tout en ajoutant une colonne personnalisée et Power Query propose une énorme liste de fonctions que vous pouvez utiliser.
Comprenons comment utiliser une fonction avec un exemple facile et simple. Je continue l’exemple ci-dessus où nous avons ajouté une nouvelle colonne en combinant le prénom et le nom de famille.
Mais maintenant, vous devez convertir le texte du nom complet que vous avez dans cette colonne en majuscules. La fonction que vous pouvez utiliser est « Text.Upper » . Comme son nom l’indique, il convertit un texte en texte majuscule.
- Tout d’abord, allez dans l’onglet Ajouter une colonne et cliquez sur la colonne personnalisée.
-
- Maintenant, dans la boîte de dialogue de la colonne personnalisée, entrez le nom de la colonne et la formule ci-dessous dans la zone de formule de la colonne personnalisée :
Text.Upper([Full Name])
- Et lorsque vous cliquez sur OK, cela crée une nouvelle colonne avec tous les noms en majuscules.
- La prochaine chose est de supprimer l’ancienne colonne et de renommer la nouvelle colonne. Faites donc un clic droit sur la première colonne et sélectionnez supprimer.
- Au final, renommer la nouvelle colonne son « Full Name ».
Il y a un total de 700 fonctions que vous pouvez utiliser dans power query tout en ajoutant une nouvelle colonne et voici la liste complète fournie par Microsoft pour ces fonctions, consultez-les.
Comment modifier une requête dans PQ
Si vous souhaitez apporter des modifications à la requête qui se trouve déjà dans votre classeur, vous pouvez simplement la modifier, puis apporter ces modifications. Dans l’onglet Données, il y a un bouton nommé Requêtes et connexions.
Lorsque vous cliquez sur ce bouton, il ouvre un volet sur le côté droit qui répertorie toutes les requêtes que vous avez dans le classeur en cours.
Vous pouvez cliquer avec le bouton droit sur le nom de la requête et sélectionner modifier et vous l’obtiendrez dans l’éditeur de requête puissant pour le modifier.
Lorsque vous modifiez une requête, vous pouvez voir que toutes les étapes que vous avez effectuées précédemment sont répertoriées dans les « Étapes appliquées » que vous pouvez également modifier ou vous pouvez effectuer de nouvelles étapes.
Et une fois que vous avez terminé vos modifications, vous pouvez simplement cliquer sur le bouton « Fermer et charger ».
Exporter et importer des connexions
Si vous avez une connexion que vous avez utilisée pour une requête et que vous souhaitez maintenant partager cette connexion avec quelqu’un d’autre, vous pouvez exporter cette connexion en tant que fichier odc.
Sur la table de requête, il y a un bouton appelé « Exporter la connexion » et lorsque vous cliquez dessus, il vous permet d’enregistrer la connexion de cette requête dans votre système.
Et si vous souhaitez importer une connexion qui est partagée par quelqu’un d’autre, vous pouvez simplement aller dans l’onglet Données et dans le Get & Transform cliquez sur les connexions existantes .
Et puis cliquez sur le bouton « Parcourir pour plus » à partir duquel vous pouvez localiser le fichier de connexion qui a été partagé avec vous et l’importer dans votre classeur.
Langage PowerQuery (code M)
Comme je l’ai mentionné plus tôt, pour chaque étape que vous avez effectuée dans PowerQuery, il génère un code (au niveau du backend) qui s’appelle M Code . Dans l’onglet Accueil, il y a un bouton appelé » Éditeur avancé » que vous pouvez utiliser pour voir le code.
Et lorsque vous cliquez sur l’éditeur avancé, il vous montrera l’éditeur de code et ce code ressemble à ceci :
M est un langage sensible à la casse et comme tous les autres langages, il utilise des variables et des expressions. La structure de base du code ressemble à ci-dessous où le code commence par l’expression LET.
Dans ce code, nous avons deux variables et les valeurs qui leur sont définies. Au final, pour obtenir la valeur, l’expression IN a été utilisée. Maintenant, lorsque vous cliquez sur OK, il renverra la valeur attribuée à la variable « Variablename » dans le résultat.
Consultez cette ressource pour en savoir plus sur Power Query Language.
À la fin
Power Query est un moteur de transformation de données que vous pouvez utiliser pour obtenir des données à partir de plusieurs sources, nettoyer et transformer ces données, puis les utiliser plus avant dans l’analyse.
Vous ne pouvez pas vous permettre d’éviter le POWER QUERY. Si vous pensez comme ça, beaucoup de choses que nous faisons avec les fonctions Excel ou les codes VBA peuvent être automatisées en l’utilisant, et je suis sûr que ce tutoriel vous inspirera à l’utiliser de plus en plus.