Wir haben in diesem Blog schon oft über den Folderimport geschrieben (hier, hier und hier). Heute zeigen wir einen (weiteren) Lösungsansatz für den in der Praxis häufig auftretenden Fall von Excel Files, der Sheet-Inhalte zwar inhaltlich gleich aufgebaut sind aber deren Sheet-Namen individuelll benannt sind.

Die Lösung ist auch gut dazu geeignet, den automatisierten Folder Import aus dem Wizard vollständig zu verstehen und damit auf andere, abweichende Fälle durch Anpassungen anwenden zu können.

1. Ausgangsdaten: Day-Ahead Strompreise

Die OeMAG – diese ist die Abwicklungsstelle für Ökostrom in Österreich und kauft Strom von PV Anlagenbetreibern zu gesetzlich festgelegten Einspeisetarifen – veröffentlicht die für die Ermittlung des monatlichen Einspeisetarifs verwendeten stündlichen Day-Ahead Preise mit der verwendeten KWh-Gewichtung. Diese Daten können hier im XLSX-Format heruntergeladen werden:

https://www.oem-ag.at/de/marktpreis

Dabei werden die Daten für 2024 als Jahresfile und die Daten für das laufende Jahr 2025 als Monatsfiles bereitgestellt:

Die Herausforderung für den Folderimport mittels Power Query liegt darin, daß die Sheets monatsspezifisch benannt sind. In den Monatsfiles gibt es jeweils 1 Sheet …

… und im Jahresfile sind es 12 Sheets:

Die Inhalte in den Sheets selbst sind in allen Files gleich strukturiert, das ist die Voraussetzung für einen automatisierten Import.

2. Import der Excel Files mittels Power Query

Für den Import dieser Excel Files mit variabler Sheet-Struktur können wir nicht den Wizard in Power Query für den Folder Import verwenden sondern erstellen diesen in Einzelschritten selbst.

2.1 Erstellen der Query für den Excel Folder Import

Wir starten ganz normal mit dem Power Query Konnektor für den Folder Import …

… und wählen das Verzeichnis mit den zu importierenden Dateien aus:

Im Dialog mit der Fileliste jetzt keinesfalls den Combine Button sondern den Transform Data Button drücken:

Wir bekommen eine Query mit erst mal nur 1 Step und der Fileliste im Vorschaufenster:

Wir entfernen erst mal alle Spalte bis auf Content, Name und Attributes und expandieren anschließend die Attributes Spalte auf das Feld Hidden:

Jetzt setzen wir einen Filter auf Hidden<> true (oder auch Hidden = false) und erreichen damit, daß eventuell vorhandene temporäre Files (aufgrund geöffneter Excel Files) dem Import keinen Fehler bescheren können. Dieser Schritt wird beim „normalen“ Folder Import Wizard automatisch angelegt, bei der manuellen Schritt-für-Schritt Umsetzung müssen wir diese Sicherheitsroutine selbst einsetzen:

Jetzt kommt der erste Clou – nämlich die Anlage einer Custom Column zur Extraktion der File Inhalte aus der Spalte Content („Binary“) in die neue Spalte Sheetliste:

Sheetliste = Excel.Workbook([Content])

Der Befehl erzeugt eine strukturierte Spalte, die wir im nächsten Schritt expandieren können, wobei für uns nur folgende Felder relevant sind:

• Spalte Name – das sind die Sheetnamen aus der jeweiligen Excel Datei
• Spalte Data – das ist der Inhalt des jeweiligen Sheets

Die Spalten Content und Attributes.Hidden können jetzt bereits entfernt werden, um eine möglichst übersichtliche Arbeitsumgegung zu erhalten. Die Spalte Name ist eine normale Textspalte und die Spalte Data ist wiederum eine strukturierte Spalte. Für die Extraktion der Inhalte dieser strukturierten Spalte benötigen wir eine M-Funktion mit den entsprechenden Transformationsbefehlen zur Aufbereitung der Rohdaten aus dem jeweiligen Sheet.

2.2 Erstellen der M-Funktion für die Muster-Transformation

Für die Erarbeitung der Muster-Transformation der Daten in den Sheets erstellen wir erst mal eine ganz normale, neue Query. Wir verwenden dazu den Konnektor für Excel Files und wählen ein beliebiges (Monats-)File aus unserem Verzeichnis aus:

Die Query nennen wir erst mal Mustertransformation. Die Daten in der Preview zeigen, daß es noch Transformationsbedarf gibt:

Hier die einzelnen Transformationsschritte kurz erklärt:

• Removed Top Rows = obere 4 Zeilen entfernen
• Removed Other Columns = unnötige Spalten entfernen
• Promoted Headers = erste Zeile als Spaltentitel verwenden
• Filtered Rows = behalten nur der Datensätze mit Stunde <> null
• Filled Down = Auskopieren des Datums auf die darunterliegenden leeren Zellen

Um aus der Query eine M-Funktion zu machen, öffnen wir erst mal den Advanced Editor …

… und machen folgende Anpassungen am M-Code:

• Neue Zeile „(InputTable as table) as table =>„
  Das „=>“ macht aus der Query eine M-Funktion.
  „InputTable“ ist die Variable, die wir beim späteren Aufruf der M-Funktion übergeben möchten – in unserem Fall sind das die Inhalte des jeweiligen Sheets.
• Auskommentieren (oder löschen) der ersten beiden Steps Source und Navigation
• Anpassen der Input Tabelle im ersten Transformationsstep auf die übergebene Variable der M-Funktion

Nach dem Bestätigen wird das Objekt im Power Query Editor nicht mehr als Query sondern als Function gezeigt, zur bessseren Orientierung benennen wir diese auf „fx_Mustertransformation“ um:

Alternativ wäre es auch möglich, die M-Function zusätzlich zur Query zu erstellen und bei Verwendung des Befehls Create Function (im Kontextmenü der Query) einen Sync zwischen der Query (die bei Bedarf leicht nachbearbeitet werden kann) und der Function herzustellen. Mir persönlich gefällt diese schlanke Lösung aber besser, die Wartung kann recht einfach über das Ein-/Auskommentieren der jeweilgen Zeilen im Advanced Editor erfolgen.

2.3 Einsetzen der M-Funktion in die Query für den Folder Import

Jetzt kommt der zweite Clou, nämlich der Aufruf der Function in der Query für den Folder Import. Dazu einfach den Button Invoke Custom Function drücken und den Dialog entsprechend konfigurieren:

Wir erhalten die neue Spalte Sheet_Data_Transformed zusätzlich zur bestehenden Spalte Sheetliste.Data. Zum besseren Verständnis lassen wir uns die Vorschau des Inhalts einer Datenzelle in der strukturierten Spalte Sheetliste.Data anzeigen – es ist der untransformierte Inhalt des Sheets zu sehen:

Die Vorschau einer Datenzelle in der ebenfalls strukturierten Spalte Sheet_Data_Transformed zeigt uns hingegen den mit den Befehlen der Mustertransformation fertig aufbereiteten Inhalt:

Jetzt kann wieder aufgeräumt werden – nicht mehr benötigte Spalten entfernen und verbleibende Spalten sauber benennen:

Im abschließenden Schritt werden jetzt alle Felder der strukturierten Spalte Sheet_Data_Transformed expandiert:

Voilá, jetzt nur noch die Datentypen der einzelnen Spalten festlegen und fertig ist der Import der Excel Files mit variabler Sheet-Struktur! Die ersten beiden Spalten stammen aus der ursprünglichen File Liste der Query für den Folder Import und die weiteren 4 Spalten kommen aus dem aufbereiteten Inhalten der jeweiligen Sheets:

3. Visualisierung

Die Query wird jetzt ausgeführt und damit die physische Tabelle im Power BI Datenmodell erzeugt und mit den aufbereiteten Daten befüllt. Hier eine erste, sehr einfache Visualisierung der verdurchschnitteten Stundenpreise auf Tagesebene:

Fazit

Mit ein paar einfachen Kniffen kann der aus dem Folder Import Wizard bekannte Aufbau eines Verzeichnisimports leicht in seine Einzelteile zerlegt und damit erfolgreich mit der Variabilität in der Sheet Struktur der Excel Files umgegangen werden. Als Nebeneffekt wird die Struktur auf nur zwei PQ Objekte (Query + Function) reduziert, was die Übersichtlichkeit im Power Query Editor erhöht.

Quellen

https://www.oem-ag.at/de/marktpreis