Comment utiliser Power Query dans Excel : Le guide complet

Power Query est un puissant outil d'informatique décisionnelle dans Excel qui vous permet d'importer, de nettoyer et de transformer des données en fonction de vos besoins. Si vous avez déjà passé des heures à nettoyer des données (nous l'avons fait) ou si vous avez eu du mal à fusionner des données provenant de diverses sources (nous l'avons également fait), cette fonctionnalité vous facilitera la vie.

La meilleure façon d'apprendre à utiliser Power Query dans Excel consiste à effectuer certaines tâches courantes telles que l'importation de données et l'utilisation de l'éditeur Power Query pour transformer les tables résultantes et combiner les données.

Cet article vous aide à démarrer en montrant des exemples clairs de Power Query avec des guides pas à pas . En maîtrisant Power Query, vous pouvez élever votre travail au niveau supérieur.

Es-tu prêt? Faisons cela.

Table des matières

• Comment savoir si vous pouvez accéder à Power Query
• Comment importer des données avec Power Query
• Comment utiliser l'éditeur Power Query
  • Aperçu des données
  • Propriétés
• Comment travailler avec plusieurs sources de données
• Comment ajouter des données avec Power Query
• Comment fusionner des données avec Power Query
• 5 façons de transformer les données avec Power Query
  • 1. Développer et condenser les données
  • 2. Tri des données
  • 3. Formatage des données
  • 4. Ajouter et supprimer des colonnes
  • 5. Modification des types de données
• Travailler avec des formules et le langage M
  • Barre de formule
  • Écrire et modifier le code M
• Qu'est-ce que la syntaxe du langage M ?
• Comment actualiser les connexions de données
• Comment intégrer Power Query au modèle de données
• Comment automatiser avec Power Query et VBA
• Fonctionnalités avancées de Power Query
• Collaboration et partage
• Résumons ça

Comment savoir si vous pouvez accéder à Power Query

Avant de commencer à utiliser Power Query, vous devez vous assurer que vous disposez d'une version d'Excel dotée de la fonctionnalité.

Voici un bref aperçu de Power Query par version :

• Excel 2010 et Excel 2013 : disponibles en tant que complément qui doit être téléchargé et installé.

• Excel 2016 : intégré dans Excel en tant que « Get & Transform ».

• Excel 2019 : performances et fonctionnalités améliorées.

• Excel 2021 et Excel pour Microsoft 365 : intégration avec des sources de données cloud.

• Excel pour Mac : disponible uniquement à partir de Microsoft 365 version 16.69 et versions ultérieures.

Pour vérifier que l'outil est intégré dans votre classeur Excel (2016 ou version ultérieure), accédez à l'onglet Données et recherchez la section "Obtenir et transformer les données" dans cette image :

Si vous avez Excel 2010 ou 2013 sous Windows, vous pouvez télécharger , installer et activer le complément Power Query.

Comment importer des données avec Power Query

L'outil Power Query vous permet d'importer et de vous connecter à un large éventail de sources de données :

• Fichiers, y compris les formats CSV, XML et JSON

• URL Web ou points de terminaison d'API

• Bases de données, y compris Access, SQL Server, Oracle et la plupart des bases de données modernes

• Services Azure, fichiers SharePoint et flux OData

La source de données la plus simple pour commencer est un simple fichier CSV avec plusieurs lignes de données. Créez un fichier texte et ajoutez ces quatre noms et prénoms, séparés par une virgule :

• Joe, Blogs

• Anne, Ryan

• James, Stewart

• Marie, brune

Assurez-vous d'enregistrer le fichier avec l'extension .CSV. Votre fichier ressemblera à ceci :

Pour charger les données avec Power Query, procédez comme suit :

1. Accédez à l'onglet Données.

2. Cliquez sur le bouton "Obtenir les fichiers" dans l'onglet Obtenir et transformer les données.

3. Sélectionnez votre fichier d'exemple.

Vous devrez peut-être attendre quelques secondes pour que l'outil extraie les données dans le volet de prévisualisation. Power Query utilise beaucoup de ressources machine, même pour une petite importation, alors soyez patients.

Conseil : fermez les autres applications pour accélérer les performances.

Power Query affichera la fenêtre « Importation de texte/CSV » avec un aperçu de vos données.

Si les données semblent correctes, vous pouvez procéder au chargement ou à la transformation des données :

• le bouton Charger chargera les données directement dans Excel.

• le bouton Transformer les données vous permet de modifier les données avant de les charger.

Avez-vous remarqué que le fichier d'exemple n'avait pas d'en-tête ? S'il est chargé directement dans Excel, les en-têtes seront "Colonne 1" et "Colonne 2".

Au lieu de cela, vous pouvez extraire les données dans l'éditeur Power Query et apporter des modifications. Pour ce faire, cliquez sur le bouton "Transformer les données".

L'éditeur Power Query s'ouvre maintenant, ce qui nous amène à la section suivante.

Comment utiliser l'éditeur Power Query

Dans cette section, nous allons explorer certains aspects essentiels de l'utilisation de l'éditeur Power Query.

Aperçu des données

Dans la zone Aperçu des données, vous pouvez afficher et interagir avec vos données importées. Vous pouvez filtrer les lignes, modifier les types de données des colonnes et remodeler les données en fonction de vos besoins.

Lorsque vous avez terminé, Excel charge les données transformées dans une feuille de calcul.

À l'aide du fichier d'exemple, vous pouvez modifier les noms de colonne directement dans ce volet.

Faire cela:

1. Cliquez avec le bouton droit sur l'en-tête de colonne.

2. Cliquez sur "Renommer" dans le menu déroulant.

3. Tapez les noms préférés dans les zones d'en-tête.

Au fur et à mesure que vous apportez des modifications, la section "Étapes appliquées" à droite du volet d'aperçu est mise à jour pour refléter chaque transformation, vous permettant de suivre et de modifier vos étapes de traitement des données selon vos besoins.

Propriétés

La définition des propriétés de vos requêtes est essentielle pour organiser et suivre votre travail.

Le volet Paramètres de la requête se trouve à droite de la fenêtre Power Query.

1. Cliquez sur le lien "Toutes les propriétés".

2. Modifiez le nom de la requête pour qu'il soit descriptif.

3. Ajoutez une description pour fournir plus d'informations.

Il est facile d'oublier ce que les requêtes de la semaine dernière ont été configurées pour faire. Dans ce cas, le nom de la requête a été modifié et une description a été ajoutée indiquant l'objet de la requête.

Si vous souhaitez en savoir plus sur les options disponibles, consultez cette présentation de l'éditeur Power Query .

Comment travailler avec plusieurs sources de données

L'éditeur Power Query propose deux méthodes principales pour combiner des données provenant de plusieurs sources : l'ajout et la fusion.

L'ajout joint les tables verticalement, en les empilant les unes sur les autres. La fusion vous permet de joindre des tables basées sur des colonnes communes.

Pour voir comment cela fonctionne, créez un deuxième petit fichier CSV avec la même structure que le premier. Cette fois, mettez deux noms (premier et dernier) dans le fichier :

• Joe, Blogs

• Laura, canne

Notez que l'un de ces noms est répété à partir du fichier précédent.

Suivez maintenant ces étapes pour extraire les données dans votre classeur Microsoft Excel.

1. Fermez la fenêtre de l'éditeur Power Query et enregistrez vos modifications.

2. Ajouter une nouvelle feuille de calcul.

3. Suivez les étapes de la section précédente pour importer le deuxième fichier.

4. Cliquez sur le bouton Transformer.

5. Renommez les en-têtes de colonne en « Prénom » et « Nom de famille ».

Le volet droit de la feuille de calcul Excel affiche les requêtes et connexions actuelles. Vous devriez maintenant voir deux requêtes dans le volet.

Comment ajouter des données avec Power Query

Dans cette section, vous allez ajouter le deuxième exemple de fichier CSV au premier.

Suivez ces étapes pour ajouter des données :

1. Cliquez avec le bouton droit sur la première table (Noms de clients) dans le volet Requêtes et connexions.

2. Choisissez "Modifier" pour ouvrir la table dans l'éditeur Power Query.

3. Cliquez sur Ajouter des requêtes dans la section Combiner.

4. Choisissez le deuxième tableau dans la liste déroulante des tableaux à ajouter.

Le résultat sera deux lignes supplémentaires dans le premier tableau.

Notez qu'une ligne est répétée - c'est ainsi que fonctionne l'opération d'ajout. Il ajoute les données d'une table dans une autre, que les données existent déjà ou non.

Vous pouvez supprimer les données en double dans l'éditeur Power Query en procédant comme suit :

1. Cliquez sur l'en-tête de colonne "Prénom".

2. Maintenez la touche "Ctrl" (ou la touche "Cmd" sur Mac).

3. Cliquez sur les deux en-têtes de colonne.

4. Cliquez sur "Supprimer les lignes" dans l'onglet Accueil du ruban de l'éditeur Power Query.

5. Sélectionnez "Supprimer les doublons" dans le menu déroulant.

Comment fusionner des données avec Power Query

Lorsque vous fusionnez des données, vous devez choisir les colonnes correspondantes entre les requêtes.

Supposons que vous disposiez de deux fichiers délimités par des virgules, l'un contenant les numéros de téléphone des clients et l'autre les adresses. Les deux fichiers ressemblent à ceci :

Suivez les instructions des sections précédentes pour charger chaque fichier dans sa propre requête de puissance. Ensuite, vous pouvez vous entraîner à fusionner les tables.

Dans le cas de nos exemples de données, les deux colonnes (Prénom et Nom) doivent être sélectionnées comme colonnes correspondantes. Maintenez la touche Ctrl enfoncée pour sélectionner plusieurs colonnes.

Suivez ces étapes:

1. Cliquez avec le bouton droit sur la première table du volet Requêtes et connexions.

2. Choisissez "Modifier" pour ouvrir la table dans l'éditeur Power Query.

3. Sélectionnez "Fusionner les requêtes" dans le groupe "Combiner".

4. Choisissez la deuxième table dans la liste déroulante des tables.

5. Cliquez sur la première colonne du premier tableau.

6. Maintenez la touche Ctrl enfoncée et cliquez sur la deuxième colonne du premier tableau.

7. Maintenez la touche Ctrl enfoncée et cliquez sur les deux colonnes du deuxième tableau.

8. Conservez l'opérateur de jointure par défaut sur "Left Outer".

9. Cliquez sur "OK" pour appliquer les modifications.

L'opérateur de jointure "Left Outer" garantit que les données en double sont éliminées.

Dans l'éditeur Power Query, vous verrez une nouvelle colonne nommée "Table" ou "Table imbriquée" contenant les données fusionnées de la deuxième requête.

1. Cliquez sur l'icône de développement (deux flèches) dans l'en-tête de cette colonne.

2. Décochez la case "utiliser la colonne d'origine comme préfixe" (cela évite les longs noms de colonnes).

3. Cliquez sur OK".

4. Cliquez sur "Fermer et charger".

Le tableau fusionné avait quatre colonnes, avec les détails du téléphone et de l'adresse fusionnés. Vous avez peut-être encore un peu de rangement à faire.

Par exemple, vous pouvez modifier le nom de la table (Merge1) et la colonne d'adresse dans l'image ci-dessous.

5 façons de transformer les données avec Power Query

La transformation des données dans votre fichier Excel est simplifiée avec Power Query, ce qui vous permet de nettoyer, d'organiser et de manipuler efficacement vos données.

Voici quelques-unes des façons les plus courantes de transformer des données :

• Développer et condenser les données

• Tri des données

• Le formatage des données

• Ajout et suppression de colonnes

• Modification des types de données

1. Développer et condenser les données

Il est important de développer et de condenser les données pour rendre vos ensembles de données plus faciles à gérer. Power Query vous permet de développer ou de réduire des tableaux pour une vue plus organisée.

La section précédente vous montre comment développer en incluant des données de tables associées.

Vous pouvez également le condenser pour n'inclure que des données résumées basées sur une agrégation de votre choix, telle qu'une somme, un nombre ou un pourcentage.

2. Tri des données

Pour trier les données, vous pouvez modifier rapidement l'ordre par valeurs croissantes ou décroissantes.

Cliquez sur la flèche vers le bas à côté d'un nom de colonne et choisissez "Trier par ordre croissant" ou "Trier par ordre décroissant".

3. Formatage des données

L'onglet Transformer de l'éditeur Power Query fournit de nombreuses options.

Par exemple, le menu déroulant Format vous permet de modifier toutes les données d'une colonne ou du tableau entier en majuscules ou en minuscules.

4. Ajouter et supprimer des colonnes

Power Query simplifie le processus d'ajout, de suppression et de modification de colonnes.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Par exemple, vous devrez peut-être convertir des décimales en pourcentages ou convertir des pourcentages textuels en pourcentages numériques pour faciliter le calcul et la comparaison.

Travailler avec des formules et le langage M

Lorsque vous utilisez Power Query dans Excel, vous rencontrerez le langage M, qui est au cœur de la création de formules avancées, de la manipulation de données et de la production de requêtes puissantes. Dans cette section, vous découvrirez :

• la barre de formule

• Code M

• les éléments essentiels de la syntaxe du langage M

Barre de formule

La barre de formule dans l'éditeur Power Query d'Excel est la zone où vous saisirez vos formules et votre code M.

Pour l'utiliser, accédez à l'éditeur Power Query en sélectionnant une requête dans le volet Requêtes et connexions. La barre de formule est située au-dessus du volet d'aperçu des données.

Écrire et modifier le code M

Pour créer des requêtes polyvalentes et puissantes dans Excel, vous devrez écrire et modifier le langage Power Query connu sous le nom de code M. Voici quelques conseils pour commencer :

• Utilisez le volet Étapes appliquées pour écrire du code étape par étape, ce qui facilite le suivi et la modification de la logique de votre requête.

• Combinez le code M avec un tableau Excel, des tableaux croisés dynamiques et des sources de données externes, comme Azure et des fichiers texte, pour créer des rapports dynamiques et automatisés.

• Ajustez les options de requête pour affiner vos préférences de chargement de données et améliorer les performances.

En vous familiarisant avec le code M, vous découvrirez que vous pouvez automatiser et optimiser de nombreux processus, rendant votre analyse de données dans Excel plus efficace et plus puissante.

Pour afficher le code derrière une requête, procédez comme suit :

1. Ouvrez la requête dans l'éditeur Power Query.

2. Cliquez sur "Éditeur avancé" dans l'onglet Accueil.

Voici le code derrière notre exemple de tableau fusionné (le vôtre peut sembler différent) :

Qu'est-ce que la syntaxe du langage M ?

Comprendre la syntaxe du langage M est crucial pour créer des formules Power Query efficaces. Gardez à l'esprit les points suivants :

• Le langage M est sensible à la casse, alors soyez prudent avec les majuscules.

• Utilisez des crochets [] pour référencer des colonnes, des enregistrements et des listes.

• Pour référencer une étape précédente dans votre requête, utilisez son nom de variable (par exemple, #"Nom de l'étape").

• Générez une étape de formule de requête avec l'instruction "in".

Au fur et à mesure que vous acquerrez de l'expérience avec le flux de langage Power Query , vous découvrirez de nouvelles façons de transformer et d'analyser vos données, faisant de vos classeurs Excel un atout encore plus précieux.

Regardez la vidéo ci-dessous, il est temps de sortir la brosse à récurer et de nettoyer vos données en désordre.

Comment actualiser les connexions de données

L'une des principales fonctionnalités de Power Query est la possibilité d'actualiser facilement votre connexion Power Query.

Lorsque vos sources de données ont été mises à jour et que vous avez besoin des dernières informations dans votre classeur, vous pouvez actualiser les connexions de données dans le volet Requêtes et connexions.

Si vous ne voyez pas le volet à droite de votre feuille de calcul, vous pouvez l'ouvrir en allant dans le tableau des données et en cliquant sur « Requêtes et connexions ».

Lorsque le volet Requêtes et connexions est ouvert, il répertorie toutes les requêtes. Suivez ces étapes:

1. Faites un clic droit sur la requête que vous souhaitez actualiser.

2. Sélectionnez Actualiser dans le menu contextuel.

Cela mettra à jour votre classeur avec les dernières données de vos sources de données connectées.

Comment intégrer Power Query au modèle de données

Le modèle de données d'Excel est une fonctionnalité puissante qui vous permet de créer des outils d'informatique décisionnelle, tels que des tableaux croisés dynamiques, basés sur des relations de données.

Le modèle de données est essentiellement une base de données cachée qui contient les données importées et les relations entre les tables.

Il fait partie de la fonctionnalité Power Pivot intégrée aux versions actuelles d'Excel. Vous devrez peut-être installer Power Pivot en tant que complément pour y accéder dans les versions précédentes).

Vous pouvez intégrer vos connexions de données Power Query au modèle de données en procédant comme suit :

1. Chargez vos données à l'aide de Power Query comme décrit dans les sections précédentes.

2. Au lieu de le charger directement dans le classeur, choisissez Charger dans dans le menu Charger.

3. Cochez la case Ajouter ces données au modèle de données dans la boîte de dialogue Importer des données.

Comment automatiser avec Power Query et VBA

Avec l'intégration VBA (Visual Basic pour Applications), vous pouvez créer des connexions de données personnalisées et automatiser les étapes de transformation des données dans Power Query.

Vous pouvez tirer parti de vos compétences VBA existantes pour créer des modèles de données avancés en incorporant des données provenant de plates-formes telles que Microsoft Access, SharePoint et Analysis Services.

Bien que Power Query n'offre pas de prise en charge native de VBA, vous pouvez utiliser le code VBA pour ouvrir, actualiser et manipuler indirectement les requêtes Power Query.

Cela vous permet de créer des workflows de données puissants et automatisés et de rationaliser votre processus d'analyse de données.

Voici un exemple de code qui actualise une requête avancée dans le classeur actuel.

:

Dim wb en tant que classeur, pq en tant qu'objet

Définir wb = ThisWorkbook

Set pq = wb.Queries("Noms des clients")

 pq.Actualiser

Voici le code dans une macro Excel VBA :

Fonctionnalités avancées de Power Query

Power Query d'Excel offre une pléthore de fonctionnalités avancées pour simplifier et améliorer votre processus d'analyse de données. En apprenant à utiliser efficacement ces fonctionnalités, vous pouvez amener vos compétences en analyse de données à un tout autre niveau.

Les outils d'analyse de données intégrés incluent :

• Colonnes conditionnelles pour appliquer une logique complexe à vos transformations de données.

• Fonctions de regroupement et d'agrégation pour résumer vos données de manière significative.

• Opérations Pivot et Unpivot pour remodeler vos données pour une analyse plus facile.

Vous trouverez bon nombre de ces fonctionnalités dans l'onglet Transformer de l'éditeur Power Query.

De plus, vous pouvez connecter Power Query à des outils de Business Intelligence comme LuckyTemplates pour des options d'analyse et de visualisation de données plus avancées .

Collaboration et partage

Le partage de vos connexions de données, requêtes et transformations avec les membres de votre équipe est essentiel pour une collaboration efficace.

Vous pouvez partager un classeur avec des requêtes en l'enregistrant sur un lecteur réseau, SharePoint ou un autre emplacement de stockage partagé.

Vous pouvez également exporter le code M derrière une seule requête en procédant comme suit :

1. Modifiez la requête dans l'éditeur Power Query.

2. Cliquez sur "Éditeur avancé" dans l'onglet Accueil.

3. Copiez l'intégralité du code M dans la fenêtre de l'éditeur avancé.

4. Ouvrez un éditeur de texte (par exemple, le Bloc-notes) et collez le code M copié dans l'éditeur de texte.

5. Enregistrez le fichier texte et envoyez-le à votre collègue.

Résumons ça

Avec un éventail de fonctionnalités et son interface conviviale, Power Query est un outil essentiel pour les utilisateurs d'Excel qui cherchent à travailler efficacement avec des données externes.

Cette fonctionnalité précieuse permet de rationaliser le processus de création de rapports, de nettoyage des données et d'analyses complexes sur vos tableaux de données. En utilisant

Power Query vous fait finalement gagner du temps et augmente l'efficacité lorsque vous travaillez avec des fichiers Excel.