Xtract: Mittel- und Großstädte Deutschlands aus einem Web-Import (Wikipedia) in Power Query so filtern, dass eine Excel-Tabelle die dynamischen Filter-Kriterien für die Komplett-Aufstellung darstellt.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Die Einträge einer Excel-Liste verwenden, um eine Power Query-Abfrage zu filtern
Für Sie als geübten Power Query-Anwender ist es gewiss eine leichte Übung, per Mausklick mehrere Filter-Kriterien einer Spalte in einer Abfrage festzulegen und die Query dann zu aktualisieren. Reine Excel-Anwender, die mit PQ praktisch keine Erfahrung haben, werden sich gewiss nicht leicht damit tun, im Power Query-Editor zu arbeiten. Da bietet sich die Möglichkeit, in Excel eine Intelligente Tabelle anzulegen und dort all jene Filter-Begriffe eintragen zu lassen, welche auf die importierten Daten angeordnet werden sollen.
Auf der Suche nach einer mittelmäßig umfangreichen Datenquelle habe ich mich für eine Liste/Tabelle der Deutschen Groß- und Mittelstädte entschieden, welche bei Wikipedia im Internet veröffentlicht worden ist. Und bei der Gelegenheit habe ich die Daten (natürlich) gleich mittels Power Query aus dem Web importiert. – Öffnen sie erst einmal im Browser ihrer Wahl den Link zur Wikipedia-Seite. Wenn Sie etwas nach unten scrollen werden sie diese Tabelle zu Gesicht bekommen:
Diese HTML-Tabelle aus Wikipedia werden Sie für die Auswertung nutzen
Sie erkennen, dass die Städte nach ihrer Einwohnerzahl absteigend geordnet sind. Und da Wikipedia ausgesprochen anwenderfreundlich ganz normale HTML-Tabellen verwendet, lassen sich diese auch direkt in Power Query importieren. Dazu markieren Sie die komplette Adress-Zeile in Ihrem Browser (In der obigen Abbildung bereits geschehen) und kopieren Sie den Link in die Zwischenablage. Spätestens jetzt ist es an der Zeit, eine neue, leere Excel-Arbeitsmappe zu erstellen oder eine solche zu öffnen. Wechseln Sie hier zum Menü Daten und Klicken im Menüband (Gruppe Daten abrufen und transformieren) auf das Symbol Aus dem Web. Im Dialog-Fenster fügen Sie in das Feld URL beispielsweise per StrgV den Inhalt der Zwischenablage ein. Im Dialog-Fenster erkennen Sie einen Eintrag mit dem Pfad zur Website sowie drei Einträge mit dem Symbol für eine Tabelle. Wenn Sie sich bei diesen drei Zeilen durchklicken wird Ihnen im rechten Teil des Fensters jeweils eine Vorschau angezeigt. Bei der untersten Auswahl-Möglichkeit zeigt sich ihnen dieses Bild:
Auswahl der gewünschten HTML-Tabelle (mit Vorschau)
… und wenn Sie hier im Vorschau-Bereich etwas nach rechts scrollen werden Sie feststellen, dass dieses genau die weiter oben gezeigte umfangreiche Tabelle ist. Klicken Sie auf die Schaltfläche Laden und Power Query wird ein neues Arbeitsblatt Tabelle2 anlegen und dort die importierten Daten hineinschreiben. Im rechten Seitenfenster wird der Name der Abfrage angezeigt mit dem Hinweis dass 702 Zeilen geladen worden sind. Ach ja, und „73 Fehler”.
An der Farbe des Fehlerhinweises erkennen Sie, dass es sich um einen Link handelt. Und natürlich ist es wichtig zu erkennen, wo und nach Möglichkeit auch warum ein Fehler entstanden ist. Darum im rechten Seitenfenster erst einmal ein Klick auf den Link und Power Query wird eine neue Abfrage erstellen, wo alle 73 Zeilen mit einem Fehler (oder mehreren Fehlern) aufgezeigt werden. Eine generelle Analyse ist gewiss nicht nur hilfreich, sondern einfach erforderlich. Und natürlich sind die Fehler zu beseitigen, aber es stellt sich die Frage, ob das erforderlich ist…
Um eine gute Entscheidung treffen zu können, müssen Sie natürlich wissen, was wonach gefiltert werden soll. Ich habe (für mich aber auch für Sie) festgelegt, dass in der Komplett-Tabelle alle Einträge (Städte) jener Bundesländer gefiltert werden, die in einer getrennten, durch den User (sprich durch Sie) angelegten Intelligenten Tabelle aufgeführt sind (siehe auch Abbildung weiter unten). Und auch gleich an dieser Stelle meine Auswahl: Da ich ein „Nordlicht” bin, werde ich auch die fünf nördlichen Bundesländer Schleswig-Holstein, Hamburg, Bremen, Niedersachsen und Mecklenburg-Vorpommern als Filter verwenden und dann anzeigen.
Da Sie jetzt prinzipiell wissen, wonach gefiltert werden soll müssen sie nur noch festlegen, welche Spalten im Filtrat ausgegeben werden sollen. Name (Stadt) und Bundesland ist klar, der Rang kann mit ausgegeben werden und bei der Einwohnerzahl ist gewiss nur die Spalte 2019 relevant, wenn überhaupt… Überprüfen Sie nun in der automatisch neu erstellten Abfrage mit den fehlerhaften Datensätzen, ob in den gezeigten Spalten in einer beliebigen Zeile ein Fehler auftaucht oder nicht. Sie können nun natürlich „zu Fuß” jede der 73 Zeilen in jeder der relevanten Spalten auf den Fehlerwert Error überprüfen. Ich finde: Der Hit ist das nicht. Und da ich von Natur aus faul bin, gehe ich hier einen anderen Weg. Das sollte Sie aber nicht daran hindern, die Fehlerursache zu finden. (Zahlen und Texte in einer Spalte ist nie so optimal…)
Erst einmal lösche ich die zweite Abfrage Fehler in Groß- und Mittelstädten …, sie ist mir zu unübersichtlich und es könnten bei einer anderen Datenquelle ja auch deutlich mehr Zeilen sein. Und da mir der Name der Abfrage nach meinem Geschmack entschieden zu lang ist, benenne ich die Query gleich um zu RawData. Das lässt sich doch deutlich leichter schreiben. 😉 Nun markiere ich in genau der gewünschten Reihenfolge per Strg jene Spalten, die erhalten bleiben sollen: Rang, Name, 2019 und Bundesland. Rechtsklick in einer der markierten Überschriften und Andere Spalten entfernen. Und wenn ich nun auf Schließen & laden klicke ist einerseits die Tabelle im Arbeitsblatt Tabelle2 deutlich schmaler und im rechten Seitenfenster ist auch der Hinweis auf vorhandene Fehler verschwunden. Damit entfällt die mühselige Suche.
Nutzen Sie die Gelegenheit, zum Arbeitsblatt Tabelle1 zu wechseln und erstellen Sie dort beispielsweise beginnend in A1 eine formatierte Liste mit den aus der Gesamtliste zu filternden Bundesländern:
Zu filternde Bundesländer
Ein kleiner Hinweis: Ich wohne seit über 30 Jahren in Schleswig-Holstein, der Tippfehler ist also kein Zufall sondern gewollt. 🙂 Übernehmen Sie also bitte genau diese Schreibweise wie in der Abbildung. Importieren Sie nun diese Tabelle in den Power Query-Editor. Ändern Sie vorzugsweise den Namen der Tabelle auch gleich auf Bundesland-Filter. Sie könnten natürlich auch nur den Namen Bundesland verwenden, aber ich finde es nicht so schick, wenn ein Spaltenname identisch ist mit dem Namen der Abfrage.
Wechseln Sie nun wieder zur Abfrage RawData, um erst einmal per Hand den Filtervorgang durchzuführen. Immer unter dem Aspekt, dass später automatisch und ausschließlich die Daten aus der Tabelle Bundesland-Filter genutzt werden sollen. Filtern Sie also per Hand die Spalte Bundesland nach Bremen, Hamburg, Mecklenburg-Vorpommern, Niedersachsen und Schleswig-Holstein. Das Ergebnis ist nach Rang sortiert und umfasst laut Statuszeile 4 Spalten und 126 Zeilen (Städte). Da dieses ja nur ein Test war merkte sich vorzugsweise die Anzahl der Zeilen und löschen im rechten Seitenfenster den letzten Schritt Gefiltert Zeilen.¿
Nun kommt der entscheidende Schritt. Die Werte der Abfrage sollen ja als Filter-Kriterium verwendet werden. Und Sie werden gleich erkennen, dass es auch ganz gut ist, dass der jetzt bestrittene Weg nur einmalig von Ihnen gegangen werden muss und nicht bei jeder Änderung der Daten im Arbeitsblatt Tabelle1. Aber sehen Sie selbst …
Die aktuelle Abfrage ist RawData und umfasst alle 702 Zeilen. Erweitern Sie die Überschrift der Spalte Bundesland und wählen Sie ein beliebiges Land als Filter; beispielsweise Baden-Württemberg, weil es das erste in der Liste ist. Oder sie nehmen Bremen, weil das Ergebnis dort so schön übersichtlich ist. Wegen dieser besseren Übersicht und dadurch bedingten klareren Darstellung habe ich mich auch für Bremen entschieden. Das Ergebnis stellt sich so dar:
Das Ergebnis des Filter-Vorgangs für das Bundesland Bremen
Wichtig ist hier nicht die Tabelle mit den zwei Zeilen, sondern die Eingabezeile mit der Funktion fürs Filtern. Und hier ist der interessante Teil der nach dem Komma, also
each ([Bundesland] = "Bremen")). Ausgesprochen einfach die „Übersetzung” dieses Teils: Selektiere (wähle, belasse) jede Zeile der Abfrage, wo in der Spalte [Bundesland] der Text „Bremen” steht. Ändern Sie nun den Text nach dem Schlüsselwort each „vom Prinzip her gesehen” so ab: … each List.Contains(Name der Liste, [Name der Spalte])) was dann diese (komplette) Zeile ergeben würde: …
Ein wichtiger Hinweis
Der Link auf den Folge-Beitrag beginnt mit „$$”. Das bedeutet für alle derartig ausgezeichneten Beiträge, dass diese kostenpflichtig sind (eine geringe Spende).
Näheres dazu in der angesprochenen Mail. Auch Wissen hat einen Wert!
Wie eben schon dargelegt habe ich auch eine ausführliche Beschreibung meines Lösungsweges in bekannter, ausführlicher Form erstellt. Eine E‑Mail an mich mit dem Stichwort/Betreff $$ PQ: DatenListe als Filter-Kriterium verwenden und Sie bekommen von mir den entsprechenden Link sowie das erforderliche Passwort zum öffnen des Beitrages, sofern Sie mir eine eine Spende von 5,00€ (Überweisung, Amazon-Gutschein oder PayPal Freundschaft) haben zukommen lassen.