Excel 2013 : Analyse de données avec Power Query

Créer des requêtes paramétrables

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Dans cette vidéo, votre formateur vous explique comment tirer parti de cellules nommées pour lancer des requêtes dynamiques.
08:31

Transcription

Pour clore cette formation, nous allons aborder deux derniers sujets. Dans un premier temps, on va maintenant s'intéresser aux paramètres dans Power Query. Il faut savoir qu'il y en a deux de types de paramètres et, comme c'est une formation sur les fondamentaux, on ne va focaliser que sur l'un de ces deux types de paramètres. Alors d'abord, permettez-moi de définir de quoi il s'agit. Un paramètre dans Power Query, c'est une sorte de variable mathématique qu'on peut utiliser dans d'autres requêtes ou d'autres tables pour définir des filtres, par exemple, des groupements ou le chemin de dossiers où se trouvent des fichiers à fusionner. Ça évite d'aller changer une information Il suffit de changer la valeur de la variable pour que ça puisse changer 5, 10, 30, 40 requêtes en même temps. Ça c'est l'idée générale de paramètre. Maintenant, nous, le type de paramètre auquel on va s'intéresser, c'est le plus simple et le plus demandé à la fois, c'est comment faire en sorte que nos collègues, via une feuille Excel, puissent changer la valeur d'une cellule et faire en sorte que cette nouvelle valeur soit prise en compte dans des filtres ou dans des groupements ou dans des chemins de dossiers et autres. Alors, voyons comment procéder. Considérons notre table, ici, MDBVentes. Donc je vous rappelle, c'est une table Access d'à peu près 12 000 000 de lignes. Actuellement oui, je peux la filtrer, par exemple, par article et dire je veux que les IBM 500 mais je dois passer par Power Query pour filtrer. Or il serait plus intéressant de pouvoir filtrer directement en utilisant une cellule Excel, donc voyons comment procéder. On va fermer Power Query. On va garder la modification qu'on vient de faire et on va créer une nouvelle feuille. Je vais l'appeler Paramètres, toujours en respectant la nomenclature de least is it read x, c'est-à-dire on ne met pas d'accent. Maintenant attention, l'exemple que je vais faire, c'est le minimum syndical en terme d'esthétique. Donc là on va d'abord mettre le nom du critère, Article et, par exemple, la valeur qui nous intéresse, IBM 500. On sélectionne le nom du paramètre et, en dessous sa valeur, mais sachez que là-haut je pourrais mettre Toto, ça n'a aucune importance et on va aller cliquer dans l'onglet Power Query sur On clique dessus, il détecte la zone, on fait un Ok et on sait que, comme à l'habitude, il va charger un tableau que l'on va tout de suite s'empresser de renommer, Parametre_ Filtre Article. Donc, une fois quand même ce tableau renommé, maintenant allons-y. Il faut, pour que ce tableau devienne un paramètre, donc un paramètre dynamique, puisque c'est lié à une cellule Excel, aller dans le langage M de la requête parce que, oui, pour info, le langage M n'est pas que pour les fonctions de calcul et de traitement de texte et ainsi de suite mais c'est aussi le langage des requêtes de Power Query. Donc, dans l'onglet Accueil ou dans l'onglet Affichage, il y a bouton Editeur avancé et ce que vous voyez là, c'est le langage M et, avec le temps, vous pouvez apprendre à l'utiliser en attendant qu'il y ait une formation sur le sujet. Là, ce que l'on va faire, c'est qu'on va modifier cette ligne 2, voilà, on va l'enlever, donc c'est plus qu'une modification et, à la place, on va mettre une commande. On va dire je veux récupérer, sous une variable qui s'appellera FiltreArticle, là je peux l'appeler comme je veux, la valeur de l'enregistrement et d'un champ donné de la table qui est derrière, via la fonction Record.Field que Microsoft met à disposition. Donc, attention, Record c'est le concept de ligne et Field, c'est parce qu'on va devoir lui dire quelle colonne. Et là, en ouvrant la parenthèse, on va lui dire de la source puisque vous voyez qu'ici la source c'est le tableau, eh bien de la source on veut la ligne 0. Donc, ce que l'on fait, c'est qu'on ouvre une accolade juste après le mot source et on va dire qu'on veut la ligne 0. Donc attention, la ligne 0 c'est en fait la ligne 1 parce que le langage M fonctionne comme on le fait en mathématiques, c'est-à-dire la première ligne c'est 0, ensuite on met entre guillemets le nom de la colonne où se trouve la valeur qu'on veut récupérer, donc la colonne là-haut, comme vous pouvez le voir, c'est Article, et ensuite on remet en bas, comme ceci, la variable qui doit être renvoyée. On valide par Terminer et, comme vous pouvez le voir ici, l'affichage change totalement et, maintenant, ici on a donc un visuel de type ABC. Donc, bien évidemment attention, si vous travaillez avec des dates, faites attention de bien avoir d'abord choisi le format Date et ainsi de suite. Donc vous n'aurez pas forcément le symbole ABC. Si c'est une date, vous aurez le symbole d'un calendrier et ainsi de suite. Maintenant, déjà, voyons si la liaison avec la cellule fonctionne bien. Si on ferme Power Query puis qu'on fait Conserver, on sait ce qui va se passer. Il va créer une nouvelle feuille avec la table qu'on ne va pas garder, voilà, donc on sait que j'aurais pu fermer Power Query autrement. Je vais désactiver le chargement et, là, je change le nom de l'article, AST Intel 150. Maintenant, si on retourne dans Power Query, lançons l'Editeur, et qu'on va revoir ici ce paramètre, on voit qu'il est effectivement passé à AST Intel 150, là j'ai oublié un L, je vais juste rajouter le L mais déjà cela démontre que ça marche, donc ça c'est une chose claire. Donc si on y retourne, voilà, c'est tout bon. Maintenant, ce nom de table qui est ici, je vais le copier, je vais renommer Ctrl C et puis on va aller dans notre table MDBVentes qui actuellement est filtrée sur un paramètre statique, ça veut dire que nos collègues devraient aller systématiquement dans Power Query pour changer le filtre. Eh bien maintenant observez, si on va dans l'onglet Accueil ou à nouveau dans l'onglet Affichage et que l'on clique sur l'Editeur avancé, en lisant le code M qu'il y a ici, on se rend compte qu'il y a une ligne qui s'appelle Lignes filtrées avec IBM 500 écrit en dur et, là, on va le remplacer par le nom de notre paramètre. On valide par Terminé et, comme pouvez le voir, ça a automatiquement changé le filtrage de la table. Alors maintenant il y a une autre manière de tester que tout ça fonctionne c'est que, si cette requête actuellement, bon là je ferme et je charge, c'est clair que je ne vois pas la table dans Excel, mais imaginons que j'aimerais cette table, on ne pourrait pas la voir de toute façon en entier puisque, on le sait, elle fait plus de 12 000 000 de lignes. On peut avoir donc, quand même, le résultat. On va voir comment procéder, je crée une nouvelle feuille. On va monter là où il y a la table MDBVentes, j'essaye de la glisser, je ne peux pas, embêtant. Là, si on fait un Clic droit, on voit qu'on a une option Charger dans, on prend Charger dans puis on dit en tant que Table, Charger, et puis là, c'est bon. En fait il crée carrément une nouvelle feuille, donc c'était pas nécessaire de créer une feuille. Vous allez me dire mais pourquoi vous faites ça ? Eh bien pourquoi est-ce que je fais ça, c'est très simple, c'est que si maintenant je vais changer ici mon paramètre à IBM 500, comme ceci, et que je vais là dans Feuille 12, comme vous pouvez le voir, c'est toujours sur AST Intel 150 mais si on rafraîchit cette table qui vient de Power Query, on n'oublie pas que les tables on peut en faire des tableaux croisés dynamiques, des graphiques pour voir View donc on peut faire du Power Pivot etc mais si j'actualise, comme vous pouvez le voir, en fait qu'est-ce que fait Power Query, il met à jour le paramètre qu'il y a dans Power Query en allant lire ce qu'il y a là et donc, pour le coup, il met à jour en cascade cette table, donc ça c'est une démonstration parfaite comme quoi le système marche. Par contre, évidemment, l'usage final est un peu décevant parce qu'on n'a pas de tableau croisé dynamique, on n'a pas de graphique, on n'a pas, comme c'est le cas dans les entreprises d'administration, une feuille avec plein de paramètres, donc là on n'en a qu'un. Le paramètre, en plus, la manière de le saisir est un peu moche, on n'a pas fait de liste déroulante mais, ensuite, il n'y a plus qu'à, il faut qu'on, comme on dit souvent, donc vous faites de l'esthétique, vous créez des graphiques à partir des tables qui viennent de Power Query. Donc, voilà, concernant ce sujet des paramètres et qui nous montre à nouveau l'importance du langage M dans Power Query. à plusieurs endroits en même temps. A partir d'un tableau/d'une plage.

Excel 2013 : Analyse de données avec Power Query

Analysez des données avec Excel 2013 et Power Query. Apprenez à importer, consolider, lier, nettoyer, fusionner ou scinder des données provenant de différentes sources.

2h53 (34 vidéos)
Aucun commentaire n´est disponible actuellement
 

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !