PowerQuery - "Datenimport 2.0" Part 2

Willkommen zurück zu unserer Deep Dive Excel Session Part 2. Heute geht es nochmal um PowerQuery und den dritten Use Case. Roland redet heute über das Einlesen eines ganzen Verzeichnisses, dem Aktualisieren von Daten
mit nur einem Klick und noch vielem mehr. Sei gespannt.

Inhaltsverzeichnis

Hinweis: Da das Thema Excel so vielseitig ist, haben wir eine Doppelfolge über die Power Query. Solltest du den ersten Teil verpasst haben, keine Problem, hier kannst du dir die Folge gerne nochmal anhören.

Use Case 3 zu PowerQuery – Ein komplettes Verzeichnis einlesen

Hier siehst du die Dateien für unser drittes Szenario. Diese haben optimalerweise ähnliche bzw. fast gleiche Namen. Diese sollten im Vorfeld vorbereitet werden.

Ebenso benötigst du wieder eine leere Excel Datei. Sobald du diese geöffnet hast, gehst du wieder auf die Registerkarte Daten -> Daten abrufen -> Aus Datei -> Aus Ordner

Nun öffnet sich die Suchansicht, die du auch aus anderen Microsoft Anwendungen kennst und du kannst dir deinen Ordner bzw. dein Verzeichnis auswählen.

Beachte, dass hier die erste Stolperfalle lauert. Wie du sehen kannst, werden in dem passenden Ordner der „Mostpläne“ keine Exceldateien angezeigt. Trotzdem ist das der richtige Ordner und du gehst auf „Öffnen“.

Nun wird dir in Excel angezeigt, dass die Dateien am Laden sind und nachdem das fertig ist, schaut es dann so aus.

Also alle Dateien vorhanden und alles richtig gemacht. Um auch wirklich die Daten innerhalb der Dateien zu erhalten und nicht nur die hier ersichtlichen Infos, gehst du auf das Feld „Kombinieren“ und dann auf „Daten kombinieren und transformieren“.

Nun öffnet sich ein weiteres Fenster.

Nun kannst du sehen, dass es sehr sinnvoll ist, deine Tabellen im Vorfeld zu formatieren und zu benennen, da hier im Beispiel die ersten beiden Zeilen keinerlei Infos enthalten und die eigentliche Überschrift erst in Zeile drei beginnt.

Hier ist die formatierte Tabelle (Mostplan_kombiniert) von Roland.

Nach der Auswahl der richtigen Tabelle kannst du mit „OK“ bestätigen und gelangst mit der Tabelle in den Power Query Editor. Nun kannst du sehen, dass alle Mostplan Dateien in diese eine Tabelle kombiniert und eingefügt wurden. In dem grünen Feld links siehst du dann aus welcher Mostdatei die Daten stammen.

Jetzt hast du vielleicht den Fall, dass es sich nicht wie hier um Mostplan Dateien handelt, sondern um Monate etc. Für eine bessere Lesbarkeit wäre es dann sinnvoll, den Dateinamen zu ändern. Zuerst kannst du hier die Spalte mit den Dateinamen duplizieren, um den Originalnamen nicht verändern zu müssen. Sobald du per Rechtsklick auf „Spalte duplizieren“ gegangen bist wird diese als letzte Spalte hinzugefügt und grün hinterlegt. Möchtest du den Spaltennamen auch direkt ändern, gehst du wieder per Rechtsklick auf „Umbenennen“ und gibst die für dich passende Bezeichnung ein.

Roland hat im Beispiel den Namen „Index“ gewählt.

Jetzt möchte Roland die Namen abändern und die Bezeichnung xlsx bei allen Dateien löschen. Hierfür geht er auf die Registerkarte „Transformieren“ und auf den Punkt „Extrahieren“. Hier kannst du Teile des Textes oder einer Formel behalten.

Da die Dateibezeichnung xlsx nach einem Punkt kommt, wählt Roland die Option „Text vor Trennzeichen“ aus. Nun musst du das gewünschte Trennzeichen eingeben und bestätigst mit „OK“.

Nun ist die Bezeichnung xlsx weg. Nun möchte Roland noch den Namen „Mostplan“ weg haben und geht wieder auf „Extrahieren“. Gleiche Vorgehensweise nur, dass er dieses Mal die Option „Text nach Trennzeichen“ wählt.

Unter den erweiterten Optionen kannst du noch weitere Einstellungen vornehmen. Beispielsweise hat der Dateiname mehrere gleiche Trennzeichen und du möchtest den Text nach dem zweiten Trennzeichen extrahieren. Dann kannst du das hier einstellen und wieder mit „OK“ bestätigen.

Jetzt siehst du nur noch die Zahlen ohne den vorherigen Text.

Roland ist aber immer noch nicht zufrieden und möchte nicht 001 da stehen haben, sondern eine „richtige“ Zahl. Die Spalte ist aktuell mit dem „ABC“ versehen, was bedeutet, dass die Spalte auf Text ausgelegt ist. Sobald du auf das ABC klickst öffnet sich ein Fenster in dem du festlegen kannst, welche Kategorie die Spalte haben soll. Wähle hier dann die für dich passende Variante aus.

Hier das Ergebnis.

Nun kannst du deine Tabelle bei Bedarf noch mehr bearbeiten, indem du Spalten löschst oder andere Veränderungen vornimmst. Sobald du dann mit allen Bearbeitungen fertig bist, kannst du die Tabelle über die Registerkarte „Start“ wieder schließen

und auswählen, in welcher Form die Tabelle in deine Excelmappe eingefügt werden soll.

So sieht das Ergebnis dann aus.

Formatänderung der angezeigten Tabelle

Jetzt hast du dich für eine „normale Tabelle“ entschieden und merkst, dass du lieber eine Pivot Tabelle haben möchtest. Hier gibt es einen Trick von Roland, wie du die Ansicht der Tabelle im Nachgang nochmal ändern kannst. Wieder in der Registerkarte „Daten“ gibt es die Möglichkeit auf „Abfragen und Verbindungen“ zu gehen. Hier erscheint dann rechts eine neue Spalte, wie du in dem Bild unten sehen kannst.

Per Rechtsklick kannst du die Option „Laden in“ wählen und gelangst wieder

zu dem Fenster „Daten importieren“. Nun kannst du deine Option von vorhin wieder korrigieren und wählst dann bspw. Die Pivot Tabelle aus und bestätigst mit „OK“.

Dir wird dann nach dem Bestätigen ein Warnhinweis angezeigt, dass du die Daten schon als „normale Tabelle“ hast. Klickst du auf „OK“, wird deine normale Tabelle gelöscht und nur noch die Pivot Tabelle angezeigt.

Danach siehst du deine Pivot Tabelle.

Hier kannst du damit wie gewohnt arbeiten.

Sollen im Nachgang weitere Dateien hinzugefügt werden, da es sich bei den Daten um Monate handelt, kannst du einfach in deinen Ordner (der ja als Datenquelle angegeben wurde) die neuen Daten hinzufügen. Hierbei ist es natürlich wichtig, dass die neue Datei dieselbe Struktur/Namen etc. aufweist, wie die restlichen Dateien. Sonst funktioniert das nicht.

Nach dem Hinzufügen in dein Verzeichnis gehst du wieder auf zu Excel in deine Tabelle und musst alle Daten aktualisieren. Danach werden die Daten auch mitberücksichtigt in der Tabelle.

FERTIG!

Welche Versionen unterstützen Power Query?

In dem Microsoft 365 Abo ist Power Query mit dabei. Office 2016 hat es auch mit minimalen Abweichungen bei der Ansicht. Office 2013 hat Power Query noch nicht inkludiert.

Möchtest du es also testen und ausprobieren, brauchst du mindesten Office 2016 auf deinem Rechner. Danach steht dem nichts mehr im Wege.

Rolands Tipp für deinen Start

DATENQUALITÄT! Achte darauf, dass der Datensatz gut vorbereitet und formatiert ist (Gleiche Dateinamen, Formatierungen innerhalb der Tabellen etc.).

Nun ist das Thema Power Query für uns erstmal beendet. Wir hoffen, dass du jetzt um einiges schlauer bist in diesem Bereich.