Power Query: Datenimport von ganzen Ordnerinhalten automatisieren

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:  IconPowerQueryQuickImportAllFilter (298.9 KiB)

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:

PQ_Neu_1Übersicht

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:

PQ_Neu_2Importdateien

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

PQ_Neu_3Dateielemente

Rechts kann man wieder Filter setzen.

Um das jetzt besser kontrollieren zu können, gibt’s noch 2 Grafiken:

PQ_Neu_Grafik

Hier kann man Ausreißer ganz gut erkennen.

Nun kann man sich die Summen des Imports anzeigen lassen und kontrollieren:

PQ_Neu_GrafikKontrolle

Details werden im nächsten Blatt angezeigt („Importkontrolle“)

PQ_Neu_Importkontrolle

Und zu guter Letzt dann das Blatt, welches lediglich die Spalten der Importdateien enthält:

PQ_Neu_Ergebnis

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:

PQ_Neu_9Nachklicken

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! 😉


Microsoft BI - Power Query

 

Weitere Tutorials?

Natürlich haben wir noch mehr wertvolle Tipps rund um Microsoft BI für Sie!