Excel Power-Query dynamische Ordnerabfragen
Вставка
- Опубліковано 7 лип 2024
- Download der Arbeitsmappen unter:
excelhero.de/arbeitsmappen-001/
Mit Power-Query bzw. Daten abrufen und transformieren kannst Du Daten aus ganzen Ordnern abrufen und transformieren. In diesem Beispiel ziehen wir uns mit nur einer Abfrage Daten aus 3 CSV-Dateien, die in 3 unterschiedlichen Ordnern liegen. Das Ganze gestalten wir zusätzliche noch dynamisch, d.h. anstatt über den Power-Query Editor immer wieder die Ordnerpfade neu anzugeben, erstellen wir uns in der Arbeitsmappe eine Tabelle, wo wir die Ordnerpfade reinschreiben. Dann holen wir uns diese Informationen über eine Abfrage in Power-Query rein und erweitern die Einträge mit einer benutzerdefinierten Funktion. Im Endergebnis haben wir somit ein praktisches Tool mit dem wir flexibel Daten aus unterschiedlichen Ordnern ziehen können und das uns jede Menge Zeit bei der Arbeit mit Excel einsparen kann.
►► Excel & Power BI Online Kurse ►►
excelhero.de/excel-online-kurse/
Mehr zum Kursangebot findest Du hier ► excelhero.de/excel-online-kurse/
Danke für die lebensnahen Beispiele und die spitzenmäßigen Erklärungen.
Top erklärt. & Danke für die tollen Tipps und Anregungen !
Super erklärt - kann man ohne Probleme nacharbeiten
Finde ich auch super erklärt!
Super. Genau das habe ich gesucht.
Super erklärt...
Weiter so...
Gruß aus Norwegen
Supertoll - vielen Dank...!
Wieder etwas gelernt! Top
Sehr gutes Video
Merci :)
super tutorial! Werde es nun in meine Arbeit mit einbringen.
Das freut mich wirklich sehr! Mit Power-Query kann man sich wirklich jede Menge Zeit einsparen!
@@Excelhero Und Zeitsparen brauche ich! :)
@@Excelhero und gibt es eine Abfragenfunktion für Ordner auch für Office 2013 Standard? Hatte ganz vergessen, dass ich softwaremäßig privat besser aufgestellt bin als im Job :(
Oder gibt es das etwa in Office 2013 Professional?
Für die Version gibt es ein kostenloses Power Query Addin auf der Microsoftseite zum Download.
Toll
Super Erklärung
Kann man auch die Ordnerpfade relativ zur auswertenden Excel Datei angeben ?
Hallo Xaver, was mir spontan als eine mögliche Lösung einfällt ist sich die Funktion ZELLE zu Nutze zu machen.
Mit = ZELLE("dateiname";A1) erhält man Dateipfad[Dateiname]Arbeitsblattname
Anschließend mit SUCHEN nach "[" suchen und dann mit LINKS den Dateipfad isolieren.
Zum Schluss den Dateipfad mit dem &-Zeichen und den Ordnernamen verketten.
Z.B:
=LINKS(ZELLE("dateiname";A1);SUCHEN("[";ZELLE("dateiname";A1))-1)&"Ordner1"
Damit kannst Du relative Ordnerpfade angeben, wenn sich die Zielordner auf gleicher Ebene oder tieferen Ebenen der Ordnerstruktur befinden. Wenn man eine Ebene höher springen möchte, müsste man schauen wie man den Dateipfad entsprechend gekürzt bekommt.
Viele Grüße
Daniel
danke für die äußerst hilfreichen videos!
ich habe excel 2016 plus, leider sehe ich in den abfrageoptionen den "Power Query-Editor" und "Datenschutz" nicht. weißt du wie ich das beheben kann?
Hallo, habe eine Frage zur dem Thema. Ich habe Power Query zur einem Arbeitsmappe erstellt und diese Arbeitsmappe wird von Benutzer genutzt, rund um die Uhr, allerdings muss er/sie/div sich immer aus der Mappe ausloggen damit ich meine Daten Aktualisieren kann. Wie kann ich Power Query einstellen sodass ich trotzdem verbinden kann obwohl er/sie/div in dem Arbeitsmappe arbeitet ?
Kann man auch den Anfang umkehren Excel Zellen als Ordnerpfade zu generieren ?
Wirklich super!
Eine Frage: Ich habe in den Quelldateien unterschiedliche Spalten (in Datei 1 heißt die Spalte anders als in Datei 2 oder fehlt sogar). Bisher habe ich die Dateien einzeln geladen, dann in der Query die Spaltenüberschriften korrigiert / gleich benannt, fehlende Spalten ergänzt und anschließend zu einer Gesamttabelle "angefügt".
Besser wäre gleich den ganzen Ordner mit den Quelldateien zu laden, wie du es vorschlägst. Doch dann fehlen mir die Zwischenschritte und die Zusammenführung der Dateien funktioniert nicht. Hast du eine Idee?
Hallo Sven,
du könntest die Transformationsschritte so anlegen, dass sie für alle deine Dateien gültig sind und jeden Sonderfall abfangen. Hier müsstest du dann mit Bedingungen arbeiten und wahrscheinlich auch den M Code anpassen. Z.B. wenn Überschrift AAA dann in Überschrift BBB ändern. Wenn nur 3 Spalten dann noch eine Spalte hinzufügen usw.
Es ist auch möglich sein verschiedene "Bearbeitungszweige" anzulegen, d.h. ein Transformationsschritt muss sich nicht unbedingt auf den vorherigen Schritt beziehen.
Z.B. in Schritt1 hast du die Daten. Schritt2 prüft ob 3 Spalten oder 4 Spalten vorhanden sind. Wenn 3 Spalten Transformation sonst leer. Ein neuer Schritt3 bezieht sich dann auf Schritt1 und nimmt wieder eine Prüfung vor. Wenn 4 Spalten dann Transformation, sonst leer. Der finale Schritt 4 schaut dann ob es ein Ergebnis bei Schritt 2 oder 3 gibt und gibt dieses aus. Somit hast du dann verschiedene Transformationspfade die deine Daten durchlaufen können und am Ende immer im gleichen Format ausgegeben werden.
VG Daniel
gibt es auch eine Lösung bei mehreren PDF Datein die man über Ordner einlesen möchte? Bei dieser Möglichkeit zeigt er natürlich immer wieder an, dass er keine Excel Tabelle gefunden hat. Bis zur Minute 4:53 klappt dementsprechend alles wunderbar. Ziel ist es, alle Transaktionen der Aktienkäufe (in Form PDF) in Excel einzulesen, ohne das jdes PDF Dokument einzelnd ausgewählt werden muss. Sie machen einen unglaublich guten Content. Besten Dank.
Hi Excelhero, danke dir für deine tollen Videos. Auch bei diesem habe ich die Vorgehensweise 1 zu 1 befolgt. Nur tritt ein Fehler bei dem Punkt auf, an dem ich auf die Arbeitsblätter der Dateien zugreifen will (Videozeit: 5:00). "Expression.Error: Es wurde keine Excel-Tabelle mit dem Namen "Tabelle2" gefunden." Um den Fehler ausfindig machen zu können: die Abfrage funktioniert auch mit .xlsx Dateien? Oder nur mit CSV?
Abfragen funktionieren auch mit xlsx Dateien. Wenn du eine bestehende Abfrage anpassen möchtest, musst du bei dem ersten Transformationsschritt Quelle, den richtige Datentyp und die richtige Quelle einstellen. Bei neuen Abfragen geschieht das automatisch.
@@Excelhero Wow, danke für die schnelle Antwort. Ich hatte die Abfrage neu aufgesetzt. Doch wird an diesem Schritt immer versucht die "Tabelle2" in der Datenquelle zu finden. Selbst wenn ich die Tabelle in der Datenquelle auf diesen Namen ändere, geht es nicht.
Bei einer neuen Abfrage auf eine Excel-Datei bekommt man im Dialog die Auswahl zwischen Arbeitsblättern und Tabellen bzw. intelligente Tabellen. Hast du dort das richtige ausgewählt?
Super erklärt. Leider friert mir mein Power query regelmäßig ohne Fehlermeldung ein, sobald man die Abfrage lädt oder bearbeiten möchte... Auch etliche Reparaturinstalationen helfen nicht.
Ich würde versuchen einmal Office ganz zu deinstallieren und dann neu aufzusetzen. Bei Abfragen mit großen Datenmengen kann es eine Zeit lang dauern bis die Abfrage fertig geladen ist. In den Fällen kann man bei den Abfrageeinstellungen "Aktualisierung in Hintergrund" aktivieren.
Gibt es auch eine Möglichkeit wöchentlich neu erstellte Ordner in die Abfrage einzubinden?
Hallo Holger, ja einfach den übergeordneten Ordner abrufen und dann einen Filter setzen, so dass nur die gewünschten Ordner bzw. Datei in der Abfrage berücksichtigt werden.
Vielen Dank. Ist es normal, dass bei Ordnerabfragen er dann alle Datein aus dem Ordner in eine Abfrage packt?
Muss ich dann unbedingt immer Laden aus Datei als Alternative nehmen?
Bsp: Ich habe rotierende Datein (Datein kommen neu in den immer gleichen Ordner hinzu / Datein werden entfernt). Meine Vorstellung war jetzt, dass ich über Ordnerabfrage die Datein auch einzelnd anpacken kann. Leider sieht das nicht so aus, dass das geht? Order beachte ich etwas nicht?
Ich würde mir gerne am Anfang ersparen X Mal per" Laden aus Datei aufzurufen"
Super Videos.
Hallo Skott, standardmäßig werden bei der Ordnerabfrage alle Dateien in dem Order geladen. Man kann jedoch relativ früh in der Abfrage auch noch einen Filter setzen so das dann nur bestimmte Datei aus dem Ordner geladen werden.
@@Excelhero super dann gucke ich mir das Mal an. Besten Dank :) ..also kurz "Abfrage aus Ordner macht Sinn wenn verschiedene Abteilungen gleiche Daten anpacken, die dann jedoch an unterschiedlichen Pfaden gespeichert werden?" Kann man das so sagen?
Genau wenn Dateien gleicher Struktur in einem Order liegen und diese zu einem Datensatz zusammengefasst werden sollen.
Lassen sich bestehende Abfragen auf einzelne Datein auch auf diese Weise erweitern?
Ja man kann den Dateipfad in eine Zelle schreiben. Daraus eine Tabelle machen und über eine Abfrage den Wert in den PQ-Editor bringen und anschließend das Abfrageergebnis über Drilldown in eine Variable umwandeln, die man dann in der ursprünglichen Abfrage verbauen kann.
Dadurch kann der Nutzer über den Tabelleneintrag steuern welche Datei abgefragt werden soll.
Viele Grüße
Daniel
@@Excelhero Top! Danke
Funktioniert diese Art der Zusammenführung auch, wenn die von mir aus den Quelldateien benötigten Tabellen Berechnungen enthalten, die tagesaktuell sein sollen (die entsprechenden Formeln beinhalten Datumsdifferenzen mit HEUTE(), und berechnen viele Zellwerte basierend auf dieser Datumsdifferenz)? Bisher hatte ich immer das Problem, dass bei geschlossenen Quelldateien immer nur der "letzte Stand" (= vom letzten Öffnen der Datei), aber nicht das "jetzt" (mit aktualisierten Werten) gezogen wurde... Ich möchte also, dass die entsprechenden Formeln zum Zeitpunkt des Datenabrufs ausgeführt, und dann der (tagesaktuell neu) berechnete Wert erst PQ übertragen wird. Gibt es eine Lösung für solch ein Problem? Aufgrund der Menge an Quelldateien funktioniert es nicht, vorher alle Dateien manuell zu öffnen, zu aktualisieren und zu speichern. Zusätzlich enthalten die Quelldateien noch Abrufe zu Aktienkursen, sodass natürlich auch dort die "jetzt"-Werte benötigt werden.
Hallo Timo, ich würde mir für diesen Zweck ein Makro schreiben welches zuerst die Quelldateien öffnet, aktualisiert und spreichert und anschließend die PQ Abfrage ausführt. Das könnte man evtl. auch per Power Automate laufen lassen. Ansonsten müsste man schauen, ob man die Heute() Funktion in der PQ Abfrage abbilden kann. Problematisch wird es dann aber mit der Abfrage der Aktienkurse.
Viele Grüße
Daniel
ich muss Daten aus mehreren täglichen Excelfiles, aus jeweils drei Worksheets aggregieren.
wie schaffe ich das?
Am einfachsten wäre es die Dateien in einem Ordner abzulegen und 3 Abfragen zu erstellen, die jeweils das 1.,2.,3 Arbeitsblatt von allen Dateien abrufen und die Ergebnisse dieser 3 Abfragen dann mit Abfrage anfügen vereinen.
Ich habe mir vor einiger Zeit, mit den Videos von dir, eine Power-Query Datei erstellen können "Hurra". Jetzt fehlt mir nur noch eines zum Erfolg^^ Wie bekomme ich heraus wie man Zahlen zählt? Ich mein, wie oft kommt die Zahl 10, in H2 bis H161 vor!? Edit : Meine Erstellte Formel >> =ZÄHLENWENNS(D4:D161;D4;H4:H161;F165)
Hallo,
wenn es nur um das zählen der Zahl 10 geht, reicht folgende Formel bereits aus.
=ZÄHLENWENN(H2:H161;10)
Die 10 kannst du auch mit einem Bezug auf eine Zelle ersetzen.
Wenn du in A1 bis A10 die Zahlen 1 bis 10 stehen hast, kannst du mit
=ZÄHLENWENN(H2:H161;A1)
=ZÄHLENWENN(H2:H161;A2) usw.
schnell schauen wie oft die Zahlen 1 bis 10 in der Spalte vorkommen.
Alternativ kannst du aus H1:H161 eine Pivot-Tabelle machen und dann die Spalte sowohl in den Zeilenbereich als auch in den Wertebereich ziehen und anschließend bei den Wertfeldeinstellungen -> Werte zusammenfassen nach -> Anzahl.
Dann bekommst du sofort die Anzahl aller eindeutig vorkommenden Werte.
Viele Grüße
Daniel
Wirklich super Video: Ich stehe jedoch nun vor der Herausforderung, dass in meinen angegebenen Ordnern noch andere Dateien enthalten sind, wodurch mir bei den entsprechenden Ordnern immer wieder ein Fehler ausgegeben wird. Könnte ich aus der Tabelle mit den Ordnerpfaden auch direkt auf Dateipfade verweisen? Danke für jede Rückmeldung!
Hallo Florian, dafür setzt man am besten gleich nach dem ersten Transformationsschritt in der automatisch angelegten Funktion einen Filter, damit nur die gewünschten Dateien verarbeitet werden.
Bei abfrageoptionen habe ich leider keine power query option und bei sicherheit gibts nur ein Kästchen. Gibts wohl eine lösung?
Dann bist Du wahrscheinlich noch mit einer älteren Version unterwegs.
Lässt sich auch ein Parameter auf einzelne Dateien erstellen? In meinem Hauptordner sind Unterordner in denen xlsx Files liegen. Ich möchte gezielt immer ein File per Parameter aufrufen und die Inhalte anzeigen lassen...
Hallo Tony, ja das geht. Den Dateipfad kann man in eine Tabelle mit einer Zeile und einer Spalte schreiben und dann eine Abfrage auf die Tabelle machen. Das Abfrageergebnis dann zu einer Liste konvertieren und diese kann man dann als Parameter in der Quellenangabe einer Abfrage verbauen.
Hallo! Funktioniert es auch mit xlsx Dateien? Weil ich versuche es aber ein Fehler taucht auf. Danke für die Hilfe.
Ja funktioniert auch mit xlsx Dateien
@@Excelhero wenn ich die Dateien kombinieren möchte, sehe ich die folgende Fehlermeldung:
Parameter 1 we couldn’t find an excel table named Dynamicpath.
Würdest du mir bitte helfen? Danke dir
tolles Video, nur wenn ich xlsx-Datei verwende weiß ich nicht wo ich genau das Datenformat in der Funktion ändern muss.
Grüße
Direkt am Anfang beim ersten oder zweiten Transformationsschritt. Dort auf das Zahnrad klicken und den Dateityp ändern.
ich habe auch .xlsm Dateien und immer wieder wechselnde Ordnerpfade, die ich in Excel angeben will. Ich bekomme auch keinen Content aus den .xlsm-Dateien. Das Video hilft mir immer nur bis dahin, wo es heißt, dass nun die Inhalte geladen werden. Aber das klappt bei mir nicht.
@@stewa1096 Nach dem Auswählen der Datei wird eine Vorschau gezeigt und man wird gefragt, ob man Daten aus einem Arbeitsblatt oder einer Tabelle ziehen möchte. Dabei kann es manchmal zu Verwechselungen kommen vor allem wenn man die Arbeitsblätter und Tabellen ähnliche Namen haben. Dort würde ich noch einmal nachschauen.
Excelhero
Ich finde nicht die Stelle an der ich vom den Dateityp csv auf xlsx umstellen kann.
Das ist gleich beim ersten Transformationsschritt "Quelle". Dort einfach auf das Zahnrad klicken und dann den Dateityp ändern.
Die Anleitungen zu Power Query sind sehr verständlich gestaltet. Leider hat der Download der Arbeitsmappen nicht funktioniert. Ich habe keine Bestätigungsmail erhalten.
Hallo Jürgen, ich glaube mein Emailprogramm spinnt. Ich sehe Dich nicht bei mir im System.
Du kannst Dir hier die Begleitdateien runterladen.
excelhero.de/download-001/
Viele Grüße
Daniel
Wenn jetzt Spalten in den Quelldateien gelöscht, hinzugefügt oder geändert werden, wie kann man das dann am besten aktualisieren ? Ich scheitere seit Stunden...
Hallo Mathias,
du hast 3 Möglichkeiten.
1) Manuell die Abfrage an den entsprechenden Stellen abändern, also bei den Transformationsschritten, bei denen Änderungen aufgetreten sind.
2) Dynamisierung der Abfragen mit Parameter. Dafür müssen die Informationen darüber welche Spalten vorhanden sind, über einen Parameter in die Abfrage einfließen.
3) Dynamisierung der Abfrage mit VBA. Per VBA wird geschaut welche Spalten vorhanden sind und dann der M Code der Abfrage entsprechend angepasst.
Ein Tipp noch, der die obigen Schritte evtl. schon überflüssig macht.
Wenn du z.B. Spalten löscht ist es besser nicht eine bestimmte Spalte zu löschen, sondern anzugeben welche Spalten übrig bleiben sollen. Dadurch kommt es später nicht zu Problemen wenn z.B. zusätzliche ungewünschte Spalten dazukommen. Diese Spalten können nicht nur über den Namen, sondern auch über die Spaltennummern ausgewählt werden.
Mehr dazu gibt es in meinem Kurs.
Viele Grüße
Daniel
@@Excelhero Super, danke für die Info :-)
Irgendwie habe ich nun ein Problem, die Dateien mit den Daten, Spinnen rum. For allem meine Formeln in den jeweiligen Dateien.
Hallo Mitsteiter,
du kannst auch in meine Facebook Gruppe kommen und dort einen Screenshot posten. Dann wird dir meist ganz schnell geholfen.
facebook.com/groups/excelmeistern/
Viele Grüße
Daniel
Dein Video ist super erklärt, leider tritt bei mir ebenfalls der Fehler von @Kaczinsky85 auf ("Expression.Error: Es wurde keine Excel-Tabelle mit dem Namen "Tabelle2" gefunden."), sobald ich die Ausführung ab 4:56 ausprobieren will. Gibt es inzwischen eine Lösung des Problems? Vielen Dank schon einmal im Voraus :-)
Generell muss man darauf achten, dass die Arbeitsblätter in allen Dateien den gleichen Namen haben.