Scrape-Webseiten mit Power Query - Excel-Tipps

Power Query ist ziemlich mächtig. Aber ich bin dabei, Power Query Squared durchzuführen … eine Abfrage für eine Seite zu schreiben und dann Excel dieselbe Abfrage für eine ganze Liste von Webseiten ausführen zu lassen.

Schau Video

Der heutige Trick ist aus dem M is for Data Monkey-Buch übernommen
Erstellen Sie eine Abfrage, um Daten von einer Webseite abzurufen
Bearbeiten Sie die Abfrage, um sie in eine Funktion mit (Variablenname) => vor Let zu ändern
Ändern Sie die fest codierte URL in Variablenname
Benennen Sie die Abfrage in fxWeather um
Schließen & Laden. Die Daten verschwinden.
Verwenden Sie Excel Trickery, um eine Tabelle aller URLs zu erstellen
Erstellen Sie eine Abfrage aus dieser Tabelle.
Fügen Sie eine neue Wetterspalte hinzu =fxWeather((URL))
Erweitern Sie die Spalte. Deaktivieren Sie das Präfix
Tolle!

Video-Transkript

Lernen Sie Excel aus Podcast, Episode 2056: Power Query Squared

Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Ich bin auf diesen Trick gestoßen, als ich mich auf ein Seminar auf einer Konferenz in Dallas mit dem Namen Excelapalooza vorbereitete, dem größten Excel-Konferenznamen aller Zeiten. Sie sollten es jeden September in Dallas überprüfen.

Und danke an Ken Puls und Miguel Escobar, weil ich eine Stunde mit Power Query verbringen musste, also habe ich natürlich ein großartiges Buch herausgebracht, das weltweit größte Buch über Power Query. Ich blätterte durch das Buch und sah, dass sie eine Technik hatten und sagte: „Warte eine Sekunde. Ich werde sehen, ob ich diese Technik anpassen kann. “ Und das ist für mich in der Power-Abfrage großartig. Und hier ist, was wir tun werden: Wir werden eine Power-Abfrage durchführen und dann diese Power-Abfrage ausführen, diese Abfrage, Dutzende Male, in Ordnung?

Das Beispiel, das ich mir ausgedacht habe, war, wo ich Daten von einer Webseite abrufen wollte, in Ordnung. Und ich ging zu - ich suchte nur nach einigen Webseiten, die ich als Beispiel verwenden konnte. Ich bin bei Weather Underground gelandet und hier ist die URL. Sie können sehen, dass ich in Dallas-Fort Worth war. Wir ziehen also Daten für Dallas und es sieht aus wie am 2. Januar 2015. Die URL enthält also genau dort die Parameter , Recht? Und das ist eine URL, die nur reif ist, damit dieses Ding funktioniert.

Wir werden einen kurzen Blick auf die Webseite werfen, obwohl sie nicht so wichtig ist. Sie sehen, es gibt viele verschiedene Daten hier auf der Webseite und ich entschied, dass ich nur versuchen würde, Niederschlag und hohe und niedrige Temperaturen zu erhalten. Und hier ist diese Abfrage. Und lassen Sie mich gleich hier sagen, dass es in diesem Podcast nicht darum geht, wie diese Abfrage, neue Abfrage, aus anderen Quellen, aus dem Web, die URL und dann eine Reihe von Schritten angegeben werden, auf die ich hier nicht näher eingehen werde, um meine zu erhalten endgültige Antwort von Max Temp, Min Temp und Niederschlag. Der Punkt ist, dass Sie Ihre eigene Abfrage nehmen und sie für eine Reihe von Dingen zum Laufen bringen.

Also klicke ich auf Schließen & Laden und diese Abfrage funktioniert, sie gibt meine eine Zeile zurück. Hier ist alles super. Und ich werde zurückkommen, ich werde diese Abfrage bearbeiten und ich werde zu Ansicht, Erweiterter Editor gehen. Ich werde diese Abfrage annehmen und sie zu einer Funktion machen, okay? Also drücke ich hier vor dem Wort LET die Eingabetaste. Und in Klammern werde ich ihm eine Variable (MyURL) und dann => einen kleinen Pfeil geben, okay? Cool. Und dann hier unten, wo sie die URL in Anführungszeichen haben, möchte ich die gesamte URL einschließlich der Anführungszeichen entfernen und dann meinen Variablennamen MyURL eingeben, in Ordnung. Wir sagen also, wir werden es an die URL übergeben und es wird dieselbe Abfrage durchführen, aber mit welcher URL auch immer wir es zufällig übergeben.

Nun, ein paar beunruhigende Dinge hier, wenn ich auf Fertig klicke, oh Männer! Alle meine angewendeten Schritte sind weg und ich soll einen Parameter eingeben. Ignoriere einfach das alles. Wir werden das umbenennen; Wir werden es fxWeather nennen. FX ist natürlich die Abkürzung für function und man muss sich wirklich an diesen Namen erinnern und sich daran erinnern, welche Buchstaben groß geschrieben werden. Das wird in ein paar Minuten sehr wichtig sein. Home, Close & Load und BAM! Alles ist weg. Ach nein! Aber das ist OK. Okay, wir wissen also, dass es da ist. Es ist nur eine Verbindung. Jetzt komme ich hierher und das ist nur altes Excel, okay? Hier ist also die URL. Ich habe sie in den ersten Teil der URL, den Endteil der URL, aufgeteilt. Ich weiß, dass ich das Datum nehmen muss; Ich muss es in diesem seltsamen Format des Jahres formatieren,Monat und Tag, also habe ich die TEXT-Funktion verwendet, um das zu tun. Geben Sie hier das Startdatum ein. Ich kann sogar den Flughafen wechseln, also bin ich jetzt hin und her. Lassen Sie uns MCO für Orlando machen und einige aktuelle Daten machen. Also fange ich am 01.10.2016 an, okay. Jetzt haben wir diesen tollen kleinen Tisch hier aufgestellt. Übrigens muss es ein Tisch sein. Sie müssen das Format als Tabelle oder Strg + T verwenden. Weißt du, das ist nur das Datum und dann + 1 + 1 + 1. Ich formatiere es, ich baue die URL.Sie müssen das Format als Tabelle oder Strg + T verwenden. Weißt du, das ist nur das Datum und dann + 1 + 1 + 1. Ich formatiere es, ich baue die URL.Sie müssen das Format als Tabelle oder Strg + T verwenden. Weißt du, das ist nur das Datum und dann + 1 + 1 + 1. Ich formatiere es, ich baue die URL.

Okay, jetzt werden wir eine Abfrage aus dieser Tabelle erstellen. Okay, und da sind meine Informationen. Ich werde eine neue Spalte hinzufügen, eine benutzerdefinierte Spalte hinzufügen, die Spalten sollten Wetter heißen und die Formel lautet = fxWeather. Stellen Sie sicher, dass es genau die gleiche Groß- und Kleinschreibung ist, und wir fügen das Feld mit dem Namen URL so ein und schließen die Klammer. Keine Syntaxfehler, klicken Sie auf OK. Sie möchten hier etwas über Datenschutz wissen. Dies sind alle öffentlichen Daten. Klicken Sie auf Speichern. Da ist also unser Date. Es ist lustig, dass sie mein Format tatsächlich in etwas geändert haben, das nicht so aussieht, wie ich es begonnen habe. Und dann ist hier Wetter mit dem Erweiterungssymbol. Also werde ich auf das Erweiterungssymbol klicken und das Kontrollkästchen Ursprünglichen Spaltennamen als Präfix verwenden deaktivieren. Ich will das Maximum, das Minimum, den Niederschlag,OK klicken. Okay, und jetzt brauche ich nur noch das Datum und die Informationen da draußen. Also klicke ich mit der rechten Maustaste und entferne diese Spalte, klicke mit der rechten Maustaste und entferne diese Spalte. Hier drüben brauche ich diese Zeit nicht, also werde ich sagen, dass dies nur ein Date ist, okay. Und sehen Sie, was es an jedem Tag tut, an dem ich es bestanden habe. Es gibt das Hoch, das Tief und den Niederschlag für Orlando zurück. Jede Zeile hier führt zu einer anderen Webseite. Stellen Sie sich vor, wenn es nicht 15 Zeilen, sondern 5.000 Zeilen wären, würden Sie es so einrichten, dass es über Nacht ausgeführt wird. Ich habe dafür Makros geschrieben. Tatsächlich ist eine der Webseiten unter, wie ein Makro erstellt wird, um Webseiten von tausend verschiedenen Webseiten auf einer Site zu entfernen, was bei der Leistungsabfrage nicht mehr erforderlich ist.Klicken Sie mit der rechten Maustaste und entfernen Sie diese Spalte. Klicken Sie mit der rechten Maustaste und entfernen Sie diese Spalte. Hier drüben brauche ich diese Zeit nicht, also werde ich sagen, dass dies nur ein Date ist, okay. Und sehen Sie, was es an jedem Tag tut, an dem ich es bestanden habe. Es gibt das Hoch, das Tief und den Niederschlag für Orlando zurück. Jede Zeile hier führt zu einer anderen Webseite. Stellen Sie sich vor, wenn es nicht 15 Zeilen, sondern 5.000 Zeilen wären, würden Sie es so einrichten, dass es über Nacht ausgeführt wird. Ich habe dafür Makros geschrieben. Eine der Webseiten ist, wie man ein Makro erstellt, um Webseiten von tausend verschiedenen Webseiten auf einer Site zu entfernen, was bei der Leistungsabfrage nicht mehr erforderlich ist.Klicken Sie mit der rechten Maustaste und entfernen Sie diese Spalte. Klicken Sie mit der rechten Maustaste und entfernen Sie diese Spalte. Hier drüben brauche ich diese Zeit nicht, also werde ich sagen, dass dies nur ein Date ist, okay. Und sehen Sie, was es an jedem Tag tut, an dem ich es bestanden habe. Es gibt das Hoch, das Tief und den Niederschlag für Orlando zurück. Jede Zeile hier führt zu einer anderen Webseite. Stellen Sie sich vor, wenn es nicht 15 Zeilen, sondern 5.000 Zeilen wären, würden Sie es so einrichten, dass es über Nacht ausgeführt wird. Ich habe dafür Makros geschrieben. Eine der Webseiten ist, wie man ein Makro erstellt, um Webseiten von tausend verschiedenen Webseiten auf einer Site zu entfernen, was bei der Leistungsabfrage nicht mehr erforderlich ist.s Rückgabe des Hochs, des Tiefs und des Niederschlags für Orlando. Jede Zeile hier führt zu einer anderen Webseite. Stellen Sie sich vor, wenn es nicht 15 Zeilen, sondern 5.000 Zeilen wären, würden Sie es so einrichten, dass es über Nacht ausgeführt wird. Ich habe dafür Makros geschrieben. Eine der Webseiten ist, wie man ein Makro erstellt, um Webseiten von tausend verschiedenen Webseiten auf einer Site zu entfernen, was bei der Leistungsabfrage nicht mehr erforderlich ist.s Rückgabe des Hochs, des Tiefs und des Niederschlags für Orlando. Jede Zeile hier führt zu einer anderen Webseite. Stellen Sie sich vor, wenn es nicht 15 Zeilen, sondern 5.000 Zeilen wären, würden Sie es so einrichten, dass es über Nacht ausgeführt wird. Ich habe dafür Makros geschrieben. Eine der Webseiten ist, wie man ein Makro erstellt, um Webseiten von tausend verschiedenen Webseiten auf einer Site zu entfernen, was bei der Leistungsabfrage nicht mehr erforderlich ist.

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

Laden Sie die Beispieldatei hier herunter: Podcast2056.xlsm