LuckyTemplates Power Query: Entfernen doppelter Zeilen

In diesem Blog zeige ich Ihnen, wie Sie doppelte Zeilen in der LuckyTemplates Power Query basierend auf Bedingungen entfernen. Sie können sich das vollständige Video dieses Tutorials unten in diesem Blog ansehen.

Dieses Beispiel stammt aus einer Frage, die im gestellt wurde . Das Mitglied verfügt über Transaktionsdaten und möchte Zeilen entfernen, die sich gegenseitig aufheben.

Es gibt zwei unterschiedliche Transaktionstypen: einen für eingehende und einen für ausgehende. Sie hat auch die Bewegungszählung hinzugefügt. Und darauf aufbauend formulierte sie drei Szenarien.

Szenario eins basiert auf zwei Zeilen. Wenn sie sich gegenseitig aufheben, sollten beide Zeilen entfernt werden. Ist dies nicht der Fall, sollten beide Zeilen beibehalten werden.

Die Szenarien zwei und drei basieren auf einer Kombination von Zeilen, die sich teilweise gegenseitig aufheben. Diese Zeilen sollten nach dem First-In-First-Out-Prinzip entfernt werden, sodass nur die neuesten eingehenden Transaktionen erhalten bleiben.

Im Grunde wiederholen wir immer wieder dieselbe Logik. Wir sollten in der Lage sein, einen einzigen Ansatz zu entwickeln, der alle diese Szenarien abdeckt.

Wir werden unter anderem eine Spalte mit absoluten Mengen und eine Gruppierung nach „Hinzufügen einer Mengenbilanzspalte“ und eine verschachtelte Tabelle mit der Option „Alle Zeilen“ hinzufügen. Außerdem transformieren wir diese verschachtelten Tabellen, sortieren die Bewegungsart und das Buchungsdatum, erstellen eine laufende Summe und behalten nur Zeilen basierend auf einer Bedingung bei.

Ich habe dies auf einem mittleren Niveau bewertet, da die meisten Transformationen, die wir hier durchführen können, über die Benutzeroberfläche erfolgen. Nachdem dies gesagt ist, gehen wir zur LuckyTemplates Power Query über.

Inhaltsverzeichnis

• Hinzufügen einer Spalte mit absoluten Mengen in der Power-Abfrage
• Gruppieren nach ID-Index und absoluter Menge
• Transformieren verschachtelter Tabellen in LuckyTemplates Power Query
  • Sortieren der Bewegungsart und des Buchungsdatums
  • Erstellen einer laufenden Summe
  • Filtern von Zeilen basierend auf einer Bedingung
• Abschluss

Hinzufügen einer Spalte mit absoluten Mengen in der Power-Abfrage

Wir beginnen mit dem Hinzufügen einer Spalte mit absoluten Mengen. Wählen Sie die Spalte „Menge“ aus , gehen Sie dann im Menüband oder auf der Registerkarte „Spalte hinzufügen “ zu „ Wissenschaftlich“ und klicken Sie dann auf „ Absoluter Wert“ .

Ich werde diese Spalte in der Bearbeitungsleiste umbenennen.

Gruppieren nach ID-Index und absoluter Menge

Wir können jetzt nach ID-Index und absoluter Menge gruppieren. Wählen Sie „ID-Index“ aus , halten Sie dann die Umschalt- oder Steuerungstaste gedrückt, um eine Mehrfachauswahl durchzuführen, und klicken Sie dann auf „ Abs qty“ (absolute Menge).

Sie finden „Gruppieren nach“ auf der Registerkarte „Transformieren“.

Sie finden es aber auch auf der Registerkarte „Startseite“.

Klicken Sie darauf und jetzt fügen wir eine neue Spalte hinzu. Es handelt sich nicht um eine „Zählung“, sondern um die „Summe“ der Menge. Benennen wir es also in Qty (Menge) Balance um . Es wird eine Summe der Mengenspalte sein. Und dann fügen wir eine weitere Aggregation hinzu, aber es wird eine besondere sein, also wählen wir „ Alle Zeilen“ aus . Geben wir dieser Spalte auch einen Namen ( AllRows ). Drücken Sie dann OK.

Wenn wir in den Leerraum der Tabelle klicken, sehen wir unten eine Vorschau der verschachtelten Tabelle. Wir sehen, dass sich die Mengen für diese verschachtelte Tabelle gegenseitig aufheben und der Mengensaldo Null ist.

Wenn wir uns die nächste verschachtelte Tabelle ansehen (also seitlich in den Leerraum der nächsten Zeile klicken), sehen wir, dass sich die Mengenspalten nicht gegenseitig aufheben und der Mengensaldo größer als Null ist.

Fügen wir eine benutzerdefinierte Spalte hinzu, damit wir diese später für unsere verschachtelten Tabellentransformationen verwenden können. Wählen Sie also „Benutzerdefinierte Spalte hinzufügen“ aus .

Benennen wir diese Spalte in „Transformationen“ um und fügen Null (0) als Platzhalter hinzu. Klicken Sie dann auf OK.

Transformieren verschachtelter Tabellen in LuckyTemplates Power Query

Jetzt können wir die Logik für die Transformation dieser verschachtelten Tabellen erstellen. Aber wir wollen nicht viele davon schreiben und das ist auch nicht nötig.

Die Benutzeroberfläche können wir größtenteils nutzen. Wenn wir die Logik für diese Transformationen in einer separaten Abfrage erstellen, war das aufwändigste Szenario, das wir hatten, das mit drei oder vier Zeilen.

Wenn ich nun rechts in die letzte Zeile klicke, sehe ich, dass diese Tabelle vier Zeilen hat. Wir können dies also nutzen, um unsere Transformationen aufzubauen.

Klicken Sie mit der rechten Maustaste auf die Seite im Leerraum und wählen Sie Als neue Abfrage hinzufügen aus .

Wir sehen, dass die verschachtelte Tabelle nun erweitert und als neue Abfrage hinzugefügt wurde und wir können damit die Logik erstellen, die wir benötigen.

Sortieren der Bewegungsart und des Buchungsdatums

Jetzt müssen wir zunächst nach Bewegungstyp sortieren . Wählen wir absteigend . Auf diese Weise werden die ausgehenden Transaktionen immer oben angezeigt.

Als nächstes sortieren wir das Buchungsdatum aufsteigend und stellen sicher, dass das Weglassen von Zeilen immer nach dem FIFO-Prinzip (First In First Out) erfolgt.

Erstellen einer laufenden Summe

Um die Zeilen zu identifizieren, die wir entfernen werden, füge ich eine laufende Summe zum Deaktivieren hinzu, und wir können für diese Aufgabe ein List.FirstN verwenden. Diese Funktion erstellt eine Liste basierend auf einer anderen Liste, wobei die Top-Elemente basierend auf einer bestimmten Anzahl oder einem Kriterium gespeichert werden.

Kehren wir zu unserer Anfrage zurück. Wir brauchen zwei Dinge für List.FirstN. Zuerst benötigen wir diese Liste, und diese Liste ist unsere Spalte „Menge“ . Wenn ich mit der rechten Maustaste auf die Spaltenüberschrift „Menge“ klicke, wird die Option „ Als NewQuery hinzufügen“ angezeigt .

Und hier sehen wir den Code, den wir zum Generieren dieser Liste benötigen. Im angewendeten Schritt können Sie sehen, dass er auf den letzten Schritt verweist und dann die Spalte in diesen Klammern identifiziert.

Der zweite Teil, den wir für List.FirstN benötigen, ist eine Zahl, die angibt, wie viele Zahlen aus dieser Liste behalten werden sollen. Dafür können wir den Index verwenden, also füge ich für diese Zeile eine Indexspalte von eins (1) hinzu.

Für Nummer 1 bleibt also die Nummer in der ersten Zeile der Spalte „Menge“ erhalten und so weiter. Und das können wir jetzt zusammenfassen.

Fügen wir eine benutzerdefinierte Spalte hinzu und nennen sie Running Total . Wir verwenden hier List.FirstN und können sehen, dass der erste Parameter diese Liste war, die Spalte „Menge“. Wir müssen auf den letzten Schritt verweisen, und dieser letzte Schritt ist der hinzugefügte Index .

Wir haben die gewünschte Spalte identifiziert, und das ist unsere Mengenspalte. Unsere Zählung steht in der Spalte Index.

Jetzt wird eine Liste zurückgegeben. Wenn wir auf die Seite im Leerraum klicken, können wir den Inhalt dieser Liste sehen. Beim ersten Datensatz wurde nur die oberste Zeile dieser Liste beibehalten. Beim zweiten Rekord behielt es die oberen beiden Reihen.

Jetzt müssen wir nur noch diese Mengen aufsummieren und dazu können wir Lists.Sum verwenden. Also werde ich das in der Bearbeitungsleiste hinzufügen. Und ich werde auch Type einfügen .

Filtern von Zeilen basierend auf einer Bedingung

Anhand dieser laufenden Summe können wir die Zeilen identifizieren, die wir behalten möchten. Wir möchten nur Zeilen behalten, die größer als Null sind, also fügen wir eine Filterbedingung hinzu.

Wir können jetzt unsere Hilfsspalten entfernen. Wählen Sie Index und laufende Summe aus und klicken Sie dann auf Spalten entfernen.

Im erweiterten Editor können wir nun den von uns erstellten Code kopieren. Ich öffne den erweiterten Editor und wir können sehen, dass dies unser geteilter Schritt ist. Wir können also alles darunter auswählen und kopieren.

Kommen wir nun zurück zu unserer ursprünglichen Abfrage. Öffnen Sie erneut den erweiterten Editor. Und statt des Platzhalters gehen wir zu einer neuen Zeile. Ich werde den Ausdruck „ let “ verwenden, da dieser Werte aus Zwischenberechnungen in Variablen erfassen kann. Also „lassen“ und dann in eine neue Zeile gehen und meine Transformationen einfügen. Außerdem werde ich Type deklarieren .

Der oben hervorgehobene Code verweist auf unsere erweiterte verschachtelte Tabelle, die in der Spalte „Alle Zeilen“ platziert ist. Ich werde darauf hinweisen, indem ich diesen hervorgehobenen Code durch AllRows ersetze .

Daher verfügt unsere Transformationsspalte jetzt über verschachtelte Tabellen. Wir bauen unsere Abfrage basierend auf der letzten Zeile auf, oder? Und als wir angefangen haben, waren es vier Zeilen und nur zwei. Wir haben auch, dass diese Menge gleich Null ist, oder? Und jetzt hat es einen leeren Tisch.

Wir können diese entfernen, indem wir nach der Spalte „Mengensaldo“ filtern. Wir wollen nicht, dass die Mengenbilanz gleich Null ist.

Und dann wähle ich meine Transformationsspalte aus und entferne andere Spalten. Ich kann jetzt meine verschachtelten Transformationstabellen erweitern. Verwenden Sie nicht den ursprünglichen Spaltennamen als Präfix und klicken Sie auf „OK“.

Wählen Sie auf der Registerkarte „Transformieren“ die Option „Datentyp“ aus , und schon sind wir fertig. Und so bereinigen Sie Transaktionsdaten.

Unpivot- und Pivot-Grundlagen in LuckyTemplates – Abfrage-Editor Lesen Sie
meine Praxistipps zur Verwendung des LuckyTemplates Advanced Editors.
Erweiterte Transformationen in LuckyTemplates

Abschluss

In diesem Tutorial habe ich Ihnen gezeigt, wie Sie Transaktionsdaten bereinigen und insbesondere doppelte Zeilen entfernen. Dies ist eine großartige Technik, die Sie bei der Verwendung von LuckyTemplates Power Query verwenden können.

Ich hoffe, es hat Ihnen gefallen. Weitere Informationen finden Sie unten im vollständigen Video-Tutorial. Schauen Sie sich auch die Links unten an, um weitere verwandte Inhalte rund um den LuckyTemplates Power Query Editor zu erhalten.

Beifall!

Melissa