2D Tabellen (Matrizen) mit PowerQuery kombinieren

[Hajo1]

Guten Morgen
aufgrund eines Forum-Tipps möchte ich ca. 20 Excel-Dateien via PowerQuery hochladen, kombinieren und bearbeiten:
- jede Excel Datei besteht aus diversen Worksheets mit 2D-Tabellen, also festen Zeilen & Spalten und darin Inhalte ( => Gewinn und Verlustrechnung)
- alle Dateien haben die gleiche Struktur
- die Dateien müssen aber zunächst zurechtgeschnitten werden, d.h. Löschen von Rand-Zeilen und -Spalten.
Wenn ich das mit einer Datei exemplarisch mache und danach den gesamten Ordner analog laden möchte, werden die Zuschnitte verworfen und die ganzen Randzeilen/spalten sind alle wieder mit dabei.
Was mache ich falsch - wie macht man's richtig? (bin PQ-Anfänger, offensichtlich ...) 
Danke, Hajo

[Warkings]

Wenn alle Dateien die gleiche Struktur haben, müssen sie vorher nicht zugeschnitten werden, was immer das auch bedeutet.
Für Powerquery muss man vorher keine "Rand-Zeilen/Spalten" löschen.

Beispieldaten/-dateien würden sicher helfen.

[Ralf A]

- die Dateien müssen aber zunächst zurechtgeschnitten werden, d.h. Löschen von Rand-Zeilen und -Spalten.
Wenn ich das mit einer Datei exemplarisch mache und danach den gesamten Ordner analog laden möchte, werden die Zuschnitte verworfen und die ganzen Randzeilen/spalten sind alle wieder mit dabei.
Was mache ich falsch - wie macht man's richtig? (bin PQ-Anfänger, offensichtlich ...) 
Danke, Hajo

...ich vermute mal, Du hast in der Quelle mehr Spalten, als Du im Ergebnis brauchst und willst evtl. einige Zeilen löschen (mehrfache Überschriftszeilen o. ä,?). 
Da PQ ja bei Import aller Dateien aus einem Ordner per Mausklick gerade für Beginner recht unübersichtlichen Code schreibt, wird es vermutlich auch nicht helfen, Dir zu sagen, schau Dir die von Dir für diese eine Datei exemplarische erstellte Abfrage an und versuche sie im automatisch erstellten Code anzupassen.  
Du machst also nix falsch, Dir fehlt es lediglich an Erfahrung. Das ist in PQ nicht anders als in VBA. Der Recorder zeichnet alle notwendigen Schritte auf, aber wenn man das dynamisieren will, muss man schon selbst Hand anlegen.
Lade doch mal eine zip Datei mit 2 Beispieldateien in einem Ordner und einem Wunschergebnis hoch.

[Andreas Killer]

aufgrund eines Forum-Tipps möchte ich ca. 20 Excel-Dateien via PowerQuery hochladen, kombinieren und bearbeiten:
- jede Excel Datei besteht aus diversen Worksheets mit 2D-Tabellen, also festen Zeilen & Spalten und darin Inhalte ( => Gewinn und Verlustrechnung)
- alle Dateien haben die gleiche Struktur

Kopiere zunächst ein paar Dateien in einen Ordner.

Daten \ Daten abrufen \ Aus Datei \ Aus Ordner \ Ordner anwählen \ Kombinieren \  Kombinieren und transformieren

Dann kriegst Du ein Vorschaufenster für die erste Datei und kannst auswählen was Du aus der Datei willst. Wähle das Blatt mit den Daten \ Ok

Jetzt macht Dir PQ eine ganze Menge an Abfragen, eine davon heißt "Beispieldatei transformieren", die wählst Du an. In dieser fügst Du nun die Schritte hinzu was Du aus dem Blatt haben willst, kannst filtern, usw.

Wenn Du damit fertig bist gehst Du zurück in die letzte Abfrage, diese hat den Namen Deines Ordners. In der ersten Spalte siehst Du den Namen der Datei und in den Spalten daneben was aus den einzelnen Dateien, gemäß Deiner Abfrage "Beispieldatei transformieren" importiert wurden.

Diese Abfrage lädst Du nun via "Schließen & Laden in..." als Tabelle in ein beliebiges oder neues Blatt. Fertig.

Wenn das nun alles so aussieht wie es sein soll, dann kannst Du die Abfrage auf den Originalordner mit allen Dateien umlegen. Dazu editiere die Abfrage nochmals, wähle dazu auf der rechten Seite den Source (oder Quelle) Schritt, dann siehst Du in der Bearbeitungsleiste sowas wie = Folder.Files("C:\Users\Killer\Downloads\Neuer Ordner")

Ändere hier den Pfad oder klicke auf das Zahnrad neben Source (Quelle), dann kannst Du den Pfad via Dialog ändern. Alles klar?

Andreas.

[Hajo1]

Ey Leute, Ihr seid super, so konkret und schnell -  vielen Dank!

In den Randspalten/Zeilen steht tatsächlich viel ungeordnetes totes Zeug, was auch nicht einheitlich ist, daher muss ich es entfernen ...

Ich hab also im weiteren den Ansatz von @Andreas aufgegriffen:  
Bis zu der Zeile 
"Wenn DU damit fertig bist ..." hat's auch geklappt, aber dann: 
".... geht Du zurück in die letzte Abfrage" - wohin und wie zurück, welche letzte Abfrage (ist ja meine erste ever hier ...)? 
"... diese hat den Namen Deines Orders" - mein Datenordner mit den Dateien heisst 'Data' und das taucht unter 'Andere Abfragen' auch auf, allerdings mit gelbem !-Dreieck. Ist die Abfrage hier gemeint?

Sorry für das kleinschrittige Vorgehen, aber will hier nix schrotten (... ja, hab die Daten kopiert)

---

.... wenn ich unter 'Andere Abfragen' zB auf  'Data' mit dem gelben Dreieck gehe, kommt "Expression Error: Column 16 not found" - klar, die hatte ich ja vorher als Randspalten gelöscht. Da geht's dann auch nicht weiter.

[Ralf A]

.... wenn ich unter 'Andere Abfragen' zB auf  'Data' mit dem gelben Dreieck gehe, kommt "Expression Error: Column 16 not found" - klar, die hatte ich ja vorher als Randspalten gelöscht. Da geht's dann auch nicht weiter.

...tja... das kommt daher, dass Deine Quelldateien halt doch nicht einheitlich aufgebaut sind. Mindestens eine hat 16 Spalten oder mindestens eine hat keine 16 Spalten. Da hilft Killer's Erklärung leider auch nicht weiter, denn das klappt nur, wenn es keine Abweichungen gibt. Hinzu kommt, dass es einen Unterschied macht, ob die Daten einfach so im Tabellenblatt, oder in einem Namensbereich oder einer formatierten Tabelle liegen. Das alles wissen wir nicht. Klar ist unter diesen Umständen jedoch, das Problem muss händisch angepasst werden.
Vielleicht hilft Dir ja die Anleitung im Anhang weiter...


  Visualisierte Schrittfolge.pdf (Größe: 489,36 KB / Downloads: 7)

[Andreas Killer]

.... wenn ich unter 'Andere Abfragen' zB auf  'Data' mit dem gelben Dreieck gehe, kommt "Expression Error: Column 16 not found" - klar, die hatte ich ja vorher als Randspalten gelöscht. Da geht's dann auch nicht weiter.

Doch das geht, alle diese Probleme kann man lösen.

Öffne mal die paar Dateien die Du kopiert hast und dann tauscht Du die Daten (NICHT die Überschriften!) gegen irgendwas aus, speicher die Dateien, pack alles zusammen, lad es hier hoch und dann zeige ich Dir wie das geht.

Andreas.

[Warkings]

Ich verweise auf #2

[Hajo1]

Danke, Ralf, für Deinen Hinweis, bzgl. der nicht-einheitlichen Dateien.

Ich habe also im 'Schritte-Recorder' rechts den letzten Befehl zur Spaltenlöschung gelöscht (X gedrückt) - und das gelbe Warndreieck war verschwunden.
Die Zeilenlöschung habe ich belassen - scheint nicht zu stören ... (?!)

Nun weiter im Skript von Andreas  - die neue Abfage 'Data' lässt sich nun via 'Schliessen/Speichern in ...'   einem Tabellenblatt des übergeordnet laufenden Excel ablegen und - voila - alle 4 Test-Dateien sind mit allen Daten erfasst und korrekt abgelegt und - weiter noch! - die entfernten und rückgängig-entfernten Spalten sind auch gelöscht, wie ich es eigentlich haben wollte. (Warum das Storno der Spaltenlöschung trotzdem noch aktiv ist und die Löschung der Spalten in allen Dateien bewirkte, führt noch zu Unwohlsein bei mir, aber egal - vielleicht/hoffentlich ein Später-Problem, aber komisch bleibt's doch). 
Aber soweit so gut - ich bin ein großes Stück voran gekommen.

Nächster Schritt ist die Anwendung auf alle Daten, nicht nur die 4 Test-Files. Dazu gebe ich gemäß Skript den neuen Pfad unter Quelle->Zahnrad ein, und sehe alle Dateien im neu geänderten Quellordner.
Bei der Auswahl der relevanten xlsx-Dateien aus dem großen Gesamtordner stehen allerdings auch ein paar andere Dateien drin, die ich abwählen muss - wenn ich unter 'Abfrageschritte' auf 'Quelle' doppelklicke, geht eine Tabelle mit allen Datein im Ordner auf. das Löschen der ungewollten Dateine mit'Zeilen verringern/löschen' funktioneirt aber nicht - gibt es hier einen Trick? 

Ich bin kurz vor der Lösung, um alles in Pivot bearbeiten zu können .... :- ))

---

Den Zwischenschritt von Dir, Andreas, habe ich erst jetzt gesehen, aber bin ja so auch weiter gekommen.

Wie ist das mit der Löschung der Zeilen zu sehen - warum hat er sie trotz Storno der Löschung rausgenommen?

[Ralf A]

Bei der Auswahl der relevanten xlsx-Dateien aus dem großen Gesamtordner stehen allerdings auch ein paar andere Dateien drin, die ich abwählen muss - wenn ich unter 'Abfrageschritte' auf 'Quelle' doppelklicke, geht eine Tabelle mit allen Datein im Ordner auf. das Löschen der ungewollten Dateine mit'Zeilen verringern/löschen' funktioneirt aber nicht - gibt es hier einen Trick? 

was heißt: funktioniert aber nicht? Oftmals spielt die Reihenfolge der Schritte eine entscheidende Rolle...

...schau mal im Script nach Schritt 2. Du kannst über den rechten Pfeil jeder Spalte (also auch die, mit den Dateinamen) alles das abwählen, was Du nicht angezeigt habe möchtest.