Verwenden von Power Query zum Extrahieren nicht tabellarischer Daten aus Webseiten

Einführung

Ivan: Ich bin vor kurzem mit meiner Familie nach Australien gezogen. Bei der Wohnungssuche, Ich musste ein gutes Vorort- und Schulpaar finden. Ich habe interessante Fakten gefunden in https://www.myschool.edu.au/. Aber trotzdem, Es gibt keine Möglichkeit, Schulen zu vergleichen. Es bietet nur einige Statistiken zu australischen Schulen, eine Seite nach der anderen. Informationen auf einer typischen Schulprofilseite (P. Nicht. Burwood öffentliche Schule) es ist wie unten gezeigt.

Bedeutet, dass, im schlimmsten Fall, wir können kopieren / fügen Sie Daten manuell in Excel ein und führen Sie unsere eigene Analyse durch. Natürlich, dies ist nur für eine begrenzte Anzahl von Schulen möglich. Selbst für zwanzig Schulen würde es ein bisschen dauern. Aber trotzdem, heute, wenn wir Tools wie Power Query haben (Bekommen & Verwandeln) in Excel, wir können Daten von Websites effizient und einfach "aktualisierbar" sammeln. Alles, was ich in diesem Beitrag beschreibe und teile, ist nur für nicht-kommerzielle Bildungszwecke erstellt.

Daten von einer Schule beziehen

Nehmen wir zum Beispiel die Burwood School. Browser-URL kopieren

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

Und überprüfen Sie die Daten dieser URL in Excel.

Notiz: Normalerweise mache ich alle Abfragen in Excel. Falls erforderlich, Ich kopiere sie / in Power BI stecken geblieben.

Power Query erkennt automatisch alle auf der Webseite verfügbaren Tabellen und präsentiert Optionen, damit wir diejenige auswählen können, die uns interessiert. Wenn wir mehr als eine Tabelle benötigen, müssen wir eine weitere Abfrage erstellen.

Für diese Veröffentlichung, Nehmen wir an, wir interessieren uns für den Parameter "Ausgangssprache außer Englisch". Aber in diesem Fall, Power Query erkennt nur eine Tabelle auf der Schulseite, Nicht die Daten, die ich will!

Die Daten, die ich möchte, sind nur ein Wert in einem Diagramm und nicht in einer Tabelle (n. ° 1 dann) Ja, Daher, kann nicht zu Vergleichszwecken verwendet werden.

Dann, die erforderlichen Daten liegen nicht im Tabellenformat vor. Sie können nur abrufen Tabellen von Webseiten mit Daten aus dem Web abrufen. Dies bedeutet, dass wir einen anderen Weg finden müssen, um diese Daten von einer Webseite zu erhalten.. Glücklicherweise, Es gibt eine Möglichkeit, dies zu tun, und ich teile es mit Ihnen in diesem Beitrag.

Die Methode

Und Google Chrome, wir können Inspizieren Webseitenelemente, indem Sie mit der rechten Maustaste klicken und dann Prüfen auswählen (n. ° 1 dann).

Im HTML-Code unten, wir können das sehen “97%” (n. ° 1 dann) ist ein Text zwischen den Etiketten “” (n. ° 2 dann) was wiederum zum Haupt-Tag gehört “” (n. ° 3 dann ), und das alles ist ein Teil von "<div id = ”nonEnglishSpeakingStudents” ” (# 4 dann). Wahrscheinlich, das brauchen wir.

Das mag beängstigend erscheinen, aber diese Tags helfen uns, die gewünschten Daten im HTML-Text der Webseite zu finden. Dann, Wir müssen zuerst diesen Text bekommen. Zurück zu Power Query, “Aus dem Internet”, Rechtsklick auf die URL (n. ° 1 dann) und klicke dann auf Bearbeiten (n. ° 2 dann).

Power Query umschließt automatisch den aus dem Web empfangenen Inhalt in der Web.Page-Funktion, wie unten gezeigt.

Aber was wir brauchen, ist nur der Text. Also müssen wir die Einstellungen ändern “Datei öffnen als” ein “Textdatei” im Quellpass. Klicken Sie auf das Zahnrad, um den Quellschritt zu bearbeiten.

Wenn wir verwenden “Textdatei”, Power Query lädt die HTML-Zeilen von der Webseite in eine Spalte.

Wir können die Spalte filtern, um die Zeile zu finden, die den Text "nonEnglishSpeakingStudents" enthält..

Der vorherige Schritt hinterlässt eine einzelne Zeile in der Tabelle. Alle anderen Zeilen wurden entfernt. Durch Auswahl der einzigen Zeile, die nach dem Filtern übrig bleibt, wir können das sehen “97%” als Teil des gespeicherten Textes erforderlich.

Jetzt, Wir können diesen Text einfach mit der Standardfunktion von Power Query extrahieren “Text zwischen Trennzeichen”.

Wir müssen lediglich die Start- und Endtrennzeichen angeben, wie im Folgenden gezeigt. Sehr leicht, Sie müssen keine eigenen Funktionen programmieren, Verwenden Sie einfach die Benutzeroberfläche, um zu helfen.

Dadurch wird eine neue Spalte mit dem gesuchten Wert hinzugefügt.

Daten für eine andere Schule abrufen

In der Theorie, wenn wir die URL mit einer anderen Schul-ID ändern, es sollte genauso funktionieren, weil der HTML-Text auf der Profilseite der anderen Schule eine ähnliche Struktur hat.
Klicke auf “Erweiterter Editor” und suchen Sie die URL mit der Identifikation der Schule im Sprachcode 'M'.

Um unsere Abfrage auf die Profilseite einer anderen Schule anzuwenden, Wir müssen nur die URL ändern.
Lass uns nehmen, zum Beispiel, Concord Öffentliche Schule. Ich habe die Abfrage geändert und jetzt ist die Abfrage zurückgegeben 69 (wie im Folgenden gezeigt), das ist das gleiche wie auf der Schulseite, Es klappt!

URL in Parameter umwandeln

Jetzt funktioniert es, Ich werde die URL in einen Parameter umwandeln, anstatt sie als String zu codieren. Es ist besser, einen Parameter zu erstellen, um ihn flexibler zu machen. Home Parameter verwalten Neue Parameter

Füllen Sie die erforderlichen Felder aus (Parametername = URL, Text, Beliebiger Wert)

Ich benutze hier eine andere Schule (Öffentliche Schule Meadowbank https://www.myschool.edu.au/school/41257) um zu überprüfen, ob die Lösung unabhängig von der ausgewählten Schule funktioniert.

Jetzt habe ich URL als ParameterR, erforderlich Ändern Sie den Power Query-Code so, dass er auf einen Parameter statt auf eine fest codierte Zeichenfolge zeigt.

Dafür, Gehe zu den Einstellungen für den Quellschritt abfragen. Und wählen Sie Parameter aus der Dropdown-Liste.

Erstellen Sie eine Funktion aus einer Abfrage

Der Grund, warum wir diese Aufgabe gestartet haben, ist der Vergleich von Schulen. Wir brauchen also Links für alle Schulprofilseiten.

Der effizienteste Weg, dies in Power Query zu tun, besteht darin, eine Funktion aus einer vorhandenen Abfrage zu erstellen. Im Power Query-Editor, Klicken Sie mit der rechten Maustaste auf die Abfrage und wählen Sie “Funktion erstellen”.

Gib deiner Rolle einen Namen.

Die Funktion repliziert den M-Code, der in der anfänglichen Abfrage verwendet wurde, aber füge eine Option hinzu, um sich selbst mit einer Parameter-URL aufzurufen. Beachten Sie, dass die ursprüngliche Abfrage noch vorhanden ist, plus eine neue Funktion.

URLs mehrerer Schulen abrufen

Die aktuelle Version der Website ermöglicht die Suche nach Schulen, zum Beispiel, nach Vorort oder Postleitzahl.

Während wir suchen, im Browser sehen wir eine URL, die Suchparameter enthält –
https://www.myschool.edu.au/school-search?FormPosted=True&SchoolSearchQuery=ryde&SchoolSector=G&SchoolType=P&State=NSW

Verwenden Sie die folgenden Parameter:
SchoolSearchQuery = ryde – zum Beispiel, Vororte rund um Ryde
Schulsektor = G – Regierung
Schultyp = P – Primär
Bundesland = NSW
Dann, Falls erforderlich, Diese Zeichenfolge kann mit einer Formel in Power Query generiert werden.
Auf die gleiche Weise wie oben für 'Daten von einer Schule beziehen' beschrieben, wir können diese Webseite von den Suchergebnissen aus konsultieren und nützliche Informationen aus dem Text extrahieren.

Zum Beispiel, Wir können alle Informationen auf der Suchergebnisseite anzeigen lassen, zusammen mit den URLs der Schulen, die wir als Parameter benötigen, wie im Folgenden gezeigt.

Die URL jeder Schule haben, wir können diese Tabelle mit Informationen von jeder Schulprofilseite erweitern, zum Beispiel, mit “% nicht englischsprachige Schüler” wie im Folgenden gezeigt, Aufruf der Funktion als Aggregatspalte.

Das ist. Die Informationen zum Vergleich von Schulen sind jetzt verfügbar.

Sie können dieselbe Technik auf jede Website anwenden, auf der die Daten in einem nicht tabellarischen Format veröffentlicht werden.

Übungsübungen

Jetzt wissen Sie, wie Sie nicht tabellarische Daten aus dem Web abfragen, Versuchen Sie, die Wechselkurse auf den folgenden Seiten zu erhalten:

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

Eine einfache Möglichkeit, Daten über australische Schulen zu erhalten

Obwohl die Absicht dieses Beitrags darin besteht, zu demonstrieren, was mit Power Query möglich ist, falls Sie neugierig sind, mehr über australische Schulen zu erfahren, Es gibt eine einfachere Möglichkeit, Daten von Schulen abzurufen.
In der Fußzeile von https://www.myschool.edu.au Website finden Sie einen Link zu http://www.acara.edu.au/contact-us/acara-data-access wo alle Daten im Excel-Format vorliegen.