Connecter Power Query à une table d'un modèle sémantique publié dans Power BI Service

Vous avez déjà passé des heures à nettoyer, consolider et fiabiliser une table de ventes dans Power BI. Elle est publiée dans Power BI Service, elle est maintenue à jour régulièrement… pourquoi ne pas la réutiliser dans vos fichiers Excel plutôt que de la reconstruire de toute pièce?

Bonne nouvelle : Power Query dans Excel peut se connecter directement à une table déjà publiée dans un modèle sémantique Power BI. Dans cet article, je vous montre comment, étape par étape, avec un exemple concret.

Dans notre précédent article Guide pratique : Exploiter les modèles sémantiques et flux Power BI dans Excel, on avait déjà abordé la connexion à un modèle sémantique Power BI dans Excel. On y présentait notamment la connexion au modèle complet (via un tableau croisé dynamique) et la connexion « directe » à une table. Toutefois, la connexion à une table en passant par Power Query pour la retravailler avant de la charger n’avait pas été couverte et c’est exactement ce qu’on explore ici.

Le contexte : une table de ventes déjà consolidée

Pour cet exemple, je travaille avec un modèle sémantique publié qui consolide les ventes de plusieurs mois dans une même table de faits, que j’ai nommée Actuels. Le modèle sémantique se nomme Modèle PBI2.

Tout ce travail de structuration a déjà été fait dans Power BI. Mon objectif ici n’est pas de le refaire, mais de récupérer la table Actuels directement dans Power Query pour l’exploiter dans Excel.

Pourquoi passer par Power Query plutôt que de tout charger dans Excel?

On pourrait se demander : pourquoi ne pas simplement charger la table complète dans une feuille Excel?

Parce que ce n’est pas toujours souhaitable :

certaines tables sont trop volumineuses pour être chargées telles quelles dans une feuille,
vous n’avez parfois besoin que d’une portion des données (un magasin, un département, un mois),
ou vous voulez utiliser la table comme étape intermédiaire dans une requête, sans jamais la charger dans le classeur.

C’est précisément la force de Power Query : on peut se brancher sur les données, les transformer et les filtrer dans la requête, et ne charger que le résultat final — voire ne rien charger du tout et garder la requête comme connexion seulement.

Les valeurs de connexion dont vous aurez besoin

Avant de commencer, repérez trois éléments dans votre environnement Power BI :

le nom de votre espace de travail (dans notre exemple : Materiel de Formation),
le nom du modèle sémantique (ici : Modèle PBI2),
le nom de la table à récupérer (ici : Actuels).

L’adresse de la source de données se construit ainsi : powerbi://api.powerbi.com/v1.0/myorg/[nom de votre espace de travail]. Dans notre cas :

powerbi://api.powerbi.com/v1.0/myorg/Materiel de Formation

Étape 1 – Ouvrir le connecteur vers Power BI

On pourrait penser qu’il suffit de passer par Données → Obtenir des données → À partir de Power Platform → À partir des modèles sémantiques Power BI.

Mais ce connecteur ne vous amène pas dans Power Query : il mène soit vers un tableau croisé dynamique, soit vers une connexion « directe » à une table (le bouton Insérer un tableau). C’est utile, mais ce n’est pas une requête Power Query, et vous ne pourrez donc pas y appliquer d’étapes de transformation.

Pour y arriver, il faut passer par le connecteur AnalysisServices, qui se branche sur l’endpoint XMLA du modèle sémantique. Je vous montre deux façons de faire — choisissez celle qui vous convient le mieux.

Méthode 1 – Créer une requête vide et saisir le code M

C’est la méthode la plus directe si vous êtes à l’aise avec l’éditeur avancé.

Dans Excel, allez dans Données → Obtenir des données → À partir d’autres sources → Requête vide.
L’éditeur Power Query ouvre une requête vide. Cliquez sur Éditeur avancé (onglet Accueil).

Remplacez le contenu par le code suivant :

let
    Source = AnalysisServices.Database(
        "powerbi://api.powerbi.com/v1.0/myorg/[Nom de votre espace de travail]",
        "[Nom de votre modèle sémantique]",
        [
            Query = "EVALUATE '[Nom de votre table]'",
            Implementation = "2.0"
        ]
    )
in
    Source

Cliquez sur Terminé.

Cliquez sur « Modifier une autorisation ». Puis cliquez sur Exécuter pour lancer la requête de collecte de données sur la table « Actuels ».

Note

Le EVALUATE ‘Actuels’ est une requête DAX qui retourne toute la table. Vous pourriez la raffiner directement en DAX, mais le plus simple reste de ramener la table complète, puis de la filtrer avec les outils habituels de Power Query.

Connectez-vous grâce à votre compte Microsoft.

La table Actuels apparaît finalement dans l’éditeur, prête à être transformée.

Méthode 2 – Passer par l’assistant Analysis Services

Si vous préférez éviter le code, l’assistant graphique fait le même travail.

Allez dans Données → Obtenir des données → À partir d’une base de données → À partir d’une base de données SQL Server Analysis Services.
Dans la fenêtre qui s’ouvre, renseignez :
- Serveur : powerbi://api.powerbi.com/v1.0/myorg/Materiel de Formation
- Base de données (optionnel) : Modèle PBI2
- Requête MDX ou DAX (optionnel) : EVALUATE ‘Actuels’
Si vous laissez la requête vide, l’assistant vous proposera de naviguer dans le modèle et de sélectionner la table Actuels dans l’arborescence.
Cliquez sur Transformer les données pour ouvrir la table dans l’éditeur Power Query (ou sur OK si vous étiez déjà dans Power Query).

Note

💡 Les deux méthodes aboutissent exactement au même résultat : une requête Power Query dont la première étape est un appel à AnalysisServices.Database. La méthode 1 est plus rapide à reproduire; la méthode 2 est plus rassurante si vous débutez.

Étape 2 – Travailler la requête dans l’éditeur Power Query

La table Actuels s’ouvre maintenant dans l’éditeur Power Query, exactement comme n’importe quelle autre source de données.

À partir d’ici, vous êtes en terrain connu. Vous pouvez, par exemple :

filtrer pour ne conserver qu’un mois ou qu’un magasin précis,
supprimer les colonnes dont vous n’avez pas besoin,
regrouper les données pour obtenir des totaux,
ou combiner cette table avec une autre source dans une requête plus large.

C’est ici que se réalise l’objectif de départ : réutiliser une table déjà fiabilisée sans jamais la reconstruire.

Étape 3 – Choisir quoi charger (ou ne rien charger du tout)

Une fois vos transformations terminées, cliquez sur Fermer et charger dans…

Excel vous propose alors de :

charger le résultat dans un tableau dans une feuille,
créer uniquement une connexion (sans charger de données dans le classeur),
ou ajouter le résultat au modèle de données Excel.

Si votre table est volumineuse et que vous vous en servez seulement comme étape intermédiaire, l’option « Créer uniquement la connexion » est votre meilleure amie : vous gardez accès à la donnée dans vos requêtes, sans alourdir votre fichier.

En résumé

Se connecter à une table d’un modèle sémantique publié depuis Power Query vous permet de :

réutiliser un travail de consolidation déjà réalisé,
éviter la duplication des transformations,
travailler avec une source de vérité unique,
et ne charger que ce dont vous avez réellement besoin dans Excel.

C’est une technique simple, mais qui change complètement la façon de faire circuler vos données entre Power BI et Excel.

Bonus : Exploiter les relations du modèle sémantique depuis Power Query

Si vous avez utilisé la méthode 2 sans saisir de requête DAX, Excel ouvre plutôt la fenêtre Navigateur. C’est une porte d’entrée très pratique, car elle vous laisse explorer visuellement tout ce que contient l’espace de travail avant de charger quoi que ce soit.

Dans le volet de gauche, vous retrouvez :

l’espace de travail auquel vous êtes connecté (ici Materiel de Formation),
tous les modèles sémantiques qu’il contient (2. Rapport utilisateurs finaux, Le-CFO-Masque_Cas-RLS-Complexe, Modèle PBI2…),
et, en dépliant un modèle, toutes ses tables : Actuels, Budget, DimBannières, DimCatégories, DimDepts, DimMagasins, DimMois.

À partir d’ici, vous pouvez :

cocher une ou plusieurs tables selon ce dont vous avez besoin (dans notre exemple, la table Actuels avec ses 9 colonnes),
prévisualiser les données dans le volet de droite pour confirmer que vous ciblez la bonne table avant de vous engager,
et récupérer plusieurs tables liées d’un seul coup (par exemple Actuels + DimMagasins + DimMois) si vous voulez reconstruire vos propres jointures dans Power Query.

Une fois vos tables sélectionnées, cliquez sur Transformer les données plutôt que sur OK : vous ouvrez ainsi votre sélection dans l’éditeur Power Query, prête à être filtrée et retravaillée.

Un avantage souvent négligé : la sécurité vous suit. Comme cette connexion pointe sur le modèle sémantique publié, la sécurité au niveau des lignes (RLS) définie dans Power BI continue de s’appliquer. Autrement dit, chaque utilisateur ne verra que les données que son rôle l’autorise à consulter — vous réutilisez la source de vérité avec ses règles de gouvernance, et non une copie brute qui les contournerait.

Vous préférez la version vidéo ? La voici!

Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

Envie d’aller plus loin avec Power Query et Power BI?

Cette technique n’est qu’un exemple de ce qu’on peut accomplir en maîtrisant bien Power Query et l’écosystème Power BI. Découvrez nos formations en informatique décisionnelle pour exploiter pleinement vos données.

La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.