Power Query est assez puissant. Mais je suis sur le point de faire Power Query Squared… écrire une requête pour une page, puis demander à Excel d'exécuter la même requête pour toute une liste de pages Web.
Regarder la vidéo
- L'astuce d'aujourd'hui est adaptée du livre M is for Data Monkey
- Créer une requête pour obtenir des données à partir d'une page Web
- Modifiez la requête pour la changer en fonction avec (VariableName) => avant Let
- Remplacez l'URL codée en dur par VariableName
- Renommez la requête en fxWeather
- Fermer et charger. Les données disparaîtront.
- Utilisez Excel Trickery pour créer un tableau de toutes les URL
- Créez une requête à partir de cette table.
- Ajouter une nouvelle colonne de Météo
=fxWeather((URL)) - Développez la colonne. Décocher le préfixe
- Incroyable!
Transcription vidéo
Apprenez Excel à partir d'un podcast, épisode 2056: Power Query Squared
Salut, bon retour sur le netcast, je suis Bill Jelen. Je suis tombé sur cette astuce alors que je me préparais à faire un séminaire lors d'une conférence à Dallas appelée Excelapalooza, le plus grand nom de conférence Excel jamais créé. Vous devriez le vérifier chaque septembre à Dallas.
Et merci à Ken Puls et Miguel Escobar parce que j'ai dû faire une heure sur Power Query, alors bien sûr, j'ai sorti un livre génial, le plus grand livre du monde sur Power Query. Je feuilletais le livre et j'ai vu qu'ils avaient une technique et j'ai dit: «Attendez une seconde. Je vais voir si je peux adapter cette technique. Et cela pour moi est génial dans les requêtes de puissance. Et voici ce que nous allons faire, nous allons prendre une requête d'alimentation, puis nous allons exécuter cette requête d'alimentation, cette requête, des dizaines de fois, d'accord?
Et donc, l'exemple que j'ai proposé était celui où je voulais extraire des données d'une page Web, d'accord. Et je suis allé - j'ai juste cherché des pages Web que je pourrais - que je pourrais utiliser comme exemple. Je me suis retrouvé à Weather Underground et voici l'URL, et vous pouvez voir que j'étais à Dallas-Fort Worth, donc nous tirons des données pour Dallas et cela ressemble au 2 janvier 2015. Donc, juste là dans l'URL se trouvent les paramètres , droite? Et c'est une URL qui est juste mûre pour faire fonctionner cette chose.
Nous allons jeter un coup d'œil à la page Web même si ce n'est pas si important. Vous voyez, il y a beaucoup de données différentes ici sur la page Web et j'ai décidé que j'allais simplement essayer d'obtenir des précipitations et des températures élevées et basses. Et voici cette question. Et laissez-moi vous dire ici que ce podcast ne concerne pas la création de cette requête, une nouvelle requête, à partir d'autres sources, à partir du Web, spécifiez l'URL, puis un tas d'étapes que je ne vais pas détailler ici pour obtenir mon réponse finale de la température maximale, de la température minimale et des précipitations. Le fait est que vous allez prendre votre propre requête et la faire fonctionner pour un tas de choses.
Donc, je clique sur Fermer et charger et cette requête fonctionne, elle renvoie ma ligne. Tout est génial. Et je vais revenir, je vais modifier cette requête et je vais aller à View, Advanced Editor. Je vais prendre cette requête et je vais en faire une fonction, d'accord? Alors juste ici avant le mot LET, j'appuie sur Entrée. Et entre parenthèses, je vais lui donner une variable (MyURL) et ensuite => petite flèche là, d'accord? Cool. Et puis ici, où ils ont l'URL entre guillemets, je veux me débarrasser de l'URL entière, y compris les guillemets, puis tapez mon nom de variable MyURL, d'accord. Donc, ce que nous disons, c'est que nous allons le transmettre à l'URL et il va faire la même requête, mais quelle que soit l'URL que nous passons.
Maintenant, quelques choses déconcertantes ici, quand je clique sur Terminé, oh les hommes! Toutes mes étapes appliquées ont disparu et ils veulent que je saisisse un paramètre. Ignorez tout cela. Nous allons renommer ceci; nous allons l'appeler fxWeather. FX, bien sûr, étant l'abréviation de fonction et vous devez vraiment vous souvenir de ce nom et vous rappeler quelles lettres sont en majuscules, ce sera très important dans quelques minutes. Accueil, Close & Load et BAM! Tout est parti. Oh non! Mais ça va. Très bien, nous savons donc que c'est là. C'est une connexion seulement. Maintenant, je vais venir ici et c'est juste du vieux Excel, d'accord? Voici donc l'URL, je l'ai éclatée dans la première partie de l'URL, la partie de fin de l'URL. Je sais que je dois prendre la date; J'ai besoin de le formater dans ce format étrange de l'année,mois et jour, j'ai donc utilisé la fonction TEXT pour le faire. Mettez ici la date de début. Je peux même changer d'aéroport, alors maintenant je fais des va-et-vient. Faisons MCO pour Orlando et faisons quelques données récentes. Je vais donc commencer le 01/10/2016, d'accord. Alors maintenant, nous avons cette super petite table installée ici. Et au fait, ce doit être une table. Vous devez utiliser le format comme tableau ou Ctrl + T. Donc, vous savez, il s'agit simplement de saisir cette date, puis + 1 + 1 + 1. Je le formate, je construis l'URL.Vous devez utiliser le format comme tableau ou Ctrl + T. Donc, vous savez, il s'agit simplement de saisir cette date, puis + 1 + 1 + 1. Je le formate, je construis l'URL.Vous devez utiliser le format comme tableau ou Ctrl + T. Donc, vous savez, il s'agit simplement de saisir cette date, puis + 1 + 1 + 1. Je le formate, je construis l'URL.
Très bien maintenant, nous allons créer une requête à partir de cette table. D'accord, et voici mes informations. Je vais ajouter une nouvelle colonne, ajouter une colonne personnalisée, les colonnes s'appelleraient Météo et la formule sera = fxWeather. Assurez-vous que c'est exactement la même casse, les mêmes lettres majuscules et minuscules, et nous insérerons ce champ appelé URL comme ça, en fermant les parenthèses. Aucune erreur de syntaxe, cliquez sur OK. Ils veulent en savoir plus sur la confidentialité ici, ce sont toutes des données publiques, cliquez sur Enregistrer, d'accord. Alors, voici notre rendez-vous. C'est drôle qu'ils aient changé mon format en quelque chose qui ne ressemble pas à ce avec quoi j'ai commencé. Et puis voici Météo avec le symbole d'expansion. Je vais donc cliquer sur le symbole de développement, décocher Utiliser le nom de la colonne d'origine comme préfixe. Je veux le Max, le Min, la Précipitation,cliquez sur OK. Très bien, et maintenant tout ce dont j'ai besoin est la date et cette information là-bas. Je vais donc faire un clic droit et supprimer cette colonne, faire un clic droit et supprimer cette colonne. Ici, je n'ai pas besoin de ce temps donc je dirai que ce n'est qu'une date, d'accord. Et voyez ce qu'il fait à chaque date où je le passe; il renvoie le haut, le bas et les précipitations pour Orlando. Chaque ligne ici va vers une page Web différente. Imaginez, s'il ne s'agissait pas de 15 lignes mais de 5000 lignes, vous le configureriez pour qu'il s'exécute pendant la nuit. J'écrivais des macros pour cela. En fait, l'une des pages Web est de savoir comment créer une macro pour extraire des pages Web de mille pages Web différentes sur un site, ce qui n'est plus nécessaire avec Power Query.ll cliquez avec le bouton droit de la souris et supprimez cette colonne, cliquez avec le bouton droit de la souris et supprimez cette colonne. Ici, je n'ai pas besoin de ce temps donc je dirai que ce n'est qu'une date, d'accord. Et voyez ce qu'il fait à chaque date où je le passe; il renvoie le haut, le bas et les précipitations pour Orlando. Chaque ligne ici va vers une page Web différente. Imaginez, s'il ne s'agissait pas de 15 lignes mais de 5000 lignes, vous le configureriez pour qu'il s'exécute pendant la nuit. J'écrivais des macros pour cela. En fait, l'une des pages Web est de savoir comment créer une macro pour extraire des pages Web de mille pages Web différentes sur un site, ce qui n'est plus nécessaire avec Power Query.ll cliquez avec le bouton droit de la souris et supprimez cette colonne, cliquez avec le bouton droit et supprimez cette colonne. Ici, je n'ai pas besoin de ce temps donc je dirai que ce n'est qu'une date, d'accord. Et voyez ce qu'il fait à chaque date où je le passe; il renvoie le haut, le bas et les précipitations pour Orlando. Chaque ligne ici va vers une page Web différente. Imaginez, s'il ne s'agissait pas de 15 lignes mais de 5000 lignes, vous le configureriez pour qu'il s'exécute pendant la nuit. J'écrivais des macros pour cela. En fait, l'une des pages Web est de savoir comment créer une macro pour extraire des pages Web de mille pages Web différentes sur un site, ce qui n'est plus nécessaire avec Power Query.s renvoyant le haut, le bas et les précipitations pour Orlando. Chaque ligne ici va vers une page Web différente. Imaginez, s'il ne s'agissait pas de 15 lignes mais de 5000 lignes, vous le configureriez pour qu'il s'exécute pendant la nuit. J'écrivais des macros pour cela. En fait, l'une des pages Web est de savoir comment créer une macro pour extraire des pages Web de mille pages Web différentes sur un site, ce qui n'est plus nécessaire avec Power Query.s renvoyant le haut, le bas et les précipitations pour Orlando. Chaque ligne ici va vers une page Web différente. Imaginez, s'il ne s'agissait pas de 15 lignes mais de 5000 lignes, vous le configureriez pour qu'il s'exécute pendant la nuit. J'écrivais des macros pour cela. En fait, l'une des pages Web est de savoir comment créer une macro pour extraire des pages Web de mille pages Web différentes sur un site, ce qui n'est plus nécessaire avec Power Query.
Now, when I Close & Load, it's funny the preview here is showing me all the results. When I Close & Load, they're actually going to go do each query. And so right now we have preview rows loaded and it will take a good long time for this information to get updated. So, go to lunch, do something especially if you're doing more than 15 rows. And it's funny the preview is correct but they're still going out and could chunk, could chunk, could chunking through each individual row.
And there it's loaded. Is this amazing or what? Hey, I do a lot of Excel seminars, the Power Excel seminar in Orlando, Florida. Look at these beautiful temperatures we have down here on November 4, 2016. My afternoon will be all about Power Query, Power BI, Power Pivot, Power Map. So, I'm going to invite you down to Orlando to check out this seminar. There'll be a link there in the top-right hand corner.
Alright, so recap. Today's trick is from this book, M is for (DATA) MONKEY. We built a query to get one web page and then edit that query to change it into a function. So right before the Let statement variable name => and then change the hard-coded URL to whatever that variable name is. Rename the query to fxWeather, Close & Load, the data disappears. Then, we use some sort of Excel trickery to create a table of all the URLs we want to crawl, create a query from that table. This has to be a Ctrl+T table, add a new column of Weather = fxWeather and again it has to match the case there, (URL), Expand that column, uncheck Prefix. BAM! It is amazing.
Thanks to Ken and Miguel for writing this book. Thanks to you for stopping by. Hope to see you in Orlando on November 4th 2016. See you next time for another netcast from.
Download File
Téléchargez l'exemple de fichier ici: Podcast2056.xlsm
