Utilisation de Power Query pour extraire des données non tabulaires à partir de pages Web

introduction

Ivan: J'ai récemment déménagé en Australie avec ma famille. En cherchant un logement, Je devais trouver une bonne paire banlieue et école. J'ai trouvé des faits intéressants dans https://www.myschool.edu.au/. Cependant, il n'y a pas d'option pour comparer les écoles. Il ne fournit que quelques statistiques sur les écoles australiennes, une page à la fois. Informations présentées sur une page de profil d'école type (p. Pas. École publique Burwood) c'est comme indiqué ci-dessous.

Signifie que, dans le pire des cas, nous pouvons copier / coller les données manuellement dans Excel et effectuer notre propre analyse. Bien sûr, cela ne peut être fait que pour un nombre limité d'écoles. Même pour vingt écoles ça prendrait un peu de temps. Cependant, Aujourd'hui, quand on a des outils comme Power Query (Avoir & Transformer) dans excel, nous pouvons collecter des données à partir de sites Web de manière efficace et facilement « évolutive ». Tout ce que je décris et partage dans cet article est créé à des fins éducatives non commerciales uniquement.

Obtenir des données d'une école

Prenez l'école Burwood par exemple. Copier l'URL du navigateur

https://www.myschool.edu.au/SchoolProfile/Index/104736/BurwoodPublicSchool/41369.

Et vérifiez les données de cette URL dans Excel.

Noter: Je fais généralement toutes les requêtes dans Excel. Si c'est nécessaire, je les copie / bloqué dans Power BI.

Power Query détecte automatiquement toutes les tables disponibles sur la page Web et présente des options afin que nous puissions choisir celle qui nous intéresse. Lorsque nous avons besoin de plusieurs tables, nous devons créer une autre requête.

Pour cette parution, disons que nous sommes intéressés par le paramètre "Langue source autre que l'anglais". Mais dans ce cas, Power Query ne détecte qu'une seule table sur la page de l'école, Pas les données que je veux!

Les données que je veux sont juste une valeur dans un graphique et non dans un tableau (m. ° 1 ensuite) Oui, donc, ne peut pas être utilisé à des fins de comparaison.

Ensuite, les données requises ne sont pas sous forme de tableau. Vous ne pouvez récupérer que les tables de pages Web utilisant Obtenir des données du Web. Cela signifie que nous devons trouver un autre moyen d'obtenir ces données à partir d'une page Web.. Heureusement, il y a un moyen de le faire et je le partage avec vous dans ce post.

La méthode

Et Google Chrome, pouvons Inspecter éléments de page Web en cliquant avec le bouton droit de la souris, puis en sélectionnant Inspecter (m. ° 1 ensuite).

Dans le code HTML ci-dessous, on peut voir ça “97%” (m. ° 1 ensuite) est un texte entre les étiquettes “” (m. ° 2 ensuite) qui à son tour appartient à la balise principale “” (m. ° 3 ensuite ), et tout cela fait partie de "<div id = ”nonEnglishSpeakingStudents” ” (# 4 ensuite). Probablement, C'est ce dont on a besoin.

Cela peut sembler effrayant, mais ces balises nous aideront à trouver les données que nous voulons dans le texte HTML de la page Web. Ensuite, nous devons d'abord obtenir ce texte. Revenir à Power Query, “Depuis le Web”, clic droit sur l'url (m. ° 1 ensuite) puis cliquez sur Modifier (m. ° 2 ensuite).

Power Query encapsule automatiquement le contenu reçu du Web dans la fonction Web.Page comme indiqué ci-dessous.

Mais ce dont nous avons besoin c'est juste le texte. Donc il faut changer les paramètres “Ouvrir le fichier en tant que” une “Fichier texte” dans la passe Source. Cliquez sur l'engrenage pour modifier l'étape source.

Lorsque nous utilisons “Fichier texte”, Power Query charge les lignes html de la page Web dans une colonne.

On peut filtrer la colonne pour trouver la ligne qui contient le texte "nonEnglishSpeakingStudents".

L'étape précédente laisse une seule ligne dans le tableau. Toutes les autres lignes ont été supprimées. En sélectionnant la seule ligne restante après filtrage, on peut voir le “97%” requis dans le cadre du texte enregistré.

À présent, nous pouvons facilement extraire ce texte en utilisant la fonction Power Query standard “Texte entre délimiteurs”.

Tout ce que nous avons à faire est de spécifier les délimiteurs de début et de fin, comme il est montré dans ce qui suit. Très facile, pas besoin de programmer vos propres fonctions, utilisez simplement l'interface utilisateur pour vous aider.

Cela ajoute une nouvelle colonne avec la valeur que nous recherchons.

Obtenir des données pour une autre école

En théorie, si nous changeons l'URL avec un autre identifiant d'école, ça devrait fonctionner de la même manière, car le texte HTML aura une structure similaire sur la page de profil de l'autre école.
Cliquez sur “Éditeur avancé” et recherchez l'URL avec l'identification de l'école dans le code de langue 'M'.

Pour appliquer notre requête à la page de profil d'une autre école, nous devons juste changer l'url.
Prenons, par exemple, École publique de Concorde. J'ai modifié la requête et maintenant la requête est retournée 69 (comme il est montré dans ce qui suit), qui est le même que sur la page de l'école, Ça marche!

Convertir l'URL en paramètre

Maintenant ça marche, Je vais convertir l'url en paramètre au lieu de l'encoder sous forme de chaîne. Mieux vaut créer un paramètre pour le rendre plus flexible. Accueil Gérer les paramètres Nouveau paramètre

Remplissez les champs obligatoires (Nom du paramètre = URL, Texte, De n'importe quelle valeur)

J'utilise une autre école ici (École publique Meadowbank https://www.myschool.edu.au/school/41257) de vérifier que la solution fonctionne quelle que soit l'école sélectionnée.

Maintenant que j'ai URL en paramètrer, nécessaire modifier le code Power Query pour pointer vers le paramètre au lieu de la chaîne codée en dur.

Pour cela, accéder aux paramètres de l'étape source de la requête. Et sélectionnez Paramètre dans la liste déroulante.

Créer une fonction à partir d'une requête

La raison pour laquelle nous avons commencé cette tâche est de comparer les écoles. Nous avons donc besoin de liens pour toutes les pages de profil de l'école.

Le moyen le plus efficace de le faire dans Power Query consiste à créer une fonction à partir d'une requête existante. Dans l'éditeur Power Query, cliquez avec le bouton droit sur la requête et sélectionnez “Créer une fonction”.

Donnez un nom à votre rôle.

La fonction réplique le code M utilisé dans la requête initiale, mais ajoute une option pour s'appeler avec une URL de paramètre. Notez que la requête d'origine est toujours là, plus une nouvelle fonction.

Obtenir les URL de plusieurs écoles

La version actuelle du site Web vous permet de rechercher des écoles, par exemple, par nom de banlieue ou code postal.

Pendant que nous cherchons, dans le navigateur, nous pouvons voir une URL qui contient des paramètres de recherche –
https://www.myschool.edu.au/school-search?FormPosted=True&SchoolSearchQuery=ryde&SchoolSector=G&SchoolType=P&State=NSW

Utilisez les paramètres suivants:
SchoolSearchQuery = ryde – par exemple, banlieue autour de Ryde
Secteur Scolaire = G – gouvernement
Type d'école = P – Primaire
État = NSW
Ensuite, si c'est nécessaire, ladite chaîne peut être générée avec une formule dans Power Query.
De la même manière que décrit ci-dessus pour « Obtenir des données d'une école », nous pouvons consulter cette page Web à partir des résultats de recherche et extraire des informations utiles du texte.

Par exemple, nous pouvons obtenir toutes les informations affichées sur la page des résultats de recherche ainsi que les URL des écoles dont nous avons besoin comme paramètres, comme il est montré dans ce qui suit.

Avoir l'URL de chaque école, nous pouvons étendre ce tableau avec les informations de chaque page de profil d'école, par exemple, avec “% étudiants non anglophones” comme il est montré dans ce qui suit, appeler la fonction en tant que colonne agrégée.

Voilà. Les informations nécessaires pour comparer les écoles sont disponibles dès maintenant.

Vous pouvez appliquer la même technique à n'importe quel site Web où les données sont publiées sous un format non tabulaire.

Exercices de pratique

Maintenant que vous savez comment interroger des données non tabulaires sur le Web, essayez d'obtenir les taux de change sur les pages suivantes:

https://www.xe.com/currencytables/?from=AUD&date=2018-06-19

http://www.floatrates.com/daily/AUD.xml

https://www.exchange-rates.org/converter/AUD/EUR/1

https://www.x-rates.com/table/?from=AUD&amount=1

Un moyen facile d'obtenir des données sur les écoles australiennes

Bien que l'intention de cet article soit de démontrer ce qui est possible avec Power Query, au cas où vous seriez curieux d'en savoir plus sur les écoles australiennes, il existe une option plus simple pour récupérer les données des écoles.
Dans le pied de page de https://www.myschool.edu.au site Web, vous pouvez trouver un lien vers http://www.acara.edu.au/contact-us/acara-data-access où toutes les données sont disponibles au format Excel.