Eine wesentliche Transformation in Power Query ist das Filtern von Tabellen. Um diesen Vorgang dynamisch und flexibel zu gestalten, zeige ich Dir in diesem Beitrag, wie Du Spalten in Power Query dynamisch nach Werten filterst: basierend auf Listen.
Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!
Die Anforderung
Ich habe eine Datenbasis in Excel, von der nicht alle Werte für mich relevant sind. Ich möchte diese Daten daher in Power Query importieren und auf die relevanten Werte einschränken. Eine Tabelle mit einer Auflistung der relevanten Buchstaben (das ist eine Spalte in der Datenbasis) befindet sich in derselben Exceldatei und erlaubt es dem Nutzer den Filter durch manuelle Eintragungen in die Exceltabelle, dynamisch zu steuern. Somit kann die Datenbasis beliebig eingeschränkt werden. Im Anschluss führt die Power Query-Abfrage eine Gruppierung durch, die je Buchstaben die Summe über alle Werte bildet. Der folgende Screenshot zeigt wie die Datenbasis (Input) durch die in der Filtertabelle (Filter) aufgelisteten Buchstaben den Output beeinflusst.
Bevor ich mich an die Umsetzung mache, zunächst ein paar Vorüberlegungen.
Vorüberlegung
Für das dynamische Filtern von Spalten in Tabellen gibt es, wie so häufig, mehr als einen Lösungsansatz. Beispielsweise wird an dieser Stelle gern mit Inner Joins zwischen der Inputtabelle und der Filtertabelle gearbeitet. Dies ist sicherlich ein valider Weg, dennoch möchte ich an dieser Stelle eine Listen-basierte Lösung heranziehen und zwar aus folgenden Gründen:
- Das Ergebnis eines Inner Joins ist schlussendlich immer eine Ergebnistabelle, auch falls diese leer sein sollte. Es wird also immer eine zusätzliche Spalte erzeugt, die ich am Ende wieder lösche, weil ich nur indirekt am Ergebnis des Inner Joins interessiert bin. Ich möchte nicht auf das Ergebnis des rechten Tabelle zugreifen, sondern lediglich die Datenmenge der linken Tabelle auf diejenigen Datensätze einschränken, die beim Schlüsselkriterium (Buchstabe) die gleichen Werte aufweisen.
- Ich habe vor kurzem einen einführenden (englischsprachigen, aber dennoch – so glaube ich – leicht zu verstehenden) Beitrag über lists in M geschrieben. Ich habe mir vorgenommen diesen Beitrag mit mehr praktischen Beispielen zu ergänzen, um die Möglichkeiten von Power Query noch mehr herauszuarbeiten. Schauen wir uns also die Lösung mittels dynamischer Listen an.
Die Lösung
Ausgangssituation ist, dass ich sowohl die zu filternden Werte, als auch die Filtertabelle in Power Query importiert habe. Da ich die Filtertabelle in Form einer dynamischen Liste als Spaltenfilter nutzen möchte, muss ich die importierte Filtertabelle zunächst in eine Liste konvertieren:
Die Filtertabelle in eine Liste konvertieren
Das Konvertieren einer (einspaltigen) Tabelle in eine Liste geht über die Nutzeroberfläche von Power Query ganz einfach:
Diese Liste erhält die Bezeichnung GesetzteFilter_Liste. Mit der nun vorhandenen Liste geht es weiter auf dem Weg zur fertigen Lösung.
Integration des dynamischen Spaltenfilters in 3 Schritten
Ich untergliedere die Erstellung meiner Lösung in 3 Schritte, die ich in den folgenden Absätzen erläutern werde.
Einen manuellen Spalten-Filter setzen
Indem ich eine manuellen Filter über die Nutzeroberfläche setze, erhalte ich gleich die richtige Syntax für die Funktion Table.SelectRows(), die ich für meine Listen-basierte Lösung benötige. Warum sich also das Leben mit händisch erzeugtem M-Code schwer machen 😉 Die folgende Animation zeigt, wie der Filter manuell zu setzen ist:
Nachdem ich nun die korrekte Syntax für das Selektieren von Datensätzen auf Basis des Buchstabens A vorliegen habe, geht es ans Modifizieren des M-Codes in der Bearbeitungsleiste. Wer die Bearbeitungsleiste nicht sehen kann, kann diese über Ansicht → Layout → Bearbeitungsleiste einblenden.
Integration der Funktion List.ContainsAny()
Der Ausdruck Table.SelectRows(#Gänderter Typ", each (Buchstabe] = "A")) filtert die bestehende Tabelle #"Geänderter Typ" in Spalte Buchstabe auf den Wert A. Dies ist eine gute Ausgangssituation für den Code, den ich nun erzeugen muss, um nicht auf den fixen Wert A, sondern auf Basis der dynamischen Liste zu filtern. Diese Liste wird im Anschluss auf Basis von Einträgen im Excelsheet durch den Nutzer angepasst.
Um mein Ziel (zumindest erst einmal teilweise) zu erreichen, wandle ich ([Buchstabe] = "A") zu List.ContainsAny(GesetzteFilter_Liste, [Buchstabe]). Während der erste Filterausdruck fix nach dem Wert A filtert, soll mein alternativer Ausdruck den in der Spalte Buchstabe befindlichen Wert mit Einträgen in meiner in Excel gepflegten Liste abgleichen. Die Funktion List.ContainsAny(list to check, list of values to check for)ist dafür die richtige Funktion. Der erste Parameter der Funktion muss eine Liste von Werten sein, welche die Werte beinhaltet, nach denen gesucht werden soll. Der zweite Parameter muss eine Liste von Werten sein, in denen nach den Werte des ersten Parameters gesucht wird. Für die vorliegende Problematik nutze ich die Funktion also wie folgt:
- Parameter: Hier setze ich meine dynamische Liste
GesetzteFilter_Listeein, denn diese beinhaltet die Werte, nach denen ich suchen möchte. - Parameter: Hier kommt der Wert aus der Spalte Buchstabe rein, denn in diesen möchte ich danach suchen, ob hier ein Wert auftaucht, der auch in meiner dynamischen Liste (die im ersten Parameter angegeben wurde) enthalten ist.
Setze ich die Lösung wie eben beschrieben um, erhalte ich jedoch leider trotzdem folgende Fehlermeldung:
Was ist hier passiert? Wie Du im Screenshot sehen kannst, gibt die Fehlermeldung zurück, dass „B“ nicht in eine Liste konvertiert werden kann. Was bedeutet das? Nun ja, die Funktion List.ContainsAny() benötigt für die ersten beiden Parameter – wie bereits erwähnt – Werte vom Typ list. Dies trifft auf die Liste GesetzteFilter_Liste zu. Allerdings ist der Verweis auf die Spalte [Buchstabe] nicht vom Typ list. Hier wird auf den ersten Wert (in diesem Falle der Buchstabe „B“) verwiesen und bemängelt, dass „B“ nicht den Typ list aufweist. Hier kann jedoch sehr einfach nachgearbeitet werden 😉
Behebung des List-Problems
Es gibt diverse Möglichkeiten, Listen zu erzeugen. Wer sich hier im Detail belesen möchte, den verweise ich auf meinen Beitrag über lists in M. Eine dieser Möglichkeiten ist die Verwendung der sog. list-Initialisierungs-Syntax: {}. Damit wird aus der Spalte [Buchstabe] die Liste {[Buchstabe]} – auch wenn diese nur einen einzigen Wert beinhaltet – und schon arbeitet die Funktion wunschgemäß:
Damit ist das wesentliche Problem gelöst und alle weiteren Schritte (wie beispielsweise das Gruppieren) sind einfach zu bewältigen.
Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de
Viele Grüße aus Hamburg,
Lars
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
laurent meint
Kann es sein, dass hier ein sematischer Irrtum vorliegt?
Aus meiner Sicht werden nicht Spalten gefilter sondern Zeilen. Das Filtern von Spalten hätte aus meiner Sicht zur Folge, dass wir nach der Filterung weniger Spalten haben. Das Filtern von Zeilen dagegen hätte zur Folge, dass nach der Filterung weniger Zeilen vorhanden sind.
Die M Formel bestätigt aus meiner Sicht auch diese Annahme: Table.SelectRows
Lars Schreiber meint
Hallo Laurent,
Du hast vollkommen recht, dass es hier um das Filtern von ZEILEN geht. Und ja, der Titel kann dadurch irreführend sein. Dennoch handelt es sich nicht um einen Irrtum. Viele Excelnutzer suchen nach „Spalten Filtern“, weil sie den Filter ja in einer Spalte setzt, auch wenn Sie diesen dann nutzen, um die Zeilen einzuschränken.
Danke für Deinen Kommentar und viele Grüße,
Lars
Dominik meint
Hallo Lars,
das hat mich in die richtige Richtung gebracht – vielen Dank.
Aber ich habe eine Frage: Warum hast du ContainsAny statt Contains verwendet? Ist das gleichwertig? Dann könntest du dir die geschweiften Klammern um das Feld [Buchstabe] sparen. Vor allem hat es bei mir aber auch einen riesigen Unterschied in der Performance gemacht: Bei ContainsAny wurden erst alle Daten vom SQL Server geladen (~100 Mio Zeilen – dauert ewig) und anschließend gefiltert. Wenn ich Contains verwende werden die Daten scheinbar direkt im SQL Server gefiltert und nur die relevanten geladen (~3 Mio Zeilen). Zumindest vermute ich aufgrund des Zeilen-Zählers und der Performance beim Aktualisieren, dass dies passiert.
Hast du damit Erfahrung oder weißt, warum sich PowerQuery da unterschiedlich verhält?
Viele Grüße!
Dominik
Sylvia meint
Hallo Lars,
vielen Dank schon einmal für diese tolle Lösung. Ich habe sie gut verstanden und konnte sie umsetzten, so dass es auch funktioniert. 😉
Nun habe ich aber das Problem, dass sich die Filterliste nicht aktualisiert, wenn ich eine Änderung vornehme. Könnte es daran lieben, dass die „Quellliste“ nur in Excel und nicht zusätzlich in PQ liegt? Dachte ich nehme als Quelle für die Liste die Tabelle aus der Exceldatei.
Du schreibst ganz oben: „Ausgangssituation ist, dass ich sowohl die zu filternden Werte, als auch die Filtertabelle in Power Query importiert habe.“ Das verwirrt mich etwas.
Sebastian meint
Hallo Lars,
danke für den hilfreichen Artikel. Ich konnte deine Lösung bereits mehrfach erfolgreich einsetzen. Bei meinem aktuellen Datensatz würde ich diese Vorgehensweise auch gern anwenden, allerdings müsste dazu der dynamische Filter auch Textteile verwerten, anstatt nur eindeutige Treffer (Bsp.: die Spalte enthält Werte wie 01-2020/001, 02-2020/001, 02-2020/002, 03-2020/001 usw. Einige Werte kann ich eindeutig und vollständig aufzählen, bei anderen würde es helfen, wenn alle Werte ausgeschlossen werden, die mit 01/2020 beginnen, egal was danach folgt). Hast du dafür auch eine kompakte Lösung? Aktuell zerlege ich die Zeichenfolgen und filtere in mehreren Stufen, was allerdings immer wieder Eingriffe in die Abfrage erforderlich macht.
Viele Grüße, Sebastian
Sebastian meint
Hallo Lars,
habe die Lösung zwischenzeitlich selbst gefunden und will sie dir nicht vorenthalten:
Table.SelectRows(#“Vorheriger Schritt“, each List.ContainsAny({[Spalte_mit_Werten]},FilterListe,(x as text, y as text)=>Text.Contains(x,y)))
Die „Filterliste“ kann sowohl die ganze Zeichenfolge oder nur Anteile der zu suchenden Zeichen enthalten.
Jörg meint
Lieber Lars,
herzlichen Dank fürs Teilen deines Wissens und deiner Erfahrungen. Ich hatte heute die Anforderung einen dynamischen Filter für eine Spalte zu definieren. Mit meinen deutschsprachigen Suchbegriffen bin ich wieder einmal bei dir gelandet und fündig geworden.
In einem etwas anderen Sinne von ‚Sharing is caring‘ möchte ich hier kurz meine Erfahrungen mit dem listenbasierten Lösungsansatz zurückmelden und darlegen weshalb ich schlussendlich den Weg über einen Join gewählt habe:
Meine Selektionskriterien liegen in einer Excel-Zelle und waren durch Kommata getrennt. Beim Einlesen eines einzelnen Wertes stehen in Excel-PQ zunächst nur einige Transformations-Optionen an der Programmoberfläche zur Verfügung. Die Option ‚Text unterteilen“ (M-Funktion Text.Split() ) machte aus den Werten der Zelle die gewünschten Einzelwerte. Prima dachte ich, doch der Datentyp in der zu filternden Spalte war vom Typ Zahl. So lieferte die obige Lösung zunächst keine Datensätze, weil eine 1 vom Typ Text nicht einer 1 vom Typ Zahl entspricht. Also Liste in Tabelle umgewandelt, Typ auf Zahl geändert und wieder in eine Liste transformiert. So funktionierte es dann.
Bei den von mir verarbeiteten Quelldaten handelte es sich um SAP-Exporte im Textformat. Bei einer kleinen Datei mit knapp 4.000 Zeilen bemerkte ich schon, dass die Abfrage nach Implementierung des dynamischen List-Filters deutlich länger dauerte.
Die Performance beim Import einer Faktentabelle mit gut 250.000 Zeilen war dann nicht mehr akzeptabel. War die Abfrage vor Einbeziehung des dynamischen Listen-Filters nach 1:20 Minuten abgearbeitet, habe ich nach Einbeziehung des Listenfilters die Abfrage nach 15 Minuten abgebrochen. Da ich in der Erstellung der Abfrage keine Fehlermeldung erhalten habe und diese im Vorschau-Modus durchlief, gehe ich davon aus, dass ich die Umsetzung richtig gemacht habe. Anschließend habe ich den Weg über den InnerJoin ausprobiert, das Ergebnis lag hier nach 1:33 Minuten vor. Das war für mich vertretbar, so dass ich es bei der Join-Lösungsvariante belassen habe.
Vielleicht ist Performance ein Grund, weshalb gern die Join-Variante eingesetzt wird. Wie auch immer, der Impuls für die Lösung meines Problems kam aus deinem Post. Dafür nochmals besten Dank.
Sonnige Grüße aus Zeven
Jörg
Robert meint
Hallo Lars,
ich fand den Text sehr verständlich. Die Ausführung mit Text und Screenshots finde ich sowieso besser als ein Video.
Allerdings sollte man sich vorher schon mit der grundlegenden syntax von M beschäftigt haben.
Gibt es auch eine Negierung hierzu?
Zeige nur Daten deren Schlüssel nicht in der Filtertabelle vorkommt.
Gruß Robert
Lars Schreiber meint
Hallo Robert,
die einfachste Möglichkeit, dies in diesem Beispiel herzustellen ist, in Abfrage ‚dynamisches_Filtern_List_ContainsAny‘ den Schritt ‚Dynamischer_Filter_korrekt‘ mit einem >not< zu versehen: = Table.SelectRows(#"Geänderter Typ", each not List.ContainsAny(GesetzteFilter_Liste, {[Buchstabe]})). Vermutlich gibt es performantere Lösungen, aber mit dieser kleinen Umstellung kommst Du zumindest inhaltlich ans Ziel.
Viele Grüße,
Lars
Peter meint
Machen Sie bitte ein Video auf Youtube dazu. Das ist kaum verständlich als Text.
Lars Schreiber meint
Hallo Peter,
vielen Dank fürs Lesen meines Blogs. Ich nehme mir stets viel Zeit, um meine Artikel so verständlich wie möglich zu schreiben und erhalte dafür viel gutes Feedback von Lesern. Schade, dass es in Ihrem Fall nicht so aussieht. Ein gutes und verständliches Video zu drehen dauert gerne mal 1,5 Tage Arbeitszeit, die ich weder aufbringen kann noch möchte. Daher muss ich hier leider enttäuschen.
Viele Grüße,
Lars