Power Query – Der Menüpunkt „Kombinieren“

Abfragen (Tabellen) Anfügen und Zusammenführen

Auch wenn diese Thematik an verschiedenen Punkten dieses Blogs diskutiert worden ist, sollen an dieser Stelle einige Grundlagen zu dieser Thematik besprochen und die Unterschiede herausgestellt werden. Wenn Sie im Register Start im Menüband das Symbol Kombinieren aufrufen, dann erkennen Sie u.A. diese beiden Menüpunkte:

Gruppe: Kombinieren

Gruppe: Kombinieren

Auch wenn es auf den ersten Blick so scheint, dass zwei unterschiedliche Bezeichnungen für die gleiche Sache existieren, das ist nicht der Fall. Das Ziel, welches Sie erreichen wollen bzw. werden ist jeweils ein komplett anderes. Die einzige Gemeinsamkeit: In jedem Fall werden Abfragen (Querys) und nicht direkt Daten aus Excel kombiniert.

Abfragen Anfügen

Auch wenn dieses der zweite, der untere Menüpunkt ist beginne ich hiermit, weil dieser Part einfacher zu verstehen und zu handhaben ist. In Kurzform: Diese Funktionalität fügt eine oder mehrere Abfragen einfach durch anhängen zusammen. Also praktisch copy and paste, wobei die Inhalte nur als Werte ohne Formatierung eingefügt werden. Diese Vorgehensweise, diesen Ablauf werden Sie wahrscheinlich öfter einmal einsetzen, wenn Sie die klassische Aufteilung mehrerer Abteilungen oder Monate in je einem Arbeitsblatt haben, der Aufbau der Tabellen aber immer gleich ist. Wenn es dann beispielsweise darum geht, die Umsätze eines Jahres aus einzelnen Monatsblättern für eine Auswertung zusammenzufassen, dann ist eine (1) große Liste mit allen Daten des Jahres mehr als sinnvoll. Bei der Gelegenheit: Im Normalfall sollten Sie das Design einer (künftigen) Auswertung von vornherein so planen, dass gleiche Daten auch nur in einem einzigen Tabellenblatt, einer einzigen zusammenhängenden Liste zusammengefasst werden. Das ist ein Standard nicht nur in Excel, sondern in der Datenverarbeitung. Und es erleichtert ungemein eine Auswertung.

Zum üben habe ich Ihnen hier eine ZIP-gepackte Datei zur Verfügung gestellt. Dort ist nach klassischer „Unart“ 😉 für jeden Monat des Jahres ein einzelnes Tabellenblatt erstellt worden. Für die Jahresübersicht sollen nun alle 12 Monate in einem einzigen Tabellenblatt konsolidiert werden. Es wird keine typische Konsolidierung mit automatischer Addition von Werten sein, da diese Daten später einmal -nach weiterer Aufbereitung- für eine PivotTable als Datenbasis genutzt werden sollen. Es geht ausschließlich darum, die Ursprungsdaten automatisiert aneinander zu hängen.

Öffnen und entpacken Sie das ZIP-File am besten in einem getrenntes Verzeichnis, damit Sie ausnahmslos die Excel-Dateien Januar bis Dezember im direkten Zugriff haben. – So weit die Vorbereitung für diese Aufgabe.

Sie haben Excel geöffnet und wahrscheinlich ein leeres Arbeitsblatt, einer auch sonst leeren Mappe auf dem Bildschirm. Wenn Sie Excel 2010 bzw. Excel 2013 verwenden, dann ist es zwingend notwendig, dass Sie das Add-On Power Query installiert und auch den entsprechenden Menüpunkt auch im Register zur Verfügung haben. Klicken Sie auf genau diesen Menüeintrag, um die Möglichkeiten des Power Query nutzen zu können. In Excel 2016 bzw. Office 365 wechseln Sie nun zum Register Daten, dort finden Sie in der Gruppe Abrufen und transformieren die gewünschte Funktionalität.

Erstellen Sie nun eine Neue Abfrage | Aus Datei und dann wählen Sie natürlich den Punkt Aus Arbeitsmappe. Im Dialog Daten importieren wechselen Sie zum eben erstellten Verzeichnis und dort die Datei für den Monat Januar aus. Wenn es Ihnen lieber ist, können Sie natürlich auch mit dem 1. Eintrag in der Liste (hier: April) beginnen; wichtig ist nur, dass Sie am Ende alle 12 Monate importiert haben. – Nach der Auswahl des ersten Files klicken Sie auf Importieren und im Navigator-Fenster wird einerseits die Datei mit einem gelben Ordner-Symbol als auch das Wort darin enthaltene Tabellenblatt mit einem Tabellen-Symbol angezeigt. Nach einem Klick auf die Zeile Januar zeigt sich dieses Bild:

Vorschau der Daten beim Import

Vorschau der Daten beim Import

Es ist unschwer zu erkennen, dass es sich hier um die gewünschten Daten handelt. Jetzt stellt sich die Frage, was Sie mit diesen Daten machen wollen. Grundsätzlich bieten sich die Schaltflächen Laden oder Bearbeiten an. Meistens werden Sie in so einem Fall die Schaltfläche Bearbeiten anklicken, um die Daten direkt danach im Editor bearbeiten zu können. In diesem Fall schlage ich aber vor, auf das Dropdown-Symbol der Schaltfläche Laden zu klicken und dann den 2. Punkt Laden in… zu wählen. Damit können Sie erreichen, dass Sie zwar die Daten im Editor be- und verarbeiten können, wenn Sie aber später einmal auf Schließen & laden klicken, werden Ihnen nicht alle 12 Monate plus die Zusammenfassung als einzelne Tabellen in jeweils einem einzelnen Arbeitsblatt abgespeichert. Später dazu mehr. Es erscheint rasch ein Dialog, wo die Auswahl Tabelle markiert ist. Sie werden hier jedoch die untere der beiden Möglichkeiten anklicken:

Es soll nur eine Verbindung erstellt werden …

Es soll nur eine Verbindung erstellt werden …

Dann auf Laden klicken und Sie müssen schon genau hinsehen, dass sich da doch etwas getan hat. Es gibt nun ein rechtes Seitenfenster, wo unter der Überschrift Arbeitsmappen alle Abfragen dieser Mappe aufgeführt werden. Derzeit ist das naturgemäß nur die Abfrage Januar. Unter dem Namen steht noch der Hinweis: Nur Verbindung. – Sie werden es sich denken können, dass Sie nun die restlichen Monate Februar bis Dezember auf die gleiche Weise importieren.

▲ nach oben …

Fehlgriff 😐 

Wenn Ihnen einmal das passiert, was auch mir mitunter widerfährt: Die Finger an der Maus waren schneller als der Kopf dann haben Sie direkt auf Laden angeklickt und nicht auf Laden in… Das merken Sie auch recht schnell, denn es wird automatisch ein neues Tabellenblatt erstellt und im Seitenfenster erkennen Sie, dass der letzte Eintrag ein klein wenig anders aussieht:

Hier sind 32 Zeilen tatsächlich geladen worden

Hier sind 32 Zeilen tatsächlich geladen worden

In dem Fall die eben erstellte direkte Abfrage löschen, indem ich diese beispielsweise im Seitenfenster markiere und dann entweder Entf oder per Rechtsklick den entsprechenden Punkt im Kontextmenü auswählen. Weitere Möglichkeiten dürfen Sie selbst erkunden. 😎

Anschließend werde ich den Vorgang für diesen Monat noch einmal mit einer Denkpause an passender Stelle auf korrekte, die vorgesehene Weise wiederholen. Ach ja, natürlich lösche ich auch das Arbeitsblatt Tabelle2, denn die Daten sind nun ja (nur) als Verbindung vorhanden und sollen nicht isoliert in einer Tabelle angezeigt werden.

▲ nach oben …

Analyse

Das wäre geschafft. Im nächsten Schritt sollten Sie der Sicherheit und Transparenz wegen erst einmal eine Datenanalyse vornehmen. Dazu öffnen Sie die Abfrage Januar auf einem beliebigen Wege; beispielsweise durch einen Doppelklick auf den Eintrag im rechten Seitenfenster. Im Editor erkennen Sie, dass die wirklich leeren Felder mit dem Begriff null dargestellt werden, was keineswegs der Zahl 0 entspricht; diese erkennen Sie beispielsweise beim 1. Januar in der Spalte Summe. Sollte einmal in den Quelldaten eine Zeile beispielsweise durch ein Formel-Ergebnis mit einem Leerstring "" gefüllt sein, dann würde in der Abfrage diese Zelle auch leer (ohne die Anführungszeichen) und nicht mit der Bezeichnung null dargestellt werden.

Wenn Sie im Editor ganz nach unten blättern dann werden Ihnen wahrscheinlich die beiden letzten Zeilen ins Auge fallen. Die Zeile mit dem Text Summe wird bei der Zusammenfassung gewiss nicht gebraucht und die letzte Zeile ist komplett leer. An dieser Stelle werden Sie sich entscheiden müssen, ob Sie für jeden Monat einzelnen direkt nach dem Import diese Korrektur vornehmen oder nach dem zusammenfügen „in einem Schlag“. Ich bevorzuge die zweite Methode, sie spart einiges an Arbeit. Und im Endeffekt soll dann ja auch noch die Spalte mit den Summen entfernt werden. – Und genauso würde ich dann auch mit der Spalte Summe umgehen.

Bleiben Sie im Editor-Fenster mit den Januar-Daten, denn diese sollen ja den Grundstock für die Zusammenfassung bilden. Wenn Sie jetzt versuchen, die Abfrage Januar schon einmal zu speichern, um eine „gesunde Basis“ zu schaffen, dann werden Sie gewiss irritiert sein:

Keine freie Auswahl …

Keine freie Auswahl …

Egal ob Sie nun auf das Symbol oder den nicht ausgegrauten Text klicken, es passiert offensichtlich gar nichts. Und es wird sich auch nicht ändern, wenn Sie noch weitere Abfragen angefügt haben. Die aktualisierte Verbindung wird gespeichert, mehr nicht.Es gibt aber einen Ausweg: Sie erstellen eine Kopie der Abfrage Januar und die können Sie dann ganz normal speichern und anschließend die weiteren Abfragen dort anfügen.

Sie ahnen es vielleicht schon, auch hier haben Sie mehrere Möglichkeiten, eine Kopie zu erstellen. Sie befinden sich immer noch im Editor. Register Start | Gruppe Abfrage und hier den Menüpunkt Verwalten anklicken. Im Dropdown erscheinen 3 Möglichkeiten, wovon die 1. von vornherein wegfällt. Bleiben Duplizieren und Verweis über. Hier treffen Sie nun eine grundsätzliche Entscheidung.

Duplizieren erstellt ein Duplikat, eine Momentaufnahme der derzeitigen Abfrage. Das bedeutet: Sollte sich etwas in den Quelldaten verändern, wird sich das Duplikat nicht verändern. Es besteht keinerlei Verbindung mehr zu der ursprünglichen Abfrage.

Genau andersherum ist es bei Verweis. Ändern sich die Daten in der Mutter-Abfrage, dann werden auch die Daten in der Tochter-Abfrage mit angepasst. Verwenden Sie hier die weniger ressourcenfressende Möglichkeit des Duplikats.

Spätestens jetzt wird links des Editors ein weiteres Seitenfenster geöffnet. Hier sind alle Abfragen der aktuellen Mappe aufgeführt. Und soeben neu dazugekommen in der unteren Zeile die Abfrage Januar (2). Und damit Sie nicht im Eifer des Gefechts nach der Zusammenfassung nur auf das Symbol Schließen & laden klicken und damit erreichen, dass wiederum nur eine Verbindung erstellt und gespeichert wird ohne die Daten in einer neuen Tabelle in einem neuen Blatt darzustellen, sollten Sie jetzt schon auf den Text unterhalb des Symbols klicken und hier Schließen & laden in… wählen. Im anschließenden Dialog markieren Sie im oberen Teil des Fensters Tabelle anstatt Nur Verbindung erstellen und es ist durchaus eine Überlegung wert, in der unteren Hälfte den Punkt Bestehendes Arbeitsblatt auszuwählen, damit das erste Tabellenblatt nicht leer bleibt.

In das 1. Arbeitsblatt wurden nun die Januar Daten geschrieben. Wahrscheinlich werden Sie etwas darüber irritiert sein, dass statt eines typischen Datums die serielle Zahl in dieser Spalte steht. In Zelle A2 wäre dieses 41640. Stören Sie sich nicht daran, dass wird sich gleich von alleine regeln.

Da dieses die Basis für die Zusammenfassung ist, öffnen Sie bitte diese eben erstellte Abfrage noch einmal. Entweder durch Doppelklick im rechten Seitenfenster auf diesen Eintrag oder im Menü-Register Abfrage und im Menüband dann Bearbeiten anklicken. Und hier werden Sie auch wieder ihr Datum in gewohnter Schreibweise sehen. 😆 

 Im Dialog klicken Sie als erstes auf die Options-Schaltfläche Drei oder mehr Tabellen und das Dialogfenster wird sich vergrößern und so darstellen:

Welche Tabellen (eigentlich Abfragen) sollen anhefügt werden?

Welche Tabellen (eigentlich Abfragen) sollen anhefügt werden?

Nacheinander werden Sie nun die Monate Februar bis November hinzufügen. Das geht beispielsweise ganz gut mit einem Doppelklick auf den jeweiligen Monat im linken  Fenster. Dadurch „wandert“ der entsprechende Monat in den Bereich Anzufügen Tabellen. Natürlich können Sie auch jeweils auf die Schaltfläche Hinzuf… klicken. Und genau das müssen Sie in jedem Fall beim Dezember bzw. der letzten anzufügenden Tabelle (Abfrage) machen, denn wenn Sie nur mit Doppelklick gearbeitet haben, wird die OK – Schaltfläche nicht aktiviert sein. Erst wenn zu mindestens 3 Einträge im rechten Bereich aufgeführt sind und dort mindestens einer davon mittels der Schaltfläche Hinzufügen eingetragen wurde, wird OK aktiviert. – Spätestens jetzt sollten Sie dieser Query einen anderen Namen geben, beispielsweise Alle Monate.

Sie haben nun 12 + 1 Abfragen. Die Daten der Abfrage Alle Monate sehen Sie im Editor. Und im nächsten Schritt werden Sie alle Zeilen entfernen, die in der Spalte Datum kein kalendarischen Datum enthalten. Dazu klicken Sie in die Überschrift dieser Spalte und dort auf das Erweitern-Symbol . Im Dropdown entfernen Sie nun das Häkchen in der Zeile (NULL), ziehen den Rollbalken des PullDowns ganz nach unten und entfernen dann auch das Häkchen bei Summe. OK und Sie haben eine „saubere“ Datenbasis. – Dass Sie im Januar an Sonn-und Feiertagen im Editor ein leeres Feld sehen, bei den restlichen Monaten jedoch den Wert null ist der Tatsache geschuldet, dass für eine andere Übung die Januar-Daten etwas manipuliert worden sind.

Prinzipiell ist diese Aufgabe damit erledigt. Die einzelnen Arbeitsblätter sind untereinander angefügt und die nicht erforderlichen Zeilen wurden gelöscht. In diesem Fall sollte noch die Spalte Summe entfernt werden, was nun mit einem einzigen Mausklick in die Überschrift und Entf geschehen kann. Auf Schließen & laden klicken, dann werden die Daten im Blatt Tabelle1 automatisch aktualisiert. Die Aufgabe ist nun wirklich und nicht nur prinzipiell erledigt.

Hinweis: Bei dieser Vorgehensweise ist es wichtig, dass die Überschriften in den einzelnen anzuhängen den Abfragen identisch zu denen in der ersten Abfrage der „Serie“ sind. Auch bei geringen Abweichungen einschließlich der Groß- / Kleinschreibung werden unerwartete Ergebnisse produziert. Meist ist es dann eine zusätzlich erzeugte Spalte, die so gar nicht in das vorgegebene Muster passt.

▲ nach oben …

Hinweis: Kurz nach der Veröffentlichung dieses Beitrages ist auch in einem Forum eine Frage gestellt worden, die mit einer sehr schönen und auch schlichten Power Query Beispiel-Lösung zu Ende gebracht werden kann. – Insbesondere wenn Sie noch nicht viel Erfahrung in Sachen PQ haben, sollten Sie dort zu lesen beginnen.

Abfragen zusammenführen

Ein ganz anderer Ansatz mit viel, viel mehr Möglichkeiten die Daten in eine neue Form zu bringen, das versteckt sich hinter diesem Menüpunkt. Prinzipiell steckt viel Datenbank dahinter. Anhand einiger typischen Beispiele werde ich Ihnen einen Einblick in die Arbeit mit dieser Funktionalität geben.

Lückenfüller

Die Aufgabe ist ganz schlicht und einfach: In einer Liste stehen diverse Zahlen. Ein kleines Manko, dass in der Reihe „Lücken“ sind, soll behoben werden. Genau dieses Thema wurde in unserem Blog bereits erörtert; klicken Sie hier für eine ausführliche Beschreibung und Lösung.

▲ nach oben …

Listen-Abgleich

Auch diese Thematik wurde bereits in unserem Blog behandelt, allerdings unter einem anderen Aspekt; wenn Sie mögen, schauen Sie gerne hier einmal herein. Auch wenn der Lösungsweg praktisch der gleiche ist, werde ich hier mit einer anderen Wortwahl und mehr Bildern etwas tiefer in die Thematik einsteigen.

Vorarbeiten

Beginnen Sie damit, dass Sie diese ZIP-Datei herunterladen und möglichst in ein eigenes Verzeichnis zu entpacken. Dort sind die Bestände von 2 Lager-Standorten in jeweils einer eigenen Arbeitsmappe (Datei) erfasst. Das Ziel ist: Sie sollen feststellen, welches Produkt in welchem Lager vorhandenes und welches nicht. Das hört sich anfangs nach einer „Fingerübung“ an, aber Sie werden rasch sehen dass es nicht reicht, beide Ergebnisse einfach nebeneinander in einer Liste darzustellen. Nicht jedes Lager hat alle Produkte und wirklich geordnet sind die Listen auch nicht. Damit würde ein Abgleich per Hand zur Sisyphos Arbeit ausarten. 😥 Ach ja, und die ZÄHLENWEN() – Methode ist hier auch nicht unbedingt optimal bzw. zielführend.

Mit einer Datenbank und SQL wäre so etwas (für Könner) wirklich eine Fingerübung also eine leichte Aufgabe. Mit dem klassischen Excel allerdings ist das aus meiner Sicht eine Zumutung für die Sachbearbeiterin oder den Sachbearbeiter. Hier ist Power Query eine riesige Hilfe. 💡 

Um die Quelldaten so weit wie möglich zu schützen, sollten Sie diese nur einlesen und nicht direkt in Power Query einbinden. Meine Empfehlung sieht so aus:

  • Erstellen Sie eine neue, leere Arbeitsmappe oder verwenden Sie ein leeres Blatt eines bereits existierenden Workbooks.
  • Aktivieren Sie erforderlichenfalls die Power Query-Funktionalität.
  • Neue Abfrage | Aus Datei | Aus Arbeitsmappe und wählen Sie im Dialog die Datei Lager_1.xlsx aus. Im Navigator markieren Sie die einzige Tabelle, kontrollieren die Vorschau auf Plausibilität und anschließend erweitern Sie die Schaltfläche Laden per Klick auf die Schaltfläche .
  • Wählen Sie hier den unteren der beiden Punkte: Laden in…
  • Unter dem Aspekt, dass die Quelldaten einerseits unangetastet bleiben sollen, sich aber auch von Woche zu Woche verändern können, wählen Sie die Option Nur Verbindung erstellen:

Es soll nur eine Verbindung erstellt werden …

Es soll nur eine Verbindung erstellt werden …

  • Schaltfläche Laden und in der Tabelle selbst tut sich erst einmal gar nichts. Allerdings öffnet sich rechts ein Seitenfenster, wo die Abfrage angezeigt wird und auch der Hinweis, dass nur eine Verbindung existiert:

Die Tabelle 'Lager 1' als 'Nur Verbindung'

Die Tabelle ‚Lager 1′ als ‚Nur Verbindung‘

  • Gehen Sie genauso zum einlesen der Daten für das Lager 2 vor. Auch hier werden Sie den Typ Nur Verbindung wählen.
  • Sollten Sie einmal „abrutschen“, dann können Sie die Abfrage im rechten Seitenfenster auf beliebige Weise löschen und anschließend nach den Vorgaben neu erstellen.

Die Vorbereitungen sind jetzt abgeschlossen, Sie können sich einen „Schlachtplan“ zurechtlegen, was Sie als Ergebnis eine Auswertung sehen wollen. In diesem Falle gebe ich Ihnen einfach einmal vor, welches Ergebnis erzielt werden soll.

▲ nach oben …

Zielsetzung

Die erste Aufgabe ist ja schon definiert. Um das Ganze zu konkretisieren hier noch einmal in Langform: Lager 1 ist das Hauptlager und es soll fest- und gegenübergestellt werden, wie der Lagerbestand des jeweiligen Produkts in Lager 2 ist. Auch wenn sich das anfangs ganz einfach anhört, so gibt es doch die eine oder andere Stolperfalle. Das beginnt damit, dass die Liste Lager 2 ganz anders sortiert ist als die des Hauptlagers. Und es sind auch nicht alle Produkte standardmäßig in Lager 2 vorrätig. Damit ist nicht gemeint, dass der Bestand auf 0 gesunken ist sondern dass sie gar nicht in der Liste erfasst sind weil gar nicht im Angebot. – Hier nun Schritt für Schritt eine Anleitung, wie Sie solch einen Abgleich bewältigen können:

  • Öffnen Sie die Abfrage für Lager 1 entweder durch Doppelklick auf den Eintrag im rechten Seitenfenster oder auf die Auswahl BEARBEITEN im Dialogfenster.

Eine der Möglichkeiten,eine Abfrage zu öffnen

Eine der Möglichkeiten,eine Abfrage zu öffnen

  • Es öffnet sich der Query-Editor und Sie verschaffen sich erst einmal einen Eindruck, was hier in welcher Form dargestellt wird.
  • Jetzt im Reiter Start | Gruppe Abfrage | Verwalten die Auswahl Verweis anklicken.
  • Um eventuellen „Ausrutschern“ vorzubeugen, werden Sie die Roh-Form diese Abfrage erst einmal so speichern, dass sie nicht nur als Verbindung sondern als Tabelle in einem Arbeitsblatt sichtbar ist:
    • Wählen Sie jetzt einfach einmal das Register Datei | Schließen und laden in… und belassen Sie es oberen Teil des Fensters bei der Option Tabelle.
    • In der unteren Hälfte klicken Sie auf Bestehendes Arbeitsblatt und belassen Sie es bei der Ziel-Angabe $A$1; wenn Sie hier ein anderes Ziel wählen kann es sein, dass Sie die neue Auswahl selbst bei korrekter Eingabe mehrfach bestätigen müssen. Das ist zumindest meine Erfahrung. Ein Klick auf Laden und die Abfrage wird als Tabelle/Liste an das von ihnen deklarierte Ziel geschrieben.
  • Da bei diesem Vorgang der Abfrage-Editor automatisch geschlossen wird, öffnen Sie wieder die eben als Verbindung gespeicherte Abfrage. Diese hat ja den korrekten aber nicht gerade aussagekräftigen Namen Lager 1 (2). Den ändern Sie jetzt bitte um und verwenden beispielsweise Lager-Vergleich (1).
  • Prägen Sie sich noch einmal den Aufbau einschließlich der Reihenfolge der Daten dieser Abfrage ein.
  • Links im Editor erkennen Sie eine Seiten-Leiste mit dem Vermerk Abfragen sowie dem Größer-Zeichen. Klicken Sie auf das > und es werden alle 3 Abfragen in einem  Seitenfenster links der Tabelle angezeigt.
  • Klicken Sie einfach einmal auf Lager 2 und erkennen Sie, was hier anders ist und welche Logik hinter dieser Sortierung steckt.
  • Wechseln Sie wieder zu Lager-Vergleich (1) und achten Sie darauf, dass das Register Start aktiviert ist. Hier ein Klick auf die Schaltfläche Kombinieren und dann die obere Auswahl Abfragen zusammenführen. – Es öffnet sich dieser Dialog:

Der erste Schritt zum Zusammenführen zweier Abfrageb

Der erste Schritt zum Zusammenführen zweier Abfrageb

  • Auch wenn dort sehr prominent Es ist keine Vorschau verfügbar zu lesen ist, so geht das doch nur für eine zweite Abfrage, die mit der ersten (in der oberen Fensterhälfte sichtbar) zusammengeführt werden soll. – Klicken Sie in das Textfeld oberhalb dieses Hinweises und wählen Sie durch Klick den Eintrag Lager 2.
  • An dieser Stelle ist es wichtig dass Sie erkennen, welche Spalten als Schlüssel für die Verknüpfung der beiden Abfragen dienen können bzw. sollen. Die Überschriften der Spalten können, müssen aber nicht identisch sein. Es können Zahlen oder auch Texte sein, die Einträge sollten aber den Charakter von IDs haben, also in der einzelnen Tabelle einmalig sein. Hier bietet sich das Feld ProduktNr. an. Und da es sich um Läger eines einzigen Unternehmens handelt ist die Behauptung, dass die Produktbezeichnung zum jeweiligen Produkt identisch ist, legitim. Und es ist auch der Fall.
  • Auch wenn für Sie der Begriff Join-Art vielleicht etwas fremdartig ist, die Listenfeld-Auswahl darunter -insbesondere die in Klammern stehenden Erklärungen- sollten Sie sich genau ansehen; sie können bei passender Gelegenheit von entscheidender Bedeutung sein. – Hier ist die Vorgabe korrekt, denn es sollen alle Produkte des Hauptlagers (oben) dargestellt werden und nur die Produkte aus Lager 2, die mit denen aus Lager 1 übereinstimmen.
  • Markieren Sie nun in der oberen Hälfte sowie in der unteren Hälfte das entsprechende Feld in den Daten-Darstellungen und der Dialog sollte sich nun so darstellen:

Die beiden Schlüssel-Spalten sind markiert und eine Information über die Anzahl übereinstimmender Schlüsselbegriffe wird angezeigt

Die beiden Schlüssel-Spalten sind markiert und eine Information über die Anzahl übereinstimmender Schlüsselbegriffe wird angezeigt

  • Beachten Sie auch den Hinweis, dass in der Datei Lager 2 nicht alle Zeilen mit der 1. Abfrage übereinstimmen. Gleiche Werte würden mich aber sehr nachdenklich machen, denn das Ziel dieser Abfrage ist ja zu erfahren, welche Produkte in Lager 2 nicht gelistet sind.

Was dann folgt, wird Sie vielleicht etwas irritieren. Es gibt zwar eine neue Spalte in der Abfrage, aber jedes Feld (Zeile) hat immer den gleichen Inhalt: Table. Tisch? Wieso? Ok., wenn Sie nicht so tief in der EDV drin stecken werden Sie wahrscheinlich als erstes an diese Übersetzung denken. Gemeint ist hier die Bedeutung „Tabelle“. – Und was soll Ihnen das sagen? Ein Klick auf eine Zelle der Spalte NewColumn (der Name wurde automatisch durch Power Query vergebenen) zeigt zwar irgendetwas an:

Nicht wirklich aussagekräftig für Einsteiger (später aber gewiss)

Nicht wirklich aussagekräftig für Einsteiger (später aber gewiss)

… aber so recht lässt sich das nicht unbedingt einordnen. Da es sich hier um Basics handelt, die vermittelt werden sollen, meine Empfehlung: „Einfach noch nicht einmal ignorieren“.  😉

Bleibt immer noch die Frage, wie ich diese Daten des Lager 2 neben die jeweiligen Produkte des Lager 1 darstellen kann. Schauen Sie sich doch einmal die Überschrift dieser Spalte an. Rechts ist eine Schaltfläche, die für die meisten Windows-Anwendungen atypisch ist. Ich würde dieses Symbol Doppelpfeil als „Doppelpfeil“ oder „abgeknickter Doppelpfeil“ bezeichnen. Klicken Sie mal darauf und der sich auftuende Dialog ist zu mindestens erst einmal vielversprechend:

Diese Felder bzw. Spalten der zweiten Abfrage können Sie anzeigen lassen

Diese Felder bzw. Spalten der zweiten Abfrage können Sie anzeigen lassen

Dass es sich hier um die Überschriften der Spalten in Lager 2 handelt, ist offensichtlich. Wenn Sie es bei der Vorgabe belassen, werden alle hier markierten Spalten aller übereinstimmender Produkte einfach an die bisherige Tabelle angefügt. Natürlich wird dabei automatisch eine Neuordnung vorgenommen, sodass die Produktnummer, welche ja das Verknüpfungsmerkmal sind, jeweils in kommunizierenden Zeile steht.

Aus meiner Sicht ist es aber nicht erforderlich, jede der Spalten zu wiederholen. Für diesen Zweck brauchen Sie doch (eigentlich) nur die Anzahl, den Lagerbestand. Selbst wenn das Produkt in Lager 2 ausverkauft sein sollte, so wird es durch die Zahl 0 in der entsprechenden Spalte als lagermäßig erfasst geführt und ausgewiesen. Entfernen Sie also alle Häkchen bis auf Anzahl. Ich persönlich entferne auch grundsätzlich das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden, weil ich Wert darauf lege, eine individuelle Überschrift zu generieren. Ein Klick auf OK und das ganze sieht schon etwas „vernünftiger“ aus.

Da in einer Liste keine 2 identischen Überschriften existieren dürfen, hat Power Query auch eine entsprechende Umbenennung zu Anzahl.1 vorgenommen. Der besseren Transparenz wegen benenne ich jetzt die 3. Spalte in der Abfrage um: Anzahl (Lager 1) und die letzte Spalte entsprechend zu Anzahl (Lager 2). Ich habe für den folgenden Screenshot die Anzeige mal etwas gescrollt und Sie sollten die Zeilen 11 und 19 etwas genauer betrachten:

null ist "nix", 0 ist die Zahl

null ist „nix“, 0 ist die Zahl

Erahnen Sie, was der Unterschied ist? Die Produktnummer 14 (Zeile 11) wird in Lager 2 gar nicht gelistet, ProduktNr. 22 ist zwar lagermäßig erfasst, hat jedoch buchmäßig einen Bestand von 0.

Schließen & laden und die im Blatt Tabelle1 bereits vorhandene Tabelle wird um diese eine Spalte ergänzt. Hier sehen Sie auch, dass Produkt 14 in Lager 2 nicht gelistet ist, denn das Feld ist leer. Jetzt steht es in den natürlich frei, in dieser Liste beliebige Auswertungen vorzunehmen. Sie können hier in der Excel-Tabelle filtern oder aber die Daten in eine PivotTable übernehmen.

▲ nach oben …

Ergebnisse im gesonderten Blatt

Wenn Sie aber für andere Zwecke eine eigenständige Liste benötigen, wo beispielsweise alle in Lager 2 nicht gelisteten Artikel oder all jene Artikel dargestellt werden, deren Bestand 0 ist, dann ist das mit dem filtern und anschließendem Kopieren und Einfügen vielleicht etwas umständlich. Vor allem dann, wenn diese getrennten Tabellen wiederkehrend benötigt werden. Da schlage ich dann folgendes Vorgehen vor:

  • Öffnen Sie wieder die Abfrage Lager-Vergleich (1) und filtern Sie die letzte Spalte.

Aber Stopp! Wenn Sie jetzt ihren etwas ändern, wird diese Abfrage ja dauerhaft verändert. Und da Sie vielleicht beide der eben genannten Auswertungen brauchen, muss die Datenbasis ja jeweils identisch sein. Darum werden Sie wiederum über den Punkt Verwalten (Register Start | Abfrage) den bisherigen Stand der Dinge Duplizieren, dieser neu erstellten Abfrage gleich einen sprechenden Namen (beispielsweise Lager 2 – nicht gelistet) geben und sofort nach der Umbenennung über Schließen & laden in… in ein neues Arbeitsblatt speichern. Jetzt gleich wieder die Abfrage öffnen und um die nicht gelisteten Artikel herauszufiltern einfach ein Klick auf  in der Überschrift Anzahl (Lager 2), und wählen Sie alle Möglichkeiten außer der obersten (NULL) ab.

Der Zweck ist damit erreicht, aber in der Liste wird dann in der letzten Spalte absolut nichts drinstehen. Ob das nun gerade die Aussagekraft erhöht? Ich würde in dieser Spalte bei den gefilterten Werten einfach den Wert null durch nicht gelistet ersetzen. Aber wie so oft im Leben: „unverhofft kommt oft …“:

Ein Klick auf das Achtung-Schild gibt einen Hinweis

Ein Klick auf das Achtung-Schild gibt einen Hinweis

Das OK ist „ausgegraut“ und wenn Sie auf das Symbol zeigen erklärt Ihnen Power Query, dass Sie einen numerischen Wert eingeben sollen. Klar, Sie können jetzt auch hier eine 0 eingeben was ja auch dem Stand der Dinge entspricht. Aber das könnte zu Irritationen führen, da ja beim wirklichen 0-Bestand gelisteter Produkte ebenfalls eine 0 in der Spalte steht. Also wie lässt sich diese Zwickmühle umgehen?

Ganz klar: erst einmal auf Abbrechen klicken. Dann ein Blick in das Menüband, wo der Datentyp dieser Spalte vermerkt ist. Und siehe da, der Wert null ist vom Typ her eine ganze Zahl. Was liegt also näher, den Datentyp auf Text zu ändern? Machen Sie das und starten Sie den nächsten Versuch, den Inhalt der Spalte zu ersetzen. Und siehe da, der Erfolg ist gegeben. 😎  Schließen & laden und die Daten werden in eine neue Tabelle geschrieben. – Eine Alternative wären natürlich, in der Query die Spalte ganz zu löschen. Ich ziehe das der Nachhaltigkeit wegen vor. Und bei der Gelegenheit sollten Sie auch die Bezeichnungen der Arbeitsblatt-Register den Gegebenheiten anpassen, damit das wiederfinden leichter fällt.

Um von den in Lager 2 gelisteten Produkten diejenigen herauszufinden, die ausverkauft sind werden Sie gleichermaßen vorgehen wie bei den fehlenden Produkten; allerdings als Filter-Kriterium die Zahl 0 eingeben. Und denken Sie daran, dass Sie auch hier wiederum von der Abfrage Lager-Vergleich (1) per Verweis eine neue Abfrage erstellen.

Sie sollten jetzt in der Lage sein, auch all jene Produkte aufzulisten, die in Lager 2 aber nicht in Lager 1 gelistet oder vorrätig sind. Sie werden in dem Fall aber (vermutlich) eine neue Abfragebasis erstellen, wo beim Zusammenführen der Daten oben die Daten vom Lager 2 stehen und runden die des Lager 1. (Hinweis an die Datenbankspezies und Insider: Ja ich weiß … 😉 )

Es wird Ihnen schon aufgefallen sein: Bei dieser Form der Kombination von Abfragen ist es immer nur möglich genau 2 Abfragen zu kombinieren. Die „Luxusausführung“, dass mehrere Abfragen gemeinsam in eine neue Query integriert werden, ist hier nicht gegeben; wenn es erforderlich ist, werden Sie Schritt für Schritt vorangehen und immer die letzte erstellte kombinierte Abfrage als Basis für die kommende Zusammenführung von Daten verwenden.


Ich weiß, viel Text … Schließlich habe ich ja einige Stunden mir der Formulierung und dem Schreiben verbracht. Aber Sie sollten eine solide Basis bekommen. Denn hier in der Gruppe Kombinieren, insbesondere  dem Zusammenführen steckt noch einiges an Potenzial mehr drin, als es auf den ersten Blick erscheint. Das würde aber diese Einführung nicht nur überfrachten sondern es würde an dieser Stelle auch zu weit führen.


▲ nach oben …

Zeitverschiebung 😉

Wie das Leben so spielt, kurz nach Fertigstellung dieses Beitrages ist in einem Forum eine recht interessante Fragestellung gepostet worden, die sich auch recht gut für den Einsatz von Power Query eignet. Gleich vorweg angemerkt: Das lässt sich auch gut mit der SVERWEIS()-Funktion des Excel bewerkstelligen, aber als Einstiegsübung für Power Query (Mittelklasse) ist das ganz einfach zu gut geeignet. 😎 

Wenn Sie diese Mappe laden, erkennen Sie 3 tabellarische Wertegruppen. In den Basis-Daten (Spalte A:C) sind in der 1. Spalte (später) alle Tage des Jahres vom 1. Januar bis zum 31. Dezember aufgeführt. Hier hat jeder Tag exakt eine Zeile. In den beiden folgenden Spalten dieses Blocks steht eine Uhrzeit.

Im Datenblock Einzufügen ist eine ähnliche Anordnung: Eine Spalte mit dem Datum, eine Spalte mit der Zeit. Hier allerdings kann ein Datum mehrfach aber auch gar nicht vorkommen. So fehlen für den 3. Januar die einzufügen den Daten.

Die Ziel-Vorstellung sieht nun so aus, dass in der Spalte Tag jedes Datum mindestens ein Mal vorkommt, mit den dazugehörigen Zeiten der Basis-Daten. Die Zeiten aus dem Datenblock Einzufügen sollen direkt nach der Spalte Tag eingefügt werden. Sind für den Tag mehrere Zeilen mit Zeiten vermerkt, dann soll in der Zieltabelle die erforderliche Anzahl von kalendarischen Daten (Zeilen) eingefügt werden und die Werte aus Zeit1 und Zeit2 sollen auch automatisch aufgefüllt werden. Das System ist in der vorliegenden kleinen Muster-Tabelle ganz gut nachvollziehbar. – Diese Daten der Ziel-Vorstellung sind naturgemäß nur zur Veranschaulichung gedacht, sie dienen im Prinzip nur als Vorgabe für die Orientierung und zum späteren Abgleich des gefundenen Ergebnisses.

Einlesen der Daten

Ich habe mich entschlossen, bei dieser kleinen Aufgabe auf einen Level herunter zu gehen, der einem absoluten Einsteiger in Sachen Power Query entgegen kommt. Ich nehme also bewusst in Kauf, dass redundante Daten in der Mappe existieren. Als Ausgleich ist es dafür gewiss einfacher, an dieser oder jener Stelle das erforderliche „aha-Erlebnis“ zu haben. 😉 Und ich werde hier auch noch detaillierter beschreiben wie ich vorgehe und auch warum ich diesen Weg so beschreite.

Beginnen wir mit den Basis-Daten. Zwischen der Gruppen-Überschrift und den Daten ist nicht ohne Grund eine Leerzeile. So ist ganz klar der Daten-Bereich abgegrenzt und kann durch Excel automatisch erkannt werden. – Die Power Query Funktionalität ist eingeschaltet, klicken Sie irgendwo in den Bereich der Basis-Daten. Um den Abfrage-Editor zu öffnen, wählen Sie Aus Tabelle. Der markierte Bereich hat ja Überschriften, darum OK. Der Editor stellt sich nun so dar:

Direkt nach dem Import in den Abfrage-Editor …

Direkt nach dem Import in den Abfrage-Editor …

Wenn Sie etwas Erfahrung mit Excel und kalendarischen Werten haben dann wissen Sie, dass das Aussehen der dargestellten Werte teilweise zwar anders als gewohnt aber durchaus korrekt ist. Die Uhrzeiten irritieren gewiss besonders, es sind die als Dezimalzeit dargestellten Anteile eines Tages. Klicken Sie mit rechts ind die Überschrift Tag, Typ ändern, Datum (statt Datum/Uhrzeit). Markieren Sie nun die Überschriften Zeit1 und Zeit2 und ändern Sie hier den Datentyp auf Zeit. Dass dann auch die Sekunden angezeigt werden ist Standard und im Editor nicht änderbar. Jetzt ein Klick auf Schließen & laden und diese Abfrage wird erst einmal gesichert und als Tabelle in ein neu erstelltes Arbeitsblatt geschrieben.

Zurück zum Blatt Tabelle1 und platzieren Sie den Cursor in die Daten des Bereichs Einzufügen. Genau wie eben werden Sie über die Auswahl Aus Tabelle im Menüband die Daten in den Query Editor importieren. Auch werden Sie hier die Spalte Tag in ein (reines) Datum ändern, die Spalte Zeit3 in eine (lesbare) Zeit umwandeln. – Da ja automatisch immer so „herrlich“ anonyme Namen für die Abfragen vergeben werden, ändern Sie den Namen für diese Abfrage auf qry_Daten Zeit3. Auch diese Abfrage können, nein sollten Sie in einem getrennten Worksheet sichern.

Sie befinden sich jetzt einer normalen Excel-Tabelle im Arbeitsblatt-Register Tabelle3. Die erstellte Liste ist markiert bzw. eine beliebige Zelle in diesem Bereich ist die aktive Zelle. Im Menü-Register klicken Sie auf Abfrage und anschließend im Menüband auf Bearbeiten. Umgehend öffnet sich der Query Editor für die eben erstellte Abfrage. Klicken Sie im linken, schmalen Seitenstreifen auf das Größer-Symbol >, um das linke Seitenfenster zu öffnen. Es zeigt sich nun dieses Bild:

Alle Abfragen werden im linken Seitenfenster gelistet

Alle Abfragen werden im linken Seitenfenster gelistet

Klicken Sie nun auf den oberen Eintrag Tabelle1 und sofort wird die 1. Abfrage eingeblendet. Um auch diese Abfrage umzubenennen, einfach F2 und dann schreiben Sie qry_Daten Zeit1&2 und bestätigen mit der Eingabetaste. Übriges: Ich verwende grundsätzlich das passende Präfix für das jeweilige Objekt; qry_ für Abfragen (Queries), tbl_ für Tabellen. So kann ich eine Tabelle gut von einer Abfrage unterscheiden.

Sie könnten jetzt direkt mit dem verknüpfen der Daten weitermachen. Das ist aus verschiedenen Gründen aber nicht so optimal. Darum erstellen Sie von dieser Abfrage eine dynamische Kopie. Gemeint ist damit, dass auch das Duplikat der Abfrage mitwächst oder anpasst, wenn sich die hier gezeigte Abfrage wegen geänderter Basis-Daten verändert. Im Register ein Klick auf Verwalten, anschließend Verweis. Es wird sofort ein Spiegel dieser Datei erstellt und die Abfrage hat den gleichen Namen mit dem Zusatz (2).

Zusammenfügen der Abfragen

In der rechten Hälfte des Menübandes finden Sie die Schaltfläche Kombinieren. Ein Klick darauf und im sich auftuenden PullDown wählen Sie den Punkt Abfragen zusammenführen. In dem Dialog erkennen Sie 2 große Bereiche. Im oberen ist die aktuelle Abfrage abgebildet. Klicken Sie dann in das einzeilige Textfeld direkt darunter und wählen Sie dort anschließend die Abfrage qry_Daten 3. Sofort erscheint auch hier ein Abbild der Daten dieser Query. Im oberen als auch unteren Bereich sind es prinzipiell immer nur die ersten 5 Zeilen, die angezeigt werden. (Passende Abbildungen finden Sie im Haupt-Teil.)

Oben sind die Daten, die in jedem Fall in die endgültige zu erstellende Tabelle übernommen werden sollen. Die Werte aus dem unteren Fensterbereich sollen mit der oberen Abfrage verknüpft werden und zwar so, dass zu jedem kalendarischem Datum oben das entsprechende Datum unten angehängt wird und eventuell fehlende Zeilen automatisch ergänzt werden. Das verbindende Glied ist das Datum in der Spalte Tag. Klicken Sie auf eine beliebige Zelle der Spalte Tag im oberen und im unteren Bereich. Damit schaffen Sie eine logische Verbindung. Wenn Sie etwas tiefer schauen (Join-Art) dann werden Sie in Klammern die Erklärung finden, dass alle Datensätze aus der ersten Abfrage und alle übereinstimmenden Zeilen aus der 2. Abfrage verwendet werden sollen. OK und es wird automatisch eine neue Spalte mit dem Namen NewColumn erstellt. Das sieht dann so aus:

Die Aussagekraft der Werte in der neuen Spalte ist noch nicht besonders hoch

Die Aussagekraft der Werte in der neuen Spalte ist noch nicht  hoch

Nicht irritieren lassen, einfach auf dieses Symbol Doppelpfeil in der Überschrift der neuen Spalte klicken. Sie erkennen, dass dort in einem kleinen Dialog die Spalten dieser Abfrage aufgeführt sind:

Alle Spalten-Namen werden "angeboten"

Alle Spalten-Namen werden „angeboten“

Sorgen Sie dafür, dass nur die Zeile mit Zeit3 ein Häkchen hat und klicken Sie auf OK. Überraschung! Für den 1. Januar worden automatisch einige Zeilen und die damit verbundenen Zeiten in die Tabelle eingefügt. Damit sind Sie dem Ziel ein Riesenschritt näher gekommen. Prinzipiell bleiben nur noch einige kleine Dinge über, um der Vorgabe gerecht zu werden. Als erstes können Sie die Überschrift der neu erstellten Spalte korrigieren und nur Zeit3 stehen lassen oder neu schreiben. Dann ziehen Sie die Spalte durch Klick in die Überschrift vor die Spalte Zeit1 und als letztes bleibt noch, die 1. Spalte aufsteigend zu sortieren, damit nicht der 3. nach dem 4. Januar eingereiht ist. Wenn dann auch noch die Spalte Zeit3 für jedes Datum, für jeden Tag getrennt aufsteigend sortiert werden soll, dann machen Sie das jetzt im Anschluss. Das unterscheidet sich grundsätzlich vom Vorgehen in Excel, wo Sie ja erst die Zeit-und dann die Tagspalte sortiert hätten. Ich benenne nun auch noch das Arbeitsblatt-Register um, damit eine bessere Zuordnung möglich ist. Ich vergebe den Namen Zusammenfassung. Jetzt noch Schließen & laden  anklicken und in dem Blatt Zusammenfassung wird das endgültige Ergebnis gespeichert.

Änderung / Ergänzung der Quell-Daten

Wenn Sie mögen, können Sie jetzt in den Quelldaten auf dem Blatt Tabelle1 in Spalte A den nächsten Tag mit irgendwelchen Zeitangaben und in Spalte E ein beliebiges Datum mit irgendeiner Zeit eingeben. Aus Test-Gründen können Sie auch noch einmal den 01.01. 2015 nehmen und 12:00 Uhr, damit der Wert auch richtig eingereiht werden kann. Hinweis: Achten Sie unbedingt darauf, dass die neuen Eintragungen sich nicht außerhalb der Intelligenten Tabelle befinden. Sie sollten also erst in die letzte Zeile der Tabelle klicken, Tab und dann erst in der neuen Zeile die Daten eingeben. Dass alles richtig ist erkennen Sie daran, dass der Markierungsrahmen der intelligenten Tabelle auch den neuen Eintrag umfasst. Wenn Sie das in der Darstellung nicht so genau erkennen können, klicken Sie einfach in den oberen Bereich der Tabelle und StrgA und der gesamte Daten-Bereich der Tabelle wird markiert. Das ist aus meiner Sicht die einfachste und beste Möglichkeit der Kontrolle. Dennoch hier einmal eine Abbildung zur Verdeutlichung:

Die Pfeile zeigen auf die Rahmenbegrenzungen und den Anfasser

Die Pfeile zeigen auf die Rahmenbegrenzungen und den Anfasser

Die schmalen blauen Linien zeigen die Grenzen der Tabelle auf. Mit dem Anfasser unten rechts im Bereich der Tabelle können Sie nachträglich die Intelligente Tabelle so anpassen, dass auch neue Werte, die außerhalb der Liste stehen, in die Tabelle integriert werden.

Öffnen Sie das Arbeitsblatt Zusammenfassung, klicken Sie erforderlichenfalls in die Tabelle und anschließend ein Klick auf den Menüpunkt Abfrage. An 4. Stelle ist das Symbol Aktualisieren, und darauf klicken Sie nun. Sie werden es ahnen, diese Zusammenfassung wird entsprechend den Werten in den Basis-Tabellen aktualisiert. Alle neu eingegebenen Daten stehen am richtigen Platz. – Die Arbeitsblätter Tabelle2 und Tabelle3 könnten Sie sogar löschen. Da aber derzeit in Sachen Power Query sehr viele Anpassungen vorgenommen werden bin ich mir nicht sicher, ob das in späteren aktualisierten Versionen immer noch toleriert wird. Darum meine ich, diese beiden Blätter nur zu verstecken/ausblenden ist der bessere Weg.

… Und hier geht’s zurück zum ursprünglichen Teil „Zusammenfügen“!

▲ nach oben …

Dieser Beitrag wurde unter Daten zusammenführen, Daten-Import / -Export, File-Handling, Filtern & Sortieren, Ohne Makro/VBA, Power Query, PQ-Basics abgelegt und mit , , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.