Power Query aus Excel: Migration zu BI und R7

Power Query aus Excel extrahieren: Automatisierte Migration zu BI und R7 Office

Excel mit Power Query und VBA wird oft für Prototypen von Datenanwendungen genutzt. Mit wachsenden Datenmengen wird die Datei zur 'Blackbox': Die Transformationslogik ist in unzähligen Objekten vergraben, Datenquellen sind fest auf die Umgebung verdrahtet, und Änderungen sind riskant. Die Migration zu BI-Systemen oder R7 Office erfordert eine vollständige Zerlegung des M-Codes, der Abhängigkeiten und Quellen.

Drei zentrale Fragen für jede Excel-Datei mit Power Query:

• Woher kommt die Daten?
• In welcher Reihenfolge werden Transformationen angewendet?
• Was bricht, wenn eine Stufe gelöscht wird?

Diese Fragen erfordern manuelle Analyse von Dutzenden Abfragen – mit hohem kognitiven Aufwand und Audit-Zeit.

Warum die Power Query-Oberfläche für Analysen ungeeignet ist

Power Query ist eine Pipeline in der M-Sprache, die in .xlsx als XML gespeichert wird. Die Benutzeroberfläche eignet sich super zum Erstellen von Schritten, ist aber für Audits nutzlos: Keine Abhängigkeitsgraphen, schwierige Nachverfolgung der Datenlinie. Bearbeiten bedeutet Springen zwischen Abfragen mit Risiko gebrochener Verknüpfungen.

Lösung: Vom manuellen Zerlegen zum automatisierten Extrahieren wechseln. .xlsx ist ein ZIP-Archiv mit XML-Strukturen des M-Codes. Parsing erzeugt einen abstrakten Syntaxbaum (AST), rekonstruiert den Abhängigkeitsgraphen und extrahiert Quellenparameter (Dateien, Datenbanken, Connectoren).

Technische Umsetzung der Extraktion

Der Prozess läuft ohne Start von Excel:

• Archiv entpacken: .xlsx auspacken und Power Query-XML-Abfragen extrahieren.
• M-Code parsen: In Strukturen zerlegen, nicht als Text – Schritte, Funktionen und Referenzen erfassen.
• Graph aufbauen: Abhängigkeiten zwischen Abfragen und Quellen abbilden.
• Artefakte exportieren: Daten nach CSV/DuckDB, Metadaten als JSON (Logik, Linie, Verbindungen).

Ergebnis: Ein unabhängiges Datenmodell mit transparenten Quellen, expliziten Abhängigkeiten und vorhersehbaren Änderungen. Bereit für BI-Import ohne Neuschreiben.

| Metrik | Manuelle Überprüfung | Automatisierte Analyse |

|--------|----------------------|------------------------|

| Zeit für komplexe Datei | 6+ Stunden | 1 Stunde |

| Risiko fehlender Abhängigkeiten | Hoch | Minimal |

| Migrationsbereitschaft | Von Grund auf neu schreiben | Linienkarte + Export |

Migration zu BI-Systemen

Statt Logik manuell nachzubauen, das extrahierte Modell einsetzen:

• Daten direkt aus Exports laden.
• Abhängigkeiten als fertige Pipeline übertragen.
• Quellen anpassen, ohne Details zu verlieren.

Das wandelt Migration von Recherche-Marathon in Ingenieursarbeit um: BI erhält strukturierte Daten mit vollständiger Liniendokumentation.

Anpassung für R7 Office und Low-Code/No-Code

Power Query wird in R7 Office nicht nativ unterstützt. Die extrahierte M-Code-Struktur dient als Basis für Übersetzung nach JavaScript oder nativen Skripten im Zielsystem. Eine Zwischenschicht entkoppelt die Logik von Excel und macht sie portabel.

Vorteile:

• Automatische Dokumentation: JSON mit Schritten und Parametern.
• Quellen-Audit: Verbindungen prüfen, ohne Datei auszuführen.
• Refactoring: Graph anpassen, ohne etwas kaputtzumachen.

Grenzen der statischen Analyse

Automatisierung meistert 90 % der Fälle, markiert knifflige:

• Dynamischer Code: Expression.Evaluate, Pfadverkettung, konditionelle Kompilierung.
• Externe Aufrufe: VBA-Integration, Custom-Connectoren.
• Versionsunterschiede: Excel 2016/365 vs. Power BI Desktop.

Diese werden für manuelle Prüfung markiert.

Wichtige Erkenntnisse

• Power Query-Extraktion macht versteckte Logik zu einem strukturierten Modell mit Linie.
• Reduziert Analysezeit um das 6-Fache, minimiert Risiken.
• Vereinfacht BI-Migration (direkter Import) und R7 Office (JS-Übersetzung).
• Ermöglicht Audit, Refactoring und Docs ohne Excel.
• Grenzen: Dynamischer Code und externe Aufrufe brauchen manuelle Checks.

— Editorial Team