Immer wieder müssen sie gleiche Daten zusammen verbinden mittels lästige SVerweise, Filter und «Wenn»- Funktionen? Eine Tabelle hat eine Information, die andere die nächste? Am besten handelt es sich nicht um zwei, sondern um mehrere Tabellen und der gesamte Wirrwarr muss immer wieder mit neuen Daten zusammen verbunden werden über ein bestimmten Scope?
Problemstellung:
Bei uns mussten verschiedene Mitarbeiter dieselben Tabelle kreieren, der eine konnte es besser der andere schlechter und am Ende wurde es immer neu erfunden mit vielen Fehlern, die hoffentlich nie zum Vorschein kamen. Doch wie sollte das Problem behoben werden?
Lösungsfindung:
Ich hatte zuvor bereits sehr viel mit Power BI gearbeitet. Daher war das natürlich der erste Gedankengang. Den in PowerBI ist es möglich mittels PowerQuery festzulegen was in welcher Reihenfolge verbunden, gefiltert und zusammengeführt wird. Aber warum alles online darstellen und dann zurück ins Excel bringen? PowerBI bietet zwar die Möglichkeit alle Graphiken als Excel zu exportieren, da aber immer eine Tabelle in Form eines Excel für unseren Kunden gefordert wird, schien dies ein unnötiger Umweg.
Aber PowerQuery gibt es auch ohne PowerBI. Und dies schien die geeignete Lösung für unsere Aufgabe. Dieses Excel werde ich von nun an Workbook nennen.
Ziel:
Der Benutzer des Workbook soll nur die einzelnen Dateien in den Ordnerpaketen erneuern. Im Anschluss wählt dieser dann im Workbook dann „Update“ der jeweiligen Zieldatei.
Unser Weg dorthin:
Damit später die Daten im Workbook aktualisiert werden können ist es wichtig, dass die Daten dabei immer dieselbe Form haben und dem Tabellennutzer klar ist was benötigt wird.
Die geforderten Dateien werden zusammen mit dem Workbook mitkopiert und sind in den Ordnern Paket 1 und Paket 2 abgelegt. Die Daten aus dem Paket 1 sind in meinem Projekt sofort verfügbar mittels Abfragen aus dem ERP System. Mithilfe von Layoutvorgaben aus den ERP System ist es möglich das die Tabellen immer gleich aussehen, sodass die PowerQuery immer dasselbe Ergebnis liefern kann. Bitte denkt auch daran das der Tabellenblattname auch vorgegeben sein muss. Gerade wenn manche Mitarbeiter eine englische Version und manche eine deutsche Version von Excel haben, heissen die Tabellenblätter entweder Sheet1 oder Tabelle1, aber fürs gelingen des „Refresh“ müssen sie in den Files immer gleich heissen.
Nachdem die einzelnen Tabellen abgespeichert sind, wird im Excel der Pfad eingegeben. Dateiname ist bei meinem Fall statisch wie oben im Bild ersichtlich. Im Query Editor ist nun hinterlegt dort den Pfad zu holen.
Eine andere Möglichkeit wäre die Daten auch direkt in das zu bearbeitende Excel hineinzukopieren und diese im Query Editor dann zu bearbeiten. Aber bei dem Weg den wir gewählt habe, müssen die Tabellen nicht mal sichtbar sein.
Im Power Query Editor verbindet man nun die entsprechenden Tabellen. Die jeweiligen Spalten die man nun zusammen übereinander legt, müssen dasselbe Datenformat haben, zb Text -Text oder Zahl-Zahl. Einer von den vielen Tabellen Editor sah bei mir am Ende dann so aus:
Am häufigsten nutze ich die Funktion «Merge» mit dem ich den S-Verweis umgehe. Das gute daran ist noch das im PowerQuery dies nicht für jede Spalte gemacht wird, sondern nur einmalig für die komplette Tabelle. Welche Spalten dann «mitkommen» entscheidet man unter «expand».
Natürlich wird auch noch gefiltert und sortiert sowie eine andere Query (Tabelle) unten angehangen mit «Appended». Wer möchte kann den Text unter «Applied Steps» auch noch anpassen um mögliche spätere Änderungen schneller tätigen zu können.
In unserem Fall wird das Ergebnis der Tabellenberechnung am Ende nochmal mit copy paste in ein leeres Excel eingefügt, damit die Berechnungen / das Vorgehen nicht vom Kunden ersichtlich ist.
Nachdem alle Berechnungen vorgegeben sind kann das File an jede Person gesendet werden und von denen die einzelnen Dateien geladen werden.
Links:
https://exceloffthegrid.com/power-query-source-cell-value/
https://docs.microsoft.com/en-us/power-query/merge-queries-overview
