Power Query : Modifiez la source de données de vos requêtes

Publié le 17 décembre 2015
par Sophie Marchand M.Sc.
Power Query : Infos de connexion

Lorsque vous effectuez une requête sur une source de données dans Power Query (ou dans Power BI Desktop), il est possible que la source change d’endroit dans le futur. Par exemple, si la source est un fichier Excel, celui-ci peut facilement être déplacé de dossier, pour des raisons de réorganisation, ou, si la source est une base de données, il est possible que vous pointiez d’abord vers un serveur en “dev” et que vous souhaitiez ensuite faire pointer vos requêtes vers un serveur en “prod”. Dans tous les cas, il existe plusieurs raisons et plusieurs façons d’ajouter de la flexibilité à vos requêtes dans Power Query, afin de les faire facilement pointer vers une nouvelle source, au besoin. Cet article vise à présenter 3 approches pour faire pointer des requêtes vers une autre source dans Power Query.

 

Approche no.1

La première approche que j’aimerais vous présenter est expliquée en détails dans un article de Chris Webb,  Avoiding Duplication Of Database Connection Information In Power BI. En voici un résumé.

D’abord, dans l’exemple ci-bas, on remarque que lorsqu’on se connecte à un serveur SQL, on peut choisir plusieurs tables (en cochant les cases qui apparaissent à gauche de l’écran, ci-bas).

Power Query - Infos de connexion

 

En cochant ces 5 cases, on se trouve à créer 5 requêtes distinctes, qu’on peut apercevoir dans la section ci-dessous, à gauche.

Power Query - Infos de connexion

 

Quand on ouvre la requête DimDate (via le Advanced Editor), on s’aperçoit qu’il y a d’abord une connexion qui se crée sur le serveur SQL Sophie et, plus précisément, sur la base de données AdventureWorksDW2014 et, finalement, sur la table DimDate.

Power Query - Infos de connexion

 

Le problème, c’est qu’en ouvrant les autres requêtes, on s’aperçoit que la connexion au serveur, et à la base de données plus particulièrement, n’est pas partagée. Elle est plutôt reproduite. Donc, dans notre petit exemple, nous avons 5 connexions à 5 tables différentes, ce qui siginifie que nous dupliquons 5 fois la connexion à la base de données.

Power Query - Infos de connexion

 

Pour contourner ce problème, Chris Webb propose d’abord de créer une requête vierge (from blank) qui ne comprend que le nom du serveur, et de nommer cette requête SQLServerInstanceName.

Power Query - Infos de connexion

 

Il recommande ensuite de faire la même chose avec la base de données.

Power Query - Infos de connexion

 

Finalement, il recommande de modifier chacune des 5 requêtes afin qu’elles s’alimentent désormais à partir de la connexion SQLServerInstanceName et SQLServerDatabaseName, tel qu’illustré ci-dessous.

Power Query - Infos de connexion

 

Si la source devait changer, il faudrait simplement changer les paramètres des requêtes SQLServerInstanceName et SQLServerDatabaseName.

 

Approche no.2

Une deuxième approche, inspirée de la première, serait de créer une requête qui effectue réellement la connexion à la source de données, et de nommer cette requête SQLServerDatabaseConnection.

Power Query : Infos de connexion

 

Ensuite, pour chacune des 5 autres requêtes, il faudrait faire une référence à cette première requête (faire un clic droit de souris sur la requête SQLServerDatabaseConnection et cliquer Reference ou, tout simplement, partir d’une requête vide et inscrire =SQLServerDatabaseConnexion dans la barre de formule).

Power Query : Infos de connexion

 

Ce faisant, si nous souhaitons procéder avec la balance de la requête pour extirper la dimension Customer, nous n’avons qu’à effectuer un filtre pour sélectionner DimCustomer dans la liste des noms de tables, tel qu’illustré ci-dessous.

Power Query : Infos de connexion

 

Ensuite, nous pouvons utiliser le menu d’expansion pour choisir seulement les colonnes qui nous intéressent pour notre modèle, le cas échéant.

Power Query : Infos de connexion

Power Query : Infos de connexion

 

Finalement, notre requête DimCustomerV2 est plus légère que la première (puisque nous n’avons retenu que les colonnes nécessaires à notre analyse). De plus, elle se réfère à la source SQLServerDatabaseConnection, ce qui nous permet de ne pas dédoubler les informations de connexion dans les autres requêtes et ce qui permet également de changer la source aisément, au besoin.

Power Query : Infos de connexion

 

Approche no.3

Une troisième approche pourrait être utilisée dans Power Query pour Excel (mais pas dans Power Bi Desktop). Il s’agirait d’insérer les paramètres de connexion dans Excel et de les utiliser pour passer des paramètres dans Power Query.

Les étapes sont les suivantes:

  • Créer une table de paramètres dans Excel
  • Modifier les requêtes afin qu’elles fassent référence au tableau de paramètres

 

Il s’agit d’abord de créer une table avec les informations de connexion, la mettre sous forme de tableau et lui donner un nom, Parameters, dans l’exemple ci-bas.

Power Query - Table de paramètres

 

Ensuite, dans chacune de vos 5 requêtes, vous devez ajouter les 3 lignes suivantes de code:

  • Parameters = Indique que les paramètres se trouvent dans le tableau Parameters de votre fichier Excel.
  • DBServer = Indique que l’information pour DBServer se trouve dans le tableau dans la colonne de valeur correspondant au libellé DB server
  • DB = Indique que l’information pour DB se trouve dans le tableau dans la colonne de valeur correspondant au libellé DB

Power Query - Table de paramètres

 

Finalement, il s’agit de modifier les lignes existantes de code pour que Sophie réfère à DBServer et AdventureWorksDW2014 à DB, tel qu’illustré ci-dessous.

Power Query - Table de paramètres

 

De cette façon, nous évitons de dupliquer les informations de connexion et celles-ci sont plus faciles à modifier, au besoin. Vous aurez également compris que cette technique de tableau de paramètres peut être utilisée à différentes sauces. Nous en présentons justement une autre version  dans l’article suivant: Passer des paramètres dans Power Query via Excel.

 

CFO-Masque_Formations-en-ligne_FBLa 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é.

5 réflexions sur “Power Query : Modifiez la source de données de vos requêtes”

  1. super astuce. je l’ai adapté pour modifier facilement le repertoire cible :
    Parametres=Excel.CurrentWorkbook(){[Name=”Parametres”]}[Content],
    DossierCible=Table.SelectRows(Parametres,each [Nom du Parametre]=”DossierCible”){0}[Valeur],
    Source = Folder.Files(DossierCible),

  2. Bonjour Mikael.
    J’ai le même souci. Et si j’ai bien compris le principe j’ai du mal à l’appliquer.
    Ma source est formulée ainsi:
    Source = Csv.Document(File.Contents(“C:\Users\14a8k\Documents\finances\Regressions\sources\ADM.csv”)
    Pourriez vous m’aidez à construire ma table de paramètres svp?
    Ce serait formidable. Merci d’avance.
    AD

  3. Bonjour,
    Quelle est la façon de faire si au lieu d’une vue, nous voulons Exécuter une procédure stockée sur notre serveur SQL?
    Pour PBI 🙂
    Merci

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut