Power Query: Datenimport von ganzen Ordnerinhalten automatisieren
In unserem vorherigen Blogbeitrag haben wir eine Exceldatei zur Verfügung gestellt, die den Import von allen Exceldateien, die sich in einem Ordner befinden, automatisiert. Diese Datei haben wir nun noch etwas überarbeitet und Filter- und Kontrollmöglichkeiten angelegt:
Download: [wpfilebase tag=file id=12 tpl=filebrowser /]
Im ersten Blatt gibt man den Ordnerpfad an und in der Tabelle darunter kann man bestimmte Inhaltstypen der Excel-Dateien von vornherein vom Import ausschließen:
Dann geht’s los: Daten – Alle aktualisieren 2x hintereinander drücken. Der erste Befehl aktualisiert die Abfragen, der zweite aktualisiert die Pivotberichte, die zur Kontrolle gedacht sind.
Im Blatt „Importdateien“ sieht man sämtliche Dateien, wo sie liegen, Änderungsdatum und Dateigröße:
In der letzten Spalte könnte man jetzt die Dateien markieren, die man nicht importieren möchte.
Im nächsten Blatt „Dateielemente“ sieht man jetzt auch alle Elementtypen. Bei Excel-Dateien kommen hier vor allem: Normale Arbeitsblätter, speziell als Tabellen formatierte Felder sowie Namen/benannte Bereiche in Frage. Hier muss man dann auch aufpassen: Eigentlich sollte man entweder nur die Blätter, oder die anderen Typen: Tabellen und Namen auswählen, sonst importiert man viele Dinge doppelt (am besten also gleich im ersten Blatt entsprechend auswählen).
Rechts kann man wieder Filter setzen.
Um das jetzt besser kontrollieren zu können, gibt’s noch 2 Grafiken:
Hier kann man Ausreißer ganz gut erkennen.
Nun kann man sich die Summen des Imports anzeigen lassen und kontrollieren:
Details werden im nächsten Blatt angezeigt („Importkontrolle“)
Und zu guter Letzt dann das Blatt, welches lediglich die Spalten der Importdateien enthält:
Interessant an der neuen Lösung ist auch, dass jetzt alle Spalten automatisch eingelesen werden, auch wenn sie andere Spaltenüberschriften als die zuerst importierte Tabelle haben. Dies weitet die Anwendungsmöglichkeiten nochmal aus und erhöht das Automatisierungspotential zur Fehlervermeidung.
Sollte Ihr Export die maximale Zeilenanzahl von Excel überschreiten, kann man auch einen csv-Export vornehmen, hier ist jedoch ein Makro erforderlich (http://www.powerpivotblog.nl/export-a-table-or-dax-query-from-power-pivot-to-csv-using-vba/). Dann muss man das Laden der Abfrageergebnisse in das Datenmodell umleiten.
Hier der kommentierte Code:
let
// Erste Filtertabelle holen: spezifische Dateielemente in den einzelnen Dateien
FilterType= Excel.CurrentWorkbook(){[Name="FilterType"]}[Content],
// Zweite Filtertabelle holen: spezifische Dateien
FilterFile= Excel.CurrentWorkbook(){[Name="FilterFile"]}[Content],
// Den zu importierenden Ordner öffnen
Quelle = Excel.CurrentWorkbook(){[Name="Dateipfad"]}[Content],
// Die darin befindlichen Dateien anzeigen
DateienAnzeigen= Folder.Files(Quelle{0}[Column1]),
// Spezifische Attribute dieser Dateien anzeigen
ShowAttributes = Table.ExpandRecordColumn(DateienAnzeigen, "Attributes", {"Kind"}, {"Kind"}),
FilterExcelFiles = Table.SelectRows(ShowAttributes, each ([Kind] = "Excel File")),
// Mit erster Filtertabelle verbinden
Content1= Table.AddColumn(FilterExcelFiles, "Custom", each Excel.Workbook([Content])),
// Mit der 2. Filtertabelle verbinden
JoinFile = Table.NestedJoin(Content1,{"Name"},FilterFile,{"Name"},"NewColumn"),
// Spalten aus der 2. Filtertabelle anzeigen
FilterFileAnzeigen = Table.ExpandTableColumn(JoinFile, "NewColumn", {"NichtImportieren"}, {"NichtImportieren"}),
// 2. Filter tatsächlich anwenden
FilterFile_ = Table.SelectRows(FilterFileAnzeigen, each ([NichtImportieren] = null)),
// 2. Filterspalte wieder löschen
EntfernteFilterspalte2 = Table.RemoveColumns(FilterFile_,{"NichtImportieren"}),
// Die Dateiattribute der zu importierenden Dateien anzeigen
ShowAttributes2= Table.ExpandTableColumn(EntfernteFilterspalte2, "Custom", {"Name", "Data", "Item", "Kind"}, {"t.Name", "t.Data", "t.Item", "t.Kind"}),
// Definieren, wie mit der Extraktion der Spaltenüberschriften in unterschiedlichen Elementtypen umgegangen werden soll
TheFormula = Table.AddColumn(ShowAttributes2, "Custom", each if [t.Kind] <> "Table" then Table.PromoteHeaders([t.Data]) else [t.Data]),
// Mit der ersten Filtertabelle verbinden
JoinFilterType = Table.NestedJoin(TheFormula,{"t.Kind"},FilterType,{"Art"},"NewColumn"),
// Filterfeld anzeigen
ShowFilterType = Table.ExpandTableColumn(JoinFilterType, "NewColumn", {"Import"}, {"Import"}),
// Imortfilter anwenden
FilterType_ = Table.SelectRows(ShowFilterType, each ([Import] = "ja")),
// Nicht benötigte Spalten entfernen
RemovedColumns = Table.RemoveColumns(TheFormula,{"Content", "t.Data", "Date accessed", "Date modified", "Date created"}),
// Dies ist die tolle Superformel, die alle Spaltenüberschriften aus allen zu importierenden Dateien konsolidiert (siehe: http://www.powerpivotpro.com/2015/01/power-query-for-excel-combine-multiple-files-of-different-file-types/)
MyList = Table.ToList(Table.RemoveColumns(Table.Distinct(Table.ExpandListColumn(Table.AddColumn(RemovedColumns, "ColumnHeaders", each Table.ColumnNames([Custom])), "ColumnHeaders"),{"ColumnHeaders"}),{"Name", "Extension", "Kind", "Folder Path", "t.Name", "t.Item", "t.Kind", "Custom"})),
// Nun öffnet man sämtliche zu importierenden Dateien mit dieser konsolidierten Überschriftenliste
Expanded = Table.ExpandTableColumn(RemovedColumns, "Custom",MyList)
in
Expanded
Denjenigen, die damit nicht viel anfangen können, aber PowerQuery gerne kennenlernen möchten, kann ich nur empfehlen, die Abfragen einfach mal nachzuklicken. Man klickt einfach auf einen Schritt nach dem anderen und kann dann die Formeln nachlesen und gleich sehen, was ausgegeben wird. Diese Transparenz ist einmalig finde ich und wirklich toll zum schnellen Lernen geeignet:
Im nächsten Beitrag zeigen wir, wie man diesen Import auf csv-Dateien umstellen kann und wie man selbst einige sinnvolle Anpassungen in der Abfrage vornehmen kann.
Stay Queryious! 😉
Brauchen Sie noch einen Xpertenrat?
Weitere Tutorials?
Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!
