Le 14 septembre 2017, nous avons publié une version actualisée de notre Politique de confidentialité. En utilisant video2brain.com vous vous engagez à respecter ces documents mis à jour. Veuillez donc prendre quelques minutes pour les consulter.

Excel 2016 : 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.
09:33

Transcription

Pour clore cette formation dans ce dernier chapitre nous allons dans un premier temps parler des paramètres. Alors qu'est-ce qu'un paramètre dans Power Query ? Vous pouvez vous imaginer que plusieurs tables et requêtes que vous avez dans Power Query dépendent d'une même valeur. Par exemple, imaginez que vous avez plein de tables avec des articles et que presque toutes les tables sont filtrées avec le ou les mêmes articles. Ce serait évidemment très pénible sur, on va dire soixante tables, à chaque fois d'aller changer la valeur du filtre des soixante tables. Donc il doit y avoir un moyen peut-être de définir ce qu'on appelle un paramètre que l'on appellerait en mathématiques une variable à laquelle on peut associer une valeur que l'on peut réutiliser soixante fois. Ce qui fait qu'à l'avenir si les critères du filtre changent, il n'y a pas besoin d'aller changer les soixante tables au niveau du filtre mais juste de changer une fois la valeur du paramètre. Et bien c'est ce qu'on va faire maintenant, mais dans un cas réduit puisque c'est une formation sur les fondamentaux. Il y a pour information deux types de paramètres dans Power Query et on va s'intéresser aux paramètres dits dynamiques. Qu'est-ce qu'un paramètre dynamique ? Je vais vous expliquer. Considérons par exemple ici notre table MDBVentes. Donc je vous rappelle que c'est une table Access avec environ douze millions de lignes que l'on avait importées. Si on va regarder, il y a une colonne article et voilà je peux tout à fait ici filtrer et dire je veux voir que les IBM 500. Donc là pour le coup, cette table elle est filtrée en dur. Ça veut dire que si on veut aller changer le filtre, il faut retourner dans Power Query pour aller le changer. Mais maintenant considérez que si je ferme Power Query je souhaiterais, donc là il génère juste la feuille correspondant à la table, voilà, je souhaiterais avoir une feuille ici que mes collègues peuvent accéder et dans laquelle il y a une cellule où ils peuvent changer la valeur du filtre. Alors allons-y. Pour ceci on va créer une feuille qu'on va arbitrairement nommer Paramètres. Et alors là pour la façon dont le paramètre apparaît et est saisi franchement on va faire le minimum syndical. Donc pas de liste déroulante, pas de slicer, pas de formatage particulier. Donc d'abord je vais donner un intitulé à mon paramètre, donc Articles au pluriel ou Article au singulier peu importe ça n'a aucune importance : on pourrait appeler ça toto aussi et en-dessous la valeur que je souhaiterais utiliser en premier. Ensuite on sélectionne ceci. Et puis on va dans l'onglet Données et on va dire qu'on veut une nouvelle requête à partir d'un tableau. Donc c'est le bouton qui est ici. On clique dessus, il va nous demander : est-ce que j'ai bien détecté la zone, est-ce que le tableau comporte des en-têtes ? On va dire effectivement c'est le cas, on fait un Ok. Il charge pour le coup la table dans Power Query et évidemment il le met dans le dossier Autres requêtes et on va tout de suite renommer la table : on va l'appeler Paramètre FiltreArticle. Bien évidemment, quand vous commencez à avoir beaucoup de paramètres, je vous recommande de créer un groupe au même titre qu'on l'avait fait pour les tables et les requêtes que vous appelez paramètres. Ceci dit c'est bien joli mais maintenant, qu'est-ce qu'on fait avec ça ? Et bien c'est une table ce truc et il nous faut un paramètre qui soit en plus dynamique. Et bien dans l'onglet Accueil ou dans l'onglet Affichage il y a un bouton qui s'appelle l'Éditeur avancé. Et là ce que vous voyez à l'écran c'est ce que l'on appelle le langage M de la requête, c'est-à-dire que les requêtes ne sont pas en SQL et toutes les étapes que vous avez faites dans le panneau de droite donc qui est ici, et bien se retrouvent là-dedans en tant que langage M. Donc avec le temps, à force de lire vous pouvez l'apprendre. Maintenant nous ce que l'on veut, c'est quoi ? C'est en fait dire à Power Query que la valeur qu'il y a dans cette ligne doit être récupérée en tant que paramètre pur. Alors on y va. D'abord on donne un nom à la valeur qu'on veut récupérer, je l'appelle FiltreArticle mais je pourrais l'appeler TotoArticle, = , et Microsoft met à disposition une commande qui s'appelle Record Field, qui veut dire Enregistrement champ, l'idée étant pour un enregistrement donné et un champ donné de récupérer sa valeur. Donc là, on va lui dire on veut récupérer l'enregistrement n°1 et sachant que c'est quand même un outil d'ingénierie, on commence à compter à partir de zéro comme c'est souvent le cas dans de nombreux langages de programmation et outils scientifiques. Donc zéro voudra dire ligne 1 et ensuite virgule le nom de la colonne donc la colonne s'appelle Article et on renvoie le tout sous le nom FiltreArticle. On peut maintenant terminer cette modification. Et comme vous pouvez le remarquer le visuel ici change considérablement, on n'a plus de tables ni de requêtes, rien, et là on a simplement un ABC alors attention ça ne veut pas dire le ABC que c'est un paramètre, ça veut dire que c'est un paramètre de type texte parce qu'évidemment si j'avais fait le même exemple avec une date on aurait un calendrier ici pour le coup plutôt qu'un ABC. Maintenant si je ferme Power Query, je vais dire Conserver, il va comme à l'habitude créer une feuille inutilement, ça on le sait, on sait qu'on pourrait fermer Power Query sans avoir cette feuille mais déjà là si je change la valeur du paramètre je mets AST Intel 150 par exemple et qu'on retourne dans Power Query donc on va dans Données, Afficher les requêtes donc qui aurait activé le panneau de droite si c'était déjà pas le cas et qu'on fait un clic droit Modifier et bien comme vous pouvez le voir il a mis à jour AST Intel 150. Donc c'est bien un paramètre dynamique parce que si on change la valeur dans la cellule Excel, ça change la valeur dans Power Query. Mais maintenant on aimerait utiliser ça dans une requête ou dans des requêtes ou dans des tableaux, pour des files, des regroupements, des chemins de dossiers, de fichiers parce que, n'oubliez pas, dans la cellule qui est derrière dans Excel on pourrait tout à fait demander le nom d'un dossier à nos collègues. Bon maintenant, venons-en à nos moutons. Comment va-t-on pouvoir injecter ceci dans un filtre d'une requête ? Et bien d'abord vous allez copier le nom ici de votre paramètre, vous allez ensuite dans votre requête ou votre table peu importe et puis vous allez à nouveau afficher le langage M de cette requête donc soit vous allez dans Affichage, Éditeur avancé soit Accueil, Éditeur avancé donc comme souvent les boutons, les mêmes, sont à deux endroits en même temps. Éditeur avancé et là qu'est-ce que l'on voit c'est qu'il y a effectivement ici IBM 500 que l'on avait mis à la main au tout début de cette vidéo. Ça on va le remplacer par la valeur du paramètre, donc voyez que j'écrase juste ce qui était écrit avant entre guillemets et en cliquant sur Terminer, ça devient pour le coup un paramètre dynamique. D'ailleurs vous pouvez voir il vient de mettre à jour ici cette table et son filtre parce que dans Excel derrière c'est de l'AST Intel 150. Maintenant je peux fermer Power Query. Conserver. On peut bien évidemment s'amuser à carrément le visualiser dans Excel ce qui vient de se passer actuellement. Donc là si on essaye d'accéder à notre table Access, j'ai un problème d'affichage que je vais essayer de résoudre parce que des fois la barre de défilement ici disparaît. Et on va prendre la table MDBVentes qui a ici un petit souci parce que voilà Power Query dans Excel 2016, c'est pas encore tout à fait ça. Et je vais faire un clic droit Charger dans en tant que table. Donc là on charge évidemment une partie de la table donc on voit que c'est avec AST Intel 150 bon on sait déjà qu'il pourra pas charger les douze millions. On est au courant de ceci et puis on va aller changer le paramètre. Donc là dans Paramètres on va mettre IBM 500 comme ceci. Je retourne là dans ma feuille et mon idée c'est de mettre à jour la requête, donc on fait un clic droit Actualiser. On va voir s'il va arriver à la mettre à jour ou pas, mais normalement s'il y arrive il va changer tous les articles qu'on a en visuel ici par des IBM 500 mais je vous garantis que ça marche lorsque ça bugge pas et ça plante pas mais là je sais pas ce qui va se passer. Je ne sais pas s'il va arriver jusqu'au bout. Ceci étant dit, si vous posez la question en attendant qu'il charge, à quoi ça peut servir de rapatrier une requête Power Query dans une feuille Excel ? Et bien à de multiples choses : à consulter, à laisser les collègues qui connaissent pas Power Pivot et Power View à faire des graphiques "avec l'ancienne méthode". Sinon c'est vrai que ça sert pas forcément à quoi que ce soit de pertinent puisqu'aujourd'hui on va avoir tendance à faire les tableaux croisés dynamiques avec Power Pivot. Donc on n'a plus besoin d'importer la table Power Query dans une feuille Excel et on va faire les graphiques avec Power View. Donc là comme on le voit il y arrive pas donc je ne pense pas qu'il va arriver à afficher les IBM 500. Ah si quand même. Il y est arrivé un petit peu, donc c'est une démonstration comme quoi le concept fonctionne bien. Là je vais supprimer cette feuille et c'est tout bon. Donc voilà pour les paramètres dynamiques dans Power Query. Vous avez vu un des deux paramètres les plus importants.

Excel 2016 : Analyse de données avec Power Query

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

3h00 (34 vidéos)
Aucun commentaire n´est disponible actuellement
 
Logiciel :
Spécial abonnés
Date de parution :8 sept. 2017

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 !