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.

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.

Screenshot Ansicht Verzeichnis Ordner

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

Screenshot Daten abrufen aus Datei

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“.

Screenshot Verzeichnis auswählen

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

Screenshot Ansicht Verzeichnis in Excel

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“.

Screenshot Daten kombinieren und transformieren

Nun öffnet sich ein weiteres Fenster.

Screenshot Dateien kombinieren

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.

Screenshot Dateien kombinieren aus Tabelle 1

Hier ist die formatierte Tabelle (Mostplan_kombiniert) von Roland.

Screenshot Dateien kombinieren Tabellenauswahl

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.

Screenshot power Query Editor Mostplan Verzeichnis

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.

Screenshot Spaltenname ändern

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

Screenshot Umbenennung der Spalte

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.

Screenshot Tabellenname extrahieren

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“.

Screenshot Text vor Trennzeichen

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.

Screenshot Spaltenanzeige nach dem Extrahieren

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.

Screenshot Text nach Trennzeichen

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

Screenshot Spalte nach dem Extrahieren des restlichen Textes

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.

Screenshot Spaltenkategorie ändern

Hier das Ergebnis.

Screenshot Ergebnis der Spaltenanpassung

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

Screenshot Power Query Editor schließen

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

Screenshot Tabelle importieren

So sieht das Ergebnis dann aus.

Screenshot Ergebnis Tabelle

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.

Screenshot 2023 04 14 104429 - nuboRadio

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

Screenshot Laden in..

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“.

Screenshot Tabellenkategorie ändern

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.

Screenshot Warnhinweis

Danach siehst du deine Pivot Tabelle.

Screenshot Pivot Tabelle

Screenshot 2023 04 14 105123 - nuboRadio

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.

Screenshot Hinzufügen einer neuen Datei

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.

Screenshot Dateien aktualisieren

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.

Wir freuen uns wie immer von dir zu hören per E-Mail, Kontaktformular oder auf Social Media.

Wusstest du, dass du diese Folge ganz einfach mit deinen Freunden teilen kannst? Klicke hier auf den gewünschten Kanal und fertig. Probiere es gleich mal aus.

 

nuboLinks

Power Query – „Datenimport 2.0“ Part 1 – nuboRadio (nuboworkers.com)
Excel – Fragen an den Profi – nuboRadio by nuboworkers
Informationen zu Power Query in Excel – Microsoft-Support

Dominique

Die Anwendungen aus Microsoft Office 365 bieten uns viele Möglichkeiten zur Zusammenarbeit und Kommunikation in der digitalen Welt. Wie diese eingesetzt werden können stellt euch Dominique mit Best Practice aus dem Projektalltag vor und informiert über neu Funktionen der Tools.

Beitrag jetzt teilen: