Der preiswerteste Lieferant (Sortieren mit Hindernissen)
Wissensstand: Level 2 ⇒ Solides Basiswissen in PQ, etwas Erfahrung
„Eigentlich kein Problem und kein Thema für meinen Blog” dachte ich. Zu Anfang. Aber „Versuch macht kluch” 😉 und ich war rasch an einem Punkt, wo ich nur noch Fragezeichen in den Augen hatte. Aber sehen Sie selbst, was zu Beginn nur einfach erschien und dann zum Problem mutierte. Schauen Sie sich einfach erst einmal die Quelldaten an. Eine typische Konstellation, wo im ersten Tabellenblatt 11 Produkte mit Artikelnummer und Bezeichnung aufgeführt sind und in Tabelle2 für jeden Artikel mehrere Lieferanten mit den jeweiligen Preisen für das Produkt. Und auf der Basis der Tabelle1 soll nun das Produkt mit den Daten des günstigsten Lieferanten verknüpft (angezeigt) werden.
Zu Beginn würden sie aus beiden Datentbereichen der Tabelle1 und Tabelle2 jeweils eine Liste/Formatierte Tabelle erstellen, wenn ich das nicht schon für Sie erledigt hätte. 😉 Sie werden auch folgendes erkennen: Ich belasse es bewusst nicht bei den defaultmäßig vorgegebenen Tabellennamen, der Klarheit wegen vergebe ich für die erste Tabelle den Namen Produkte und die zweite Tabelle den Namen Lieferanten. Die spezielle Formatierung der Lieferanten-Tabelle spreche ich später an. Importieren Sie nun nacheinander die beiden Intelligenten Tabellen in den Power Query-Editor.
Idealerweise speichern Sie nun erst einmal beide Abfragen als Nur Verbindung. Öffnen Sie nun die Abfrage Lieferanten. Um dem preiswertesten Lieferanten je Produkt zu finden bzw. zu separieren bietet sich dieses Vorgehen an:
- Sortieren Sie zuerst die Spalte Artikelnummer aufsteigend.
- Anschließend sortieren Sie die Spalte Preis ebenfalls aufsteigend.
Der Effekt ist, dass zu jeder Artikelnummer der niedrigste Preis an oberster Stelle steht:
Die Sortierung ist (natürlich) wie gewollt
Prima, der Teil ist geschafft (war ja auch nicht so schwer). Der nächste Schritt erfordert, dass für jedes Produkt der preiswerteste Lieferant herausgefiltert wird. Und dazu bietet sich das Entfernen von Duplikaten an. Und da ja nur pro Artikelnummer 1 Lieferant erhalten bleiben soll, werden die Duplikate bei der Artikelnummer entfernt. Und an dieser Stelle noch einmal der Hinweis, dass typischerweise beim Entfernen der Duplikate der erste Datensatz (Zeile) erhalten bleibt und die restlichen Duplikate entfernt werden. Und das Ergebnis:
Die Sortierung ist zu diesem Zeitpunkt nicht gerade wie gewünscht
Überraschung… 😯
Und ich versichere Ihnen, dass Sie keinen Fehler gemacht haben, wenn Sie meinen stichwortartigen Anweisungen gefolgt sind. Das ist aus meiner Sicht eine klare Schwachstelle in Power Query und sollte bereinigt werden. In der Excel-Tabelle (links im Bild) habe ich die niedrigsten Preise je Produkt farblich hervorgehoben. Sie erkennen deutlich, dass hier das Entfernen der Duplikate nach anderen Regeln erfolgte; offensichtlich wurde die Sortierung nach Preis vollkommen ignoriert und immer die erste Zeile der Basis-Daten als Filtrat verwendet.
Dieser Weg ist also nicht zielführend. Entfernen Sie im rechten Seitenfenster bei Angewendete Schritte den letzten Eintrag Entfernte Duplikate. Damit ist wieder die nach Preis sortierte Abfrage in vollem Umfang gegenwärtig. Fügen Sie nun über Spalte hinzufügen eine Indexspalte hinzu. Und jetzt noch einmal den Schritt Duplikate entfernen in der Spalte Artikelnummer:
Jetzt stimmen die beiden Werte vollkommen überein
Bingo, das war’s! In der Spalte Preis ist jeweils der niedrigste Wert ausgewiesen. Power Query erkennt irgendwie, dass diese eindeutige sortierte Zahlenfolge Vorrang hat und nicht „vermischt” werden darf. – Wie auch immer, das Ergebnis stimmt. Und nur das zählt.
Die Spalte Index hat ihren Dienst getan und kann nun gelöscht werden. Bleibt „nur” noch der Schritt, die beiden Abfragen so zu verbinden, zu verknüpfen, dass die Abfrage Produkte um die Preis-Spalte der Abfrage Lieferanten ergänzt wird. Dazu wechseln Sie zur Abfrage Produkte und achten Sie darauf, dass das Register Start aktiviert ist. Kombinieren | Abfragen zusammenführen 
erweitern und Abfragen als neue Abfrage zusammenführen auswählen. Wählen Sie Lieferanten als untere Abfrage und Klicken Sie in beiden Abfrage-Kästen in das Feld Artikelnummer. Join-Art kann so bleiben und jetzt OK. – Hinweis: An verschiedenen Stellen in diesem Blog ist dieses Zusammenführen von 2 Abfragen ausführlich beschrieben, beispielsweise hier. Weitere Beispiel finden Sie per Stichwort-Suche im Blog.
Erweitern Sie die Spalte Lieferanten 
und entfernen Sie alle Häkchen außer bei Preis. Vergleichen Sie nun gerne einmal, ob Power Query seine Arbeit korrekt gemacht hat. Sie werden erkennen, dass stets der niedrigste Preis gefiltert worden ist und dank der Verlinkung über die Produktnummer auch an der richtigen Position angefügt wurde. Schließen & laden oder Schließen & laden in… und das Werk ist getan.