Qu'est-ce qu'Excel Power Query et pourquoi l'utiliser ?
Excel Power Query, également appelé "Obtenir et transformer" dans les versions récentes d'Excel, est un outil puissant d'extraction, transformation et chargement (ETL) de données. Il permet d'importer des données depuis une grande variété de sources, de les nettoyer, de les transformer et de les charger dans Excel pour analyse et reporting.
Les avantages de Power Query
- Automatisation des tâches répétitives : Automatisez l'importation et la transformation de données, ce qui vous fait gagner un temps considérable.
- Connexion à des sources de données multiples : Importez des données depuis des fichiers Excel, CSV, TXT, bases de données (SQL Server, Access, Oracle…), pages web, services en ligne (SharePoint, Salesforce…) et bien plus encore.
- Transformation des données sans programmation : Utilisez une interface conviviale pour filtrer, trier, supprimer des colonnes, remplacer des valeurs, fractionner des colonnes, ajouter des colonnes calculées et effectuer d'autres transformations sans avoir à écrire de code VBA.
- Consolidation de données : Combinez des données provenant de différentes sources en une seule table.
- Actualisation automatique des données : Actualisez vos données en un clic pour obtenir les dernières informations.
- Visualisation des données : Chargez vos données transformées dans Excel pour créer des tableaux croisés dynamiques, des graphiques et des rapports.
Comment accéder à Power Query dans Excel ?
L'accès à Power Query dépend de votre version d'Excel :
- Excel 2010 et 2013 : Vous devez télécharger et installer l'add-in Power Query depuis le site de Microsoft.
- Excel 2016 et versions ultérieures : Power Query est intégré à Excel sous l'onglet "Données" dans le groupe "Obtenir et transformer les données".
Localisation des commandes Power Query
Une fois Power Query installé ou intégré, vous trouverez les commandes principales dans l'onglet "Données" :
- Obtenir des données : Permet de se connecter à différentes sources de données.
- Requêtes et connexions : Affiche la liste des requêtes Power Query existantes.
- Actualiser tout : Actualise toutes les requêtes Power Query.
Importer des données avec Power Query : Un exemple concret
Prenons un exemple simple : importer des données depuis un fichier CSV contenant une liste de clients.
- Ouvrez Excel et créez un nouveau classeur.
- Allez dans l'onglet "Données" et cliquez sur "Obtenir des données" > "À partir d'un fichier" > "À partir d'un fichier texte/CSV".
- Sélectionnez le fichier CSV contenant vos données clients.
- La fenêtre Power Query s'ouvre. Vous pouvez prévisualiser les données et choisir le délimiteur (par exemple, la virgule).
- Cliquez sur "Transformer les données" pour ouvrir l'éditeur Power Query.
L'éditeur Power Query : Votre atelier de transformation
L'éditeur Power Query est l'endroit où vous allez effectuer toutes les transformations sur vos données. Il se compose de plusieurs éléments :
- Le ruban : Contient les commandes pour effectuer les transformations.
- Le volet "Requête" : Affiche la requête en cours et les étapes appliquées.
- Le volet "Aperçu des données" : Affiche un aperçu des données.
- Le volet "Paramètres de la requête" : Permet de modifier les paramètres de la requête.
Transformer les données : Nettoyage et mise en forme
Dans l'éditeur Power Query, vous pouvez effectuer diverses transformations pour nettoyer et mettre en forme vos données. Voici quelques exemples :
- Supprimer les lignes vides ou les lignes d'en-tête inutiles : Utilisez la commande "Supprimer les lignes".
- Modifier le type de données d'une colonne : Cliquez sur l'icône à gauche du nom de la colonne et choisissez le type de données approprié (texte, nombre, date…).
- Remplacer des valeurs : Utilisez la commande "Remplacer les valeurs" pour corriger les erreurs ou uniformiser les données.
- Fractionner une colonne : Utilisez la commande "Fractionner la colonne" pour diviser une colonne en plusieurs colonnes en fonction d'un délimiteur.
- Ajouter une colonne calculée : Utilisez la commande "Ajouter une colonne" > "Colonne personnalisée" pour créer une nouvelle colonne à partir d'une formule.
Exemple : Remplacer les valeurs nulles par "Inconnu" dans la colonne "Ville".
- Sélectionnez la colonne "Ville".
- Cliquez sur "Remplacer les valeurs".
- Dans la boîte de dialogue, entrez "null" dans le champ "Valeur à rechercher" et "Inconnu" dans le champ "Remplacer par".
- Cliquez sur "OK".
Charger les données dans Excel
Une fois que vous avez terminé de transformer vos données, vous pouvez les charger dans Excel.
- Cliquez sur "Fermer et charger" dans le ruban.
- Choisissez l'emplacement où vous souhaitez charger les données : une nouvelle feuille de calcul, une feuille de calcul existante ou un tableau croisé dynamique.
Vos données transformées sont maintenant dans Excel, prêtes à être analysées et visualisées.
Exemples d'utilisation avancée de Power Query
Power Query ne se limite pas à l'importation et à la transformation de données simples. Voici quelques exemples d'utilisation plus avancée :
Combiner plusieurs fichiers CSV dans un seul tableau
Imaginez que vous avez plusieurs fichiers CSV contenant des données de ventes mensuelles. Vous pouvez utiliser Power Query pour les combiner en un seul tableau.
- Allez dans l'onglet "Données" et cliquez sur "Obtenir des données" > "À partir d'un dossier".
- Sélectionnez le dossier contenant les fichiers CSV.
- Cliquez sur "Transformer les données".
- Dans l'éditeur Power Query, cliquez sur "Combiner les fichiers" (généralement une option en haut).
- Power Query va automatiquement détecter la structure des fichiers CSV et les combiner en un seul tableau.
Extraire des données d'une page web
Vous pouvez utiliser Power Query pour extraire des données d'une page web. Par exemple, vous pouvez extraire un tableau de données financières d'un site web.
- Allez dans l'onglet "Données" et cliquez sur "Obtenir des données" > "À partir du web".
- Entrez l'URL de la page web.
- Power Query va analyser la page web et afficher les tables disponibles.
- Sélectionnez la table que vous souhaitez importer.
Créer des colonnes conditionnelles
Vous pouvez créer des colonnes conditionnelles en fonction de la valeur d'autres colonnes. Par exemple, vous pouvez créer une colonne "Catégorie" basée sur la valeur de la colonne "Montant des ventes".
- Dans l'éditeur Power Query, cliquez sur "Ajouter une colonne" > "Colonne conditionnelle".
- Définissez les conditions et les valeurs correspondantes.
Bonnes pratiques et astuces pour Power Query
- Nommez vos requêtes de manière descriptive : Cela vous aidera à les identifier facilement plus tard.
- Appliquez les transformations dans l'ordre logique : Commencez par les transformations les plus générales et terminez par les transformations les plus spécifiques.
- Utilisez les commentaires pour documenter vos requêtes : Cela facilitera la compréhension de vos requêtes par d'autres personnes.
- Testez vos requêtes régulièrement : Assurez-vous que vos requêtes fonctionnent correctement et qu'elles produisent les résultats attendus.
- Utilisez les paramètres : Les paramètres vous permettent de rendre vos requêtes plus flexibles et réutilisables.
Erreurs courantes et comment les éviter
- Erreur de type de données : Assurez-vous que le type de données de chaque colonne est correct. Power Query peut parfois deviner incorrectement le type de données.
- Erreur de connexion à la source de données : Vérifiez que vous avez les droits d'accès à la source de données et que les paramètres de connexion sont corrects.
- Erreur de transformation : Vérifiez que les transformations que vous appliquez sont valides et qu'elles ne provoquent pas d'erreurs.
- Erreur d'actualisation : Vérifiez que la source de données est toujours disponible et que les données n'ont pas été modifiées de manière incompatible.
En conclusion, Excel Power Query est un outil indispensable pour tous ceux qui travaillent avec des données dans Excel. En maîtrisant Power Query, vous pouvez automatiser vos tâches, gagner du temps et obtenir des informations plus précises à partir de vos données.