Power-Query #5/6: Daten aus gesamten Ordnern abfragen - Absoluter Geheimtipp!
Вставка
- Опубліковано 8 січ 2018
- Mit Power-Query kannst du Daten aus unterschiedlichen Quellen abrufen und transformieren. In diesem Video zeige ich dir wie man mit Power-Query Daten aus gesamten Ordnern abfragen kann. Das bedeutet sobald eine Datei mit neuen Daten in den Ordner abgelegt wird, erscheinen diese Daten in unserer Abfrage.
Allein dieses Feature hätte mir in der Vergangenheit sicherlich 3 Monate reine Arbeitszeit für mich und meine Kollegen hätte einsparen können!!!
►► Excel & Power BI Online Kurse ►►
excelhero.de/excel-online-kurse/
Mehr zum Kursangebot findest Du hier ► excelhero.de/excel-online-kurse/
Hat dir das Video zu Power-Query gefallen? Ja, dann subscribe, lasse mir einen Daumen hoch da und schreibe deine Fragen in die Kommentare.
Nach langem Suchen hab ich durch dieses Video nun endlich eine Lösung auf mein Problem gefunden! Danke, ich werde es morgen bei der Arbeit direkt ausprobieren. Ich bin eigentlich überhaupt kein Excelcrack, aber ich finde es extrem interessant, was sich alles mit Excel machen lässt! Werde morgen vielleicht noch mals kommentieren, falls ich es doch nicht schaffe haha;)
Power Query ist echt eine tolle Sache und garnicht so schwer zu lernen. Freut mich, dass ich mot dem Video helfen konnte :)
Großartig 🙂
Hallo, vielen Dank für Deine tollen Tutorials! Habe mittlerweile einiges getestet, habe festgestellt, dass ich nur Worksheets mit gleichem Namen kombinieren kann. Ich schaffe es nicht, für Folge-Dateien das passende Worksheet als Parameter zu definieren. LG Dan
Hallo und danke für das Video. Ich würde es sehr hilfreich finden, wenn die Dateien bzw. Dateinamen mit einem Hyperlink geöffnet werden könnten. Ist das auf einem Einfachen Weg möglich? Danke und MfG
So und jetzt mal bitte für die Mac Version….dafür wäre ich super dankbar
Hallo! Ich habe 18 Dateien, die bereits alle gleich formatiert sind und auch Makros enthalten, diese 18 Dateien sollen nun zu einer Datei zusammengefasst werden und natürlich die gesamten Einstellungen und Formatierungen behalten (z. b. Bedingte Formatierungen bei Eintragungen in die Liste)leider klappt das Zusammenfügen nicht, kann das an den Makros in den Dateien liegen? Der Tabellenbereich wird ab Datei Nummer 2 auch schon nicht mehr erkannt.
Sehr gutes Video! Lässt sich das auch realisieren, dass ich eine Pivot daraus erstelle wenn die ersten zwei bis drei Zeilen nicht gleich sind wie der Rest? Oder brauche ich für jede Excel dieselbe Spaltenüberschrift wie für die Pivot üblich? Generell stellt sich mir die Frage ob ich für jede Excel Datei zuerst eine Überschrift einfügen muss? Beste Grüße
Hallo Deus,
ich würde einen weiteren Transformationsschritt hinzufügen der eine einheitliche Überschrift erstellt. Wenn man eine Spaltenüberschrift manuell ändert, hat man im M-Code die alte und neue Überschrift als Text stehen. Das führt dann natürlich zu Problemen wenn die Überschriften in den einzelnen Dateien unterschiedlich sind. Daher muss dieser Schritt dynamisiert werden. D.h. im Code muss Spalte 1, 2, 3 usw. nicht über den Namen, sondern über den Index zugegriffen werden.
Wie das geht findest du hier:
excelgorilla.com/power-bi/power-query/change-column-type-by-position/
Im Ergebnis hast du eine einheitliche Überschrift und dann sollte die Erstellung einer Pivot keine Probleme machen.
Viele Grüße
Daniel
Wirklich sehr tolle Videos. Danke für die Mühe! Ist es auch möglich aus einem Ordner bestimmte Dateien auszuwählen und aus denen zum bespiel nur bestimmte Werte für einen Vergleich darzustellen? Das habe ich nämlich nicht hinbekommen.
Wenn man den Ordner einliest stehen die unterschiedlichen Datein erst einmal als Liste mit dem Dateinamen in der Abfrage bevor die Inhalte ausgelesen werden. Dort kann man eingreifen und einen Filter setzen. Z.B. nach Dateiendung oder Namen filtern. Somit kann man die Dateien ganz genau ansteuern.
@@Excelhero Perfekt! Vielen Dank für den Tipp. Hat super funktioniert, weiter so!
Super Tutorial. Habe nur eine Frage: Was mache ich wenn meine Daten immer auf dem Sheet 2 sind?
Hallo Oliver,
wenn du eine Abfrage auf einen Order ausführst, kannst du die Schaltfläche Kombinieren aufklappen und dann "Daten kombinieren und transformieren" auswählen. Anschließen kommt der Vorschaudialog und dort kannst du die Arbeitsblätter und Tabellen auswählen aus denen die Daten kommen sollen. Hier kannst du also auch Sheet 2 auswählen.
Viele Grüße
Daniel
gutes video und einfach erklärt, vielen dank. hab jedoch eine frage, kann man das ganze als makro aufzeichnen, so dass die verbindung zum pfad immer besteht? ziel ist es mittels knopfdruck (makro zuweisung) die daten mit der filterung aus dem pfad automatisch zu holen?
Hallo Deniz,
ja das geht. Du kannst dafür den Makrorecorder nutzen und das aktualisieren der Power Query Abfrage aufnehmen und anschließen auf einen Knopf legen.
Viele Grüße
Daniel
@@Excelhero Danke für die rasche Antwort. Aber das Problem ist, dass das File aus dem Pfad täglich gelöscht und ein neues File mit gleichen Namen hinzugefügt wird. Verliert die PowerQuery da nicht die Verbindung zum Pfad?
Solange die Datei gleich heißt, im gleichen Ornder liegt und das Arbeitsblatt/Name der intelligenten Tabelle gleich bleibt sollte die Abfrage weiterhin funktionieren.
Das ist super erklärt! Aber wie schaffe ich es wenn ich die Namen der verschiedenen Ordner in einem Ordner auslesen möchte? Geht das damit auch?
Das geht auch mit Power Query. Man erhält eine Auflistung alles Elemente im Ordner und kann sich dann die Namen der Ordner ausgeben lassen.
Hallo tolles Video! Ich finde in deinen Videos leider nicht wie ich Daten aus SAP abfragen kann bzw wie ich Ordner mit aktualisierten SAP Daten erstellen kann
Bzw. das Problem liegt wahrscheinlich an den txt Dateien die sich neben den Excel Dateien in diesem Ordner befinden. Somit werden mir nur die txt angezeigt und nicht die xls.
Du kannst im ersten Schritt z.B. nach einer bestimmten Dateiendung filtern, damit dir nur die Excel-Dateien angezeigt werden.
Hallo,
von allen Videos über dieses Thema erklärst du es mit Abstand am besten. Leider habe ich bisher noch keine Lösung zu meinem Problem gefunden.
Ich mache eine Ordnerabfrage und bekomme auch alle Daten so wie es ich es haben möchte. Es Handelt sich um Bilddateien mit Namen und Pfadangabe. Jetzt würde ich gern die Liste nehmen und zu jeder Zeile einen Titel und Beschreibung manuell hinzufügen, quasi eine weitere leere Spalte. Problem bei Änderungen in den Ordnern (neue Bilder/alte Löschen) verschiebt sich natürlich das ganze. Eine Idee ob das irgendwie Umzusetzen ist?
Hallo Curry King,
wenn du das Abfrageergebnis in dein Arbeitsblatt lädst ist dies eine intelligente Tabelle. Wenn du rechts davon eine Spaltenüberschrift eingibst wird diese Tabelle um eine Spalte erweitert. Dort kannst du dann deine Kommentare reinschreiben. Falls ein Datensatz bzw. Zeile gelöscht wird, verschwindet die Zeile inklusive des Eintrags in der neuen Spalte. Wenn neue Einträge dazukommen werden diese mit leerem Kommentarfeld hinzugefügt.
Viele Grüße
Daniel
@@Excelhero Danke für die schnelle Antwort. Das mit der Spalte klappt schon mal, leider verschiebt ihr wieder alle Einträge/Kommentare sobald was neues dazu kommt wenn ich auf "Aktualisieren" drücke. Arbeite jetzt mit SVERWEIS und füge neue Daten Manuell in ein neunes Arbeitsblatt hinzu.
Wie sieht es denn aus, wenn ich Datein mit untersiedlichen Formaten zsammenfügen will ? Z.B., aus Word, Power-Point oder gar aus einer PDF ? Kann ich die auch mit Powerquery zusammenführen ? Ansonsten genial.
Hallo Thomas, aktuell lässt sich nur PDF als Quelle auswählen. Man könnte vorher aber auch per VBA die Word und PPT Dateien öffnen und als PDF abspeichern und dann mit Power Query einlesen.
Hallo Excelhero,
mit Hilfe Deiner Excel-Videos bin ich in Power Query eingestiegen. Vielen Dank für die sehr guten Tutorials.
Ich hab da ein Problem, bei dem ich nicht weiterkomme. Ich habe eine Abfrage aus einem Ordner erstellt. Leider bekomme ich die Daten doppelt angezeigt! Als Beispiel: Meine erste Datei im Ordner hat 39 Zeilen, nach der Abfrage mit Power Query habe ich aber 78 Zeilen. Wie kann ich das Problem lösen?
Hallo Christian, das ist komisch. Hattest Du die Datei vielleicht gerade offen als du die Abfrage durchgeführt hast?
Hallo Excelhero, nein, die Datei war nicht geöffnet. Ich habe mehrere Dateien in dem Ordner liegen und auch dort habe ich die doppelte Anzahl an Zeilen.
Super Tutorial. Funktioniert das auch mit einem Ordner der im SharePoint abgelegt ist?
Ja, das tut es.
@@Excelhero Irgendwie will das bei mir nicht funktionieren. gibt es dazu eine extra Beschreibung?
Hallo
Ist es möglich eine Kommentarspalte einzufügen , wo die Kommentare bestehen bleiben auch wenn man die Datenbasis aktualisiert?
Hallo Peter;
am besten wäre es eine separate Kommentartabelle zu pflegen und diese dann mit Power Query zu landen und an die ursprünglichen Abfrage anzufügen.
Hallo Leute, wie kann ich bestimmte Spalten aus mehreren Dateien abfragen?
Die Datei enthält ca. 25-30 Spalten mit verschiedenen Spaltennamen, aus denen ich nur 4 brauche.
Bei jeder neu eingefügten Datei in den Dateipfad variiert die Spaltenanzahl und ich kriege eine Fehlermeldung das die spalte "xy" nicht vorhanden ist.
Danke für eure Hilfe
Hallo Paul,
dann wird es ein bisschen komplizierter. Der der Erstellung von Abfragen werden die Spaltennamen in den M-Code geschrieben. Die entsprechenden Stellen müssten dynamisch gestaltet werden. Also so, dass z.B. immer Spalte Nummer 5,6,7,8 ausgewählt werden und das unabhängig vom Namen. Dafür brauchst du 2 Transformationsschritte:
Spaltennamen1 = Table.ColumnNames(#"Vorheriger Schritt"){0}
Hier wird aus dem vorherigen Schritt die Spaltennamen ausgelesen und der erste Spaltenname ausgewählt. Bei Power Query beginnt der Index bei 0. D.h. 0 = 1. Eintrag 1 = 2. Eintrag usw.
Wir haben jetzt also den Spaltennamen aus Spalte 1. Dies Information wird im nächsten Transformationsschritt verbaut. hier wählen wir dann die 1. Spalte aus aber ohne das der Spaltennamen explizit im Code steht.
Spaltennamen2 = Table.Column(#"Vorheriger Schritt", Spaltennamen1)
Mit diesem Umweg kann man Power Query Abfragen dynamisch gestalten.
Mehr dazu findest Du in meinem Kurs.
Viele Grüße
Daniel
Gibt es eine Möglichkeit nur die neuste Datei aus einem Ordner zu nehmen?
Ja, man könnte einen Filter auf das Erstellungsdatum der Dstei setzen.
Bei mir kommt dieses Vorschaufenster nicht. Was mache ich falsch?
Haha hat sich erledigt. Die zu importierenden Dateien waren noch geöffnet #anfängerfehler
Ich kann keinen Ordner als Datenquelle in Mac auswählen. Ist das wirklich so? :-( Vielen Dank
Hallo Jakeline, ich hoffe das die Mac-Version in Zukunft alle Feature der PC-Version bekommen wird.
Es funktioniert einfach nicht, obwohl die Struktur meiner Files exakt gleich ist 🤷🏻♂️
Nach diesem Video bin darüber erstaunt, dass nicht mehr Excel-Nutzer diese Seite aufgerufen und kommentiert haben.
Microsoft hat leider in der Vergangenheit nicht viel Werbung für die Power Tools gemacht und daher sind die relativ unbekannt. Das ist schade, da man z.B. mit Power Query in bestimmten Anwendungsfällen ungeheuer viel Zeit einsparen kann.
als Absoluten Geheimtipp würde ich das nicht bezeichnen.