4 Commandes Excel Power Query que vous devez savoir

Avez-vous entendu parler de Power Query dans Microsoft Excel, mais vous avez toujours pensé qu'il n'était destiné aux experts Excel? Permettez-moi de vous arrêter là-bas parce que, en fait, la requête de puissance est conçue pour être conviviale et, surtout, vous éviter beaucoup de tracas lors de l'organisation de vos données.

Ce guide est destiné aux lecteurs qui sont nouveaux dans Microsoft Excel ou Power Query. Les conseils ne vous obligent pas à connaître ou à utiliser un langage de formule de requête de puissance m, et ils offrent un bon point de départ pour connaître certaines des commandes que vous pouvez utiliser dans ce puissant outil de manipulation de données.

1

Les cellules divisées par délimiteur pour isoler les valeurs

Il existe de nombreuses façons d'Excel pour diviser les données en plusieurs colonnes, y compris l'outil de texte à colonnes, de remplissage flash et de fonctions intégrées. Cependant, la méthode la plus intuitive consiste à lancer l'éditeur de requête Power et à y effectuer l'opération.

Ensuite, après avoir collé l'URL dans le champ de texte dans la boîte de dialogue From Web et cliqué sur « OK », vous avez sélectionné le tableau dans le volet gauche du navigateur, et cliqué sur « Transformer les données ».

Lorsque la table a été chargée dans l'éditeur de requête de puissance, vous avez remarqué que la colonne de Span de carrière contient deux données – l'année chaque joueur a commencé sa carrière et la dernière année au cours de laquelle elle a joué – et vous voulez que ce soit dans des colonnes distinctes.

Pour ce faire, cliquez avec le bouton droit sur l'en-tête de la colonne, survolez « Split Column » et sélectionnez « par Delimiter ».

UN délimiteur est un caractère, un symbole ou un espace utilisé pour séparer les éléments d'une séquence.

À ce stade, l'éditeur de requête de puissance passe en revue les données de la colonne pour voir si elle peut détecter un délimiteur potentiel. Dans ce cas, il est identifié que chaque cellule contient un tableau de bord et suppose correctement que c'est le moment où la cellule doit être divisée. Cependant, vous pouvez sélectionner un délimiteur différent dans le premier menu déroulant si nécessaire.

Puisqu'il n'y a qu'un seul délimiteur dans chaque cellule de la colonne sélectionnée dans cet exemple, il n'est pas nécessaire d'apporter d'autres modifications aux options dans la boîte de dialogue, alors cliquez sur « OK » et voir les données nouvellement transformées dans l'éditeur de requête de puissance.

Pour renommer les nouvelles colonnes, double-cliquez sur les en-têtes de colonne et tapez les nouvelles étiquettes de données.

Maintenant, vous avez réussi à diviser les données à l'aide de l'éditeur de requête Power, mais avez-vous remarqué que les cellules des deux nouvelles colonnes sont alignées différemment? En effet, les valeurs de la colonne « First Play » sont tous des nombres (comme indiqué par l'icône 123 à côté de l'en-tête de colonne), mais comme les valeurs de la colonne « Last Play » incluent à la fois le texte et les nombres, Excel traite cela comme une colonne de texte (d'où l'ABC dans l'en-tête de colonne).

Pour résoudre ce problème, sélectionnez l'icône « ABC » dans la colonne « Last Play », puis cliquez sur « Numéro entier ».

Désormais, les deux colonnes sont formatées en ensembles de données numériques.

Cependant, parce que certaines cellules de la colonne « Last Play » contenaient des valeurs de texte, celles-ci affichent désormais comme des erreurs. Mais ne vous inquiétez pas – continuez la lecture pour savoir comment résoudre ce problème!

2

Remplacer les erreurs à utiliser dans les calculs

La requête Power d'Excel est un outil puissant pour traiter les erreurs qui apparaissent dans vos données.

Prenant la requête dans l'exemple précédent, certaines cellules de la colonne « Last Play » contiennent des erreurs, car elles contenaient auparavant la valeur de texte « présente » dans une colonne formatée sous forme de nombres entiers.

À l'heure actuelle, si vous cliquez sur « Fermez et chargez » dans le coin supérieur gauche de l'éditeur de requête Power, ces cellules avec des valeurs d'erreur seront vides dans le tableau résultant. Bien que cela puisse sembler idéal, les cellules vierges dans une colonne Excel peuvent causer des problèmes lorsque vous triagez et filtrez les données ou référençant des en-têtes de colonne en formules, il est donc préférable de les remplir de valeurs significatives.

Pour ce faire, cliquez avec le bouton droit sur l'en-tête de la colonne et sélectionnez «Remplacer les erreurs».

Vous pourriez être tenté de sélectionner « Supprimer les erreurs » dans le menu avec le bouton droit. Cependant, cela supprime toute la ligne, pas seulement l'erreur, donc cliquez sur cette option que si c'est ce que vous voulez arriver.

Maintenant, dans la boîte de dialogue Remplacer les erreurs, saisissez la valeur que vous souhaitez apparaître au lieu d'erreurs. Dans ce cas, puisque les erreurs sont apparues lorsque la cellule se lisait «présente», vous pouvez taper l'année en cours.

Lorsque vous cliquez sur « OK », toutes les valeurs d'erreur sont remplacées par cette nouvelle valeur.

Étant donné que toutes les cellules des deux colonnes les plus à droite contiennent toutes les mêmes types de données sans aucune erreur, vous pouvez créer une nouvelle colonne qui élabore le nombre total d'années de jeu pour chaque joueur.

Ici, j'ai cliqué sur la « colonne personnalisée » dans l'onglet Ajouter une colonne, j'ai changé le nom de la colonne en « Années jouées » et j'ai utilisé la liste des colonnes dans le menu des colonnes disponibles pour créer une soustraction simple.

Ensuite, lorsque j'ai cliqué sur « OK », cette nouvelle colonne – sans erreur – a été ajoutée au côté droit de la requête.

Lorsque vous avez terminé, cliquez sur l'icône « Fermer et charger » dans l'onglet Home de l'éditeur de requête Power pour envoyer la table à une nouvelle feuille de calcul dans votre classeur Excel.

3

Univot des données pour faciliter l'analyse

Lors de la création d'un nouvel ensemble de données dans Microsoft Excel, dans la mesure du possible, je suis le principe du champ d'enregistrement, où:

• Chaque ligne contient une collection de types de données apparentés mais différents (également appelés enregistrer), et

• Chaque colonne contient un seul type de données unique (également appelé champ) concernant chaque enregistrement.

Dans cet exemple simple, chaque pays est un record, et leurs continents, populations et devises sont des champs.

L'utilisation de ce format prépare les données pour une analyse plus approfondie, car vous pouvez facilement filtrer et trier les données, des en-têtes de colonne de référence dans les formules et visualiser les statistiques.

Comparez le tableau dans l'exemple ci-dessus à la capture d'écran suivante, où chaque colonne (champ) contient des types de données identiques.

En conséquence, il est impossible de filtrer les données par année, et vous ne pouvez pas facilement voir quelle année a été la plus rentable pour chaque entreprise. Pour faire ces analyses, vous devrez déboucher (ou aplatir) les données, qui consistent à convertir les données d'une table large en une longue table en transformant chaque année une colonne en une ligne. En d'autres termes, vous voulez que toutes les valeurs financières soient stockées en tant que champ unique.

Tout d'abord, sélectionnez n'importe quelle cellule dans les données et dans l'onglet Données du ruban, cliquez sur « From Table / Range ».

À ce stade, si vos données ne sont pas déjà formatées en tant que table Excel, vous verrez une boîte de dialogue vous incitant à résoudre ce problème. C'est ainsi que l'éditeur de requête Power peut lire vos données plus facilement.

Ensuite, dans l'éditeur de requête Power, sélectionnez les en-têtes de toutes les colonnes que vous souhaitez Univot. Dans cet exemple, vous devrez cliquer sur l'en-tête « 2020 », appuyez sur Shift, puis cliquez sur l'en-tête « 2024 ».

Maintenant, dans l'onglet Transformer sur le ruban, développez le menu déroulant « Univot Colonnes » et cliquez sur « Univot Sélectionnez les colonnes ».

Désormais, chaque colonne est un domaine distinct (entreprise, année et profit), et chaque ligne est un enregistrement détaillé de l'entreprise.

Enfin, double-cliquez sur les en-têtes de colonne pour les renommer afin qu'ils correspondent au champ qu'ils représentent, puis cliquez sur les icônes de format de numéro pour garantir que chaque colonne contient le type de données correct.

Maintenant, lorsque vous cliquez sur « Fermez et chargez » dans l'onglet Home sur le ruban, la table non ditée est envoyée à une nouvelle feuille de calcul dans votre classeur Excel.

En conséquence, vous pouvez effectuer des analyses de données qui étaient impossibles avant d'aplatissant le tableau, comme filtrant les données par année et par l'entreprise ou de trier les bénéfices de l'entreprise en ordre décroissant.

Après avoir ajouté plus de données à la table d'origine, comme une autre année de chiffres, rendez-vous au tableau produit précédemment par la requête d'alimentation, et dans l'onglet de requête, cliquez sur « Rafraîchir ». Excel ajoute ensuite les nouvelles données à la table résultante dans son format non diffusé, vous évitant de revenir à l'éditeur de requête de puissance à chaque fois.

4

Remplissez des cellules vierges basées sur la cellule ci-dessus (ou en dessous)

Comme je l'ai mentionné plus tôt, les cellules vierges dans un ensemble de données peuvent entraîner des problèmes lorsque vous triez et filtrez vos données ou utilisez des formules qui font référence aux en-têtes de colonne, c'est donc une bonne idée de les remplir.

Dans cet exemple, seul le premier joueur de chaque équipe se voit attribuer un numéro d'équipe dans la colonne A, donc si vous réorganisez les données, vous perdrez de vue quel joueur se trouve dans quelle équipe. En outre, actuellement, vous ne pouvez pas filtrer les données par numéro d'équipe.

Idéalement, les cellules A3 à A5 devraient contenir le nombre 1, les cellules A7 à A9 devraient contenir le numéro 2, etc. La saisie manuelle de ces chiffres serait longue, surtout si, comme dans cet exemple, il y a de nombreuses lignes dans l'ensemble de données. Au lieu de cela, utilisez Power Query pour remplir les cellules vierges en quelques secondes.

Tout d'abord, avec n'importe quelle cellule dans les données sélectionnées, dans l'onglet Données du ruban, cliquez sur « From Table / Range ».

Lorsque l'éditeur de requête de puissance s'ouvre, remarquez comment les cellules vierges de la colonne en question contiennent le mot «nul». Pour résoudre ce problème, sélectionnez la colonne en cliquant sur l'en-tête de la colonne, et dans l'onglet Transformer, cliquez sur « Remplir ».

Ensuite, choisissez si vous souhaitez remplir les données vers le bas ou vers le haut. Cliquez sur « Down » trouve une cellule dans la plage sélectionnée contenant une valeur et remplit toutes les cellules vides en dessous de la même valeur. D'un autre côté, cliquer sur « Up » remplit des cellules vierges au-dessus d'une cellule contenant une valeur. Dans ce cas, car seule la première ligne de chaque équipe contient un nombre, vous devez remplir vers le bas.

Maintenant, chaque joueur se voit correctement attribué un numéro d'équipe, vous pouvez donc cliquer sur « Fermer et charger » dans l'onglet Home pour envoyer la requête à une nouvelle feuille de calcul.

Grâce à ce changement rapide mais important, vous pouvez trier et filtrer l'ensemble de données, en toute sécurité en sachant que vous ne perdrez pas la trace du joueur dans chaque équipe.

---

Si vous avez plusieurs tables dans des feuilles de calcul Excel distinctes, à condition qu'ils aient les mêmes en-têtes de colonne, vous pouvez utiliser Power Query pour empiler les données dans une seule table. Cela implique l'étape supplémentaire (mais simple) de créer des connexions de données entre chaque tableau, avant de combiner les requêtes dans l'éditeur de requête de puissance.