Power Query Guide: Wie der Befehl „List.Select” für einen Vergleich mit Wert aus Spalte richtig funktioniert

Power Query ist ein mächtiges Werkzeug in Excel und Power BI, mit dem Sie Daten aus verschiedenen Quellen importieren, transformieren und bereinigen können. Eine der nützlichsten Funktionen in Power Query ist List.Select. Dieser Artikel erklärt detailliert, wie Sie den Befehl List.Select verwenden, um Werte aus einer Spalte zu filtern und mit einem bestimmten Wert zu vergleichen. Wir werden auch auf typische Fehler und deren Lösungen eingehen, um Ihnen einen umfassenden Leitfaden zu bieten.

Was ist List.Select in Power Query?

List.Select ist eine Power Query-Funktion, die eine Liste als Eingabe nimmt und eine neue Liste zurückgibt, die nur die Elemente enthält, die eine bestimmte Bedingung erfüllen. Die Syntax von List.Select lautet wie folgt:

List.Select(list as list, selection as function) as list

• list: Die Liste, aus der Sie Elemente auswählen möchten.
• selection: Eine Funktion, die auf jedes Element der Liste angewendet wird. Diese Funktion muss entweder true oder false zurückgeben. Elemente, für die die Funktion true zurückgibt, werden in die neue Liste aufgenommen.

Grundlagen der Verwendung von List.Select mit einer Spalte

Um List.Select effektiv zu nutzen, müssen Sie zunächst verstehen, wie Sie auf Spalten in Power Query zugreifen und diese in eine Liste umwandeln können. Hier ist ein grundlegendes Beispiel:

1. Daten importieren: Importieren Sie Ihre Datenquelle in Power Query. Dies kann eine Excel-Tabelle, eine CSV-Datei oder eine andere unterstützte Quelle sein.
2. Spalte auswählen: Wählen Sie die Spalte aus, die Sie filtern möchten. Angenommen, Sie haben eine Spalte namens „Umsatz”.
3. In Liste umwandeln: Konvertieren Sie die Spalte in eine Liste. Dies kann durch Referenzieren der Spalte in der M-Formel erfolgen. Zum Beispiel, wenn Ihre Tabelle „Tabelle1” heißt, können Sie die Liste der „Umsatz”-Werte mit Tabelle1[Umsatz] erstellen.
4. List.Select anwenden: Verwenden Sie List.Select, um die Liste zu filtern.

Hier ist ein konkretes Beispiel. Angenommen, Sie möchten alle Umsatzwerte über 100 filtern:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Umsatz", type number}}),
    UmsatzListe = #"Geänderter Typ"[Umsatz],
    GefilterteListe = List.Select(UmsatzListe, each _ > 100)
in
    GefilterteListe

Erläuterung:

• Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content]: Referenziert die Tabelle „Tabelle1” in der Excel-Arbeitsmappe.
• #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Umsatz", type number}}): Stellt sicher, dass die Spalte „Umsatz” als Zahl erkannt wird. Dies ist wichtig für numerische Vergleiche.
• UmsatzListe = #"Geänderter Typ"[Umsatz]: Erstellt eine Liste aller Werte in der Spalte „Umsatz”.
• GefilterteListe = List.Select(UmsatzListe, each _ > 100): Verwendet List.Select, um die UmsatzListe zu filtern. each _ > 100 ist die Auswahlfunktion. Das Unterstrichzeichen (_) repräsentiert jedes Element in der Liste. Die Funktion gibt true zurück, wenn der Umsatzwert größer als 100 ist, und false andernfalls.

Vergleich mit einem Wert aus einer anderen Spalte

Die Herausforderung besteht oft darin, Werte nicht nur mit einer festen Zahl, sondern mit Werten aus einer anderen Spalte zu vergleichen. Dafür müssen Sie die Logik etwas anpassen. Hier ist ein Szenario: Angenommen, Sie haben eine Spalte „Zielumsatz” und möchten alle Datensätze filtern, bei denen der „Umsatz” größer ist als der entsprechende „Zielumsatz”.

Es gibt verschiedene Ansätze, dies zu erreichen. Einer der direktesten ist, eine benutzerdefinierte Spalte hinzuzufügen, die einen booleschen Wert zurückgibt und dann die Tabelle nach dieser Spalte zu filtern. Eine andere Methode, die direkt List.Select verwendet, ist etwas komplexer, aber demonstriert die Flexibilität von Power Query.

Methode 1: Hinzufügen einer benutzerdefinierten Spalte und Filtern

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Umsatz", type number}, {"Zielumsatz", type number}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "UmsatzErreichtZiel", each [Umsatz] > [Zielumsatz]),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Hinzugefügte benutzerdefinierte Spalte", each [UmsatzErreichtZiel] = true)
in
    #"Gefilterte Zeilen"

Erläuterung:

• Table.AddColumn fügt eine neue Spalte namens „UmsatzErreichtZiel” hinzu. Der Wert dieser Spalte ist true, wenn der „Umsatz” größer als der „Zielumsatz” ist, und false andernfalls.
• Table.SelectRows filtert die Tabelle, sodass nur die Zeilen erhalten bleiben, in denen „UmsatzErreichtZiel” gleich true ist.

Diese Methode ist oft klarer und leichter zu verstehen, besonders für komplexere Bedingungen. Sie ist auch performanter, da Power Query die Filterung auf Tabellenebene optimieren kann.

Methode 2: Verwendung von List.Zip und List.Select (Fortgeschritten)

Diese Methode ist komplizierter, demonstriert aber, wie man List.Select direkt für einen solchen Vergleich nutzen kann. Sie verwendet List.Zip, um zwei Spalten zu kombinieren und dann List.Select, um die entsprechenden Paare zu filtern.

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Umsatz", type number}, {"Zielumsatz", type number}}),
    UmsatzListe = #"Geänderter Typ"[Umsatz],
    ZielumsatzListe = #"Geänderter Typ"[Zielumsatz],
    KombinierteListe = List.Zip({UmsatzListe, ZielumsatzListe}),
    GefilterteListe = List.Select(KombinierteListe, each each{0} > each{1}),
    //Umwandlung zurück in eine Tabelle (optional)
    #"In Tabelle konvertiert" = Table.FromRecords(List.Transform(GefilterteListe, each [Umsatz=_{0}, Zielumsatz=_{1}]))

in
    #"In Tabelle konvertiert"

Erläuterung:

• List.Zip({UmsatzListe, ZielumsatzListe}): Erstellt eine Liste von Listen, wobei jede innere Liste ein Paar aus Umsatz und Zielumsatz enthält. Zum Beispiel: {{120, 100}, {80, 90}, {150, 130}}.
• List.Select(KombinierteListe, each each{0} > each{1}): Filtert die kombinierte Liste. each{0} bezieht sich auf den Umsatzwert im Paar, und each{1} bezieht sich auf den Zielumsatzwert.
• #"In Tabelle konvertiert" = Table.FromRecords(List.Transform(GefilterteListe, each [Umsatz=_{0}, Zielumsatz=_{1}])): Wandelt die gefilterte Liste (von Paaren) zurück in eine Tabelle. Dieser Schritt ist optional, aber notwendig, wenn Sie die Ergebnisse als Tabelle weiterverarbeiten möchten.

Diese zweite Methode ist deutlich komplexer und in den meisten Fällen weniger effizient als die erste. Sie ist jedoch ein gutes Beispiel dafür, wie flexibel Power Query ist und wie List.Select in komplexeren Szenarien eingesetzt werden kann.

Häufige Fehler und wie man sie vermeidet

• Falsche Datentypen: Stellen Sie sicher, dass die Spalten, die Sie vergleichen, den richtigen Datentyp haben. Verwenden Sie Table.TransformColumnTypes, um die Datentypen explizit festzulegen (z.B. type number, type text, type date).
• Nullwerte: Berücksichtigen Sie, wie Nullwerte Ihre Vergleiche beeinflussen. Sie können Value.Isnull verwenden, um Nullwerte zu überprüfen und entsprechend zu behandeln.
• Performance: Bei großen Datensätzen kann die Performance ein Problem sein. Vermeiden Sie unnötige Transformationen und stellen Sie sicher, dass Ihre Formeln effizient sind. Die erste Methode (Hinzufügen einer benutzerdefinierten Spalte und Filtern) ist in der Regel performanter als die Verwendung von List.Zip und List.Select direkt.
• Syntaxfehler: Achten Sie genau auf die Syntax von M-Formeln. Klammern, Kommas und das each-Schlüsselwort müssen korrekt platziert sein.

Best Practices für die Verwendung von List.Select

• Kommentare: Kommentieren Sie Ihren Code, um ihn verständlicher zu machen, besonders wenn Sie komplexe Transformationen durchführen.
• Schrittweise Entwicklung: Bauen Sie Ihre Abfrage schrittweise auf und testen Sie jeden Schritt, um Fehler frühzeitig zu erkennen.
• Lesbarkeit: Schreiben Sie Ihren Code so, dass er leicht zu lesen und zu verstehen ist. Verwenden Sie aussagekräftige Namen für Variablen und Funktionen.
• Fehlerbehandlung: Integrieren Sie Fehlerbehandlungsmechanismen, um unerwartete Fehler abzufangen und zu behandeln.

Fazit

Power Query‘s List.Select ist ein wertvolles Werkzeug für die Datenfilterung. Durch das Verständnis der Grundlagen und der fortgeschrittenen Techniken, wie dem Vergleich mit Werten aus anderen Spalten, können Sie Ihre Daten effektiv transformieren und analysieren. Denken Sie daran, die Datentypen zu überprüfen, Nullwerte zu berücksichtigen und Ihren Code sauber und lesbar zu halten. Experimentieren Sie mit den verschiedenen Methoden und wählen Sie diejenige, die für Ihr spezifisches Szenario am besten geeignet ist. Die Methode mit der hinzugefügten benutzerdefinierten Spalte ist oft die einfachste und performanteste Lösung.