Comment utiliser Power Query dans Excel?
Excel Power Query est utilisé pour rechercher des sources de données, établir des connexions avec des sources de données, puis mettre en forme les données en fonction de nos besoins d'analyse. Une fois que nous avons fini de façonner les données en fonction de nos besoins, nous pouvons également partager nos résultats et créer divers rapports en utilisant plus de requêtes.
Pas
Fondamentalement, il y a 4 étapes, et l'ordre de ces 4 étapes dans Power Query est le suivant:
- Se connecter: nous nous connectons d'abord aux données, qui peuvent être quelque part, dans le cloud, en service ou localement.
- Transformer: La deuxième étape serait de changer la forme des données selon les besoins de l'utilisateur.
- Combiner: dans cette étape, nous exécutons certaines étapes de transformation et d'agrégation et combinons les données des deux sources pour produire un rapport combiné.
- Gérer: cela fusionne et ajoute des colonnes dans une requête avec des colonnes correspondantes dans d'autres requêtes du classeur.
Il existe de nombreuses fonctionnalités très puissantes d'Excel Power Query.
Supposons que nous ayons des données d'achat pour les 15 dernières années dans 180 fichiers. Désormais, la gestion d'une organisation nécessiterait de consolider les chiffres avant de les analyser. La direction peut utiliser l’une des méthodes suivantes:
- Ils ouvriraient tous les fichiers et les copier-coller dans un seul fichier.
- D'autre part, ils peuvent utiliser une solution judicieuse, qui consiste à appliquer des formules, car elle est sujette à l'erreur.
Quelle que soit la méthode choisie, elle contient beaucoup de travail manuel, et après quelques mois, il y aurait de nouvelles données de ventes pour la durée supplémentaire. Ils devront refaire le même exercice.
Cependant, Power Query peut les aider à ne pas effectuer ce travail fastidieux et répétitif. Comprenons cette requête de puissance Excel avec un exemple.
Exemple
Supposons que nous ayons des fichiers texte dans un dossier contenant des données de vente et que nous souhaitons obtenir ces données dans notre fichier Excel.
Comme nous pouvons le voir dans l'image ci-dessous, nous avons deux types de fichiers dans le dossier, mais nous voulons obtenir les données des fichiers texte uniquement dans le fichier Excel.
Pour faire de même, les étapes seraient:
Étape 1: Tout d'abord, nous devons obtenir les données dans Power Query afin de pouvoir apporter les modifications nécessaires aux données pour les importer dans un fichier Excel.
Pour faire la même chose, nous choisirons le « De dossier » option dans le « From File » le menu après avoir cliqué sur la commande « Get Data » de la « Get & Transform » groupe dans le « Data » onglet.
Étape 2: Sélectionnez l'emplacement du dossier en naviguant.
Cliquez sur 'OK'
Étape 3: Une boîte de dialogue s'ouvrira contenant la liste de tous les fichiers dans le dossier sélectionné avec les en-têtes de colonne comme «Contenu», «Nom», «Extension», «Date d'accès», «Date de modification», «Date de création», "Attributs" et "Chemin du dossier".
Il existe 3 options, à savoir, Combiner , Charger et Transformer les données .
- Combiner : Cette option est utilisée pour accéder à un écran où nous pouvons choisir les données à combiner. L'étape d'édition est ignorée pour cette option et ne nous donne aucun contrôle sur les fichiers à combiner. La fonction de combinaison prend chaque fichier du dossier à consolider, ce qui peut entraîner des erreurs.
- Charger: Cette option ne chargera que le tableau comme affiché ci-dessus dans l'image dans la feuille de calcul Excel au lieu des données réelles dans les fichiers.
- Transformer les données: Contrairement à la commande 'Combiner' , si nous utilisons cette commande, nous pouvons choisir les fichiers à combiner, c'est-à-dire que nous ne pouvons combiner qu'un seul type de fichier (même extension).
Comme dans notre cas, nous voulons combiner uniquement des fichiers texte (.txt); nous choisirons la commande «Transformer les données» .
Nous pouvons voir «Étapes appliquées» sur le côté droit de la fenêtre. Pour l'instant, il n'y a qu'une seule étape qui consiste à extraire les détails des fichiers du dossier.
Étape 4: Il y a une colonne nommée «Extension» où nous pouvons voir que les valeurs de la colonne sont écrites dans les deux cas, c'est-à-dire en majuscules et minuscules.
Cependant, nous devons convertir toutes les valeurs en minuscules car le filtre différencie les deux. Pour faire de même, nous devons sélectionner la colonne puis choisir «Minuscules» dans le menu de la commande «Format» .
Étape 5: Nous filtrerons les données en utilisant la colonne «Extension» pour les fichiers texte.
Étape 6: Nous devons maintenant combiner les données des deux fichiers texte en utilisant la première colonne «Contenu». Nous cliquons sur l'icône placée à droite du nom de la colonne.
Étape 7: Une boîte de dialogue sous-titrée `` Combiner les fichiers '' s'ouvrira où nous devons sélectionner le délimiteur comme `` Tab '' pour les fichiers texte (fichiers avec l'extension `` .txt '') et pouvons choisir la base pour la détection du type de données. Et cliquez sur «OK».
Après avoir cliqué sur «OK», nous obtiendrons les données combinées des fichiers texte dans la fenêtre «Power Query» .
Nous pouvons changer le type de données des colonnes selon les besoins. Pour la colonne "Revenu" , nous allons changer le type de données en "Devise".
Nous pouvons voir les étapes appliquées aux données à l'aide d'une requête d'alimentation sur le côté droit de la fenêtre.
Après avoir effectué toutes les modifications requises dans les données, nous pouvons charger les données dans une feuille de calcul Excel à l'aide de la commande «Fermer et charger vers» sous le groupe «Fermer» de l' onglet «Accueil» .
Nous devons choisir si nous voulons charger les données en tant que table ou connexion. Cliquez ensuite sur «OK».
Nous pouvons maintenant voir les données sous forme de tableau dans la feuille de calcul.
Et le volet "Requêtes de classeur" sur le côté droit, que nous pouvons utiliser pour modifier, dupliquer, fusionner, ajouter les requêtes et à de nombreuses autres fins.
Excel Power Query est très utile car nous pouvons voir que 601 612 lignes ont été chargées en quelques minutes.
Choses dont il faut se rappeler
- Power Query ne modifie pas les données source d'origine. Au lieu de modifier les données source d'origine, il enregistre chaque étape prise par l'utilisateur lors de la connexion ou de la transformation des données, et une fois que l'utilisateur a terminé la mise en forme des données, il prend l'ensemble de données raffiné et l'amène dans le classeur.
- Power Query est sensible à la casse.
- Lors de la consolidation des fichiers dans le dossier spécifié, nous devons nous assurer que l'utilisation de la colonne 'Extension', et nous devons exclure les fichiers temporaires (ayant l'extension '.tmp' et le nom de ces fichiers commence par le signe '~') comme Power Query peut également importer ces fichiers.
