Power Query depuis Excel : migration vers BI et R7

Extraction de Power Query depuis Excel : Automatisation de la migration vers BI et R7 Office

Excel avec Power Query et VBA est souvent utilisé pour prototyper des applications de données. À mesure que les volumes de données augmentent, le fichier devient une « boîte noire » : la logique de transformation est enfouie dans d'innombrables objets, les sources de données sont codées en dur pour l'environnement, et les modifications sont risquées. Migrer vers des systèmes BI ou R7 Office nécessite une dissection complète du code M, des dépendances et des sources.

Trois questions clés pour tout fichier Excel avec Power Query :

• D'où viennent les données ?
• Dans quel ordre les transformations sont-elles appliquées ?
• Qu'est-ce qui risque de casser si on supprime une étape ?

Répondre à ces questions exige une analyse manuelle de dizaines de requêtes, ce qui augmente la charge cognitive et le temps d'audit.

Pourquoi l'interface Power Query est inadaptée à l'analyse

Power Query est un pipeline en langage M, stocké dans les fichiers .xlsx sous forme XML. L'interface est excellente pour construire des étapes, mais inutile pour l'audit : pas de graphes de dépendances, lignage difficile à suivre. Modifier implique de sauter d'une requête à l'autre, au risque de rompre des liens.

Solution : Passer de la dissection manuelle à l'extraction automatisée. Le fichier .xlsx est une archive ZIP contenant des structures XML du code M. L'analyse reconstruit un arbre syntaxique abstrait (AST), le graphe de dépendances, et extrait les paramètres de sources (fichiers, bases de données, connecteurs).

Mise en œuvre technique de l'extraction

Le processus s'exécute sans lancer Excel :

• Dézipper l'archive : Extraire le .xlsx et récupérer les requêtes XML Power Query.
• Analyser le code M : Le décomposer en structures, pas en texte — capturant étapes, fonctions et références.
• Construire le graphe : Mapper les dépendances entre requêtes et sources.
• Exporter les artefacts : Données en CSV/DuckDB, métadonnées en JSON (logique, lignage, connexions).

Résultat : Un modèle de données indépendant avec sources transparentes, dépendances explicites et changements prévisibles. Prêt pour l'import BI sans réécriture.

| Métrique | Analyse manuelle | Analyse automatisée |

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

| Temps pour fichier complexe | 6+ heures | 1 heure |

| Risque d'omettre des dépendances | Élevé | Minimal |

| Prêt pour migration | Réécriture complète | Carte de lignage + export |

Migration vers les systèmes BI

Au lieu de recréer manuellement la logique, intégrer le modèle extrait :

• Charger les données directement depuis les exports.
• Transférer les dépendances comme un pipeline prêt à l'emploi.
• Adapter les sources sans perdre de détails.

Cela transforme la migration d'une corvée de recherche en travail d'ingénierie : BI reçoit des données structurées avec documentation complète du lignage.

Adaptation pour R7 Office et low-code/no-code

Power Query n'est pas supporté nativement dans R7 Office. La structure de code M extraite sert de base pour traduire en JavaScript ou scripts natifs du système cible. Une couche intermédiaire découple la logique d'Excel, la rendant portable.

Avantages :

• Documentation automatique : JSON avec étapes et paramètres.
• Audit des sources : Vérifier les connexions sans exécuter le fichier.
• Refactorisation : Modifier le graphe sans tout casser.

Limites de l'analyse statique

L'automatisation gère 90 % des cas mais signale les cas complexes :

• Code dynamique : Expression.Evaluate, concaténation de chemins, compilation conditionnelle.
• Appels externes : Intégration VBA, connecteurs personnalisés.
• Différences de versions : Excel 2016/365 vs Power BI Desktop.

Ces éléments sont marqués pour révision manuelle.

Points clés à retenir

• L'extraction de Power Query transforme la logique cachée en modèle structuré avec lignage.
• Réduit le temps d'analyse des fichiers par 6, minimise les risques.
• Fluidifie la migration BI (import direct) et R7 Office (traduction JS).
• Permet audit, refactorisation et documentation sans Excel.
• Limites : Code dynamique et appels externes nécessitent des vérifications manuelles.

— Editorial Team