Charger des données dans PowerPivot en fonction de valeurs de cellules (Avec PowerQuery)

Qu’il s’agisse de pointer vers une source de données différente (entre environnements de Tests ou de Production) ou pour que l’utilisateur définisse une période, nous avons souvent besoin d’ajouter des paramètres.

Ce que je vous propose ici est de créer un tableau de paramètres contenant le chemin vers la source de données et l’année d’extraction des données. L’utilisateur pourra ainsi modifier ces paramètres avant même l’extraction de données.

POWER QUERY

La toute première étape est de vous assurer de bien avoir Power Query.

• Avec Excel 2010 et 2013: Télécharger ici
• Avec Excel 2016: Power Query est déjà intégré selon la version (Ruban: Données > Récupérer et transformer)

Pour la suite de la démonstration, les copies d’écran seront faites à partir d’Excel 2010.

 

Etape 1: Tableau des paramètres

Pour utiliser notre tableau de paramètres, nous allons créer une fonction dans PowerQuery qui permet de récupérer un paramètre de notre feuille Excel.

Voici ci-dessous les paramètres:

La fonction nous permettra de définir la valeur à retourner en fonction du paramètre choisi. Par exemple, la fonction retournera 2009 si le paramètre est « Année ».

 

Etape 2: Création d’une fonction

Dans le ruban, Allez dans Power Query, puis dans « Obtenir des données externes« , choisissez « A partir d’autres sources » et sélectionnez enfin « Requête vide« , comme ci-dessous.

La fenêtre Power Query va alors s’ouvrir. Commencez par modifier le nom de la requête en écrivant: fnGetParameter:

Pour modifier ensuite notre requête, cliquez sur « Editeur avancé« :

Effacez ensuite le contenu qui est proposé et copiez le code suivant:

---

(ParameterName as text) =>

let

ParamSource = Excel.CurrentWorkbook(){[Name= »Parameters »]}[Content],

ParamRow = Table.SelectRows(ParamSource, each ([Parametre] = ParameterName)),

Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0}, »Valeur »)

in

Value

Vous devrez avoir ceci:

Vous pouvez cliquer sur Terminé et ensuite sur « Ferme et charger« :

Vous êtes à présent revenu sur votre fichier Excel, comme dans l’image suivante. Si vous ne voyez pas la partie de droite, allez dans le Ruban, sur Power Query et cliquez sur « Afficher le volet« :

Etape 3: Utilisation de la fonction

Vous allez à présent importer vos données. Dans l’exemple, je récupère une table d’une base de données Access. Pour réaliser l’exercice, vous pouvez importer un fichier Excel, contenant un tableau avec au moins un e colonne ayant des dates.

Pour importer les données:

Allez ensuite dans l’Éditeur avancé pour visualiser l’ensemble du code (en langage M) ayant permis l’import des données:

Vous devriez avoir l’écran suivant. Notez que vous pouvez facilement identifier le chemin dans lequel se trouve le fichier que vous avez importé.

Comme notre objectif est d’utiliser le chemin spécifié dans la table des paramètres, nous allons donc remplacer cette partie par notre fonction: fnGetParameter(« Chemin »)

Vous pouvez cliquer sur « Terminé« . Vous remarquerez que rien ne change. Mais le fichier est maintenant charger en fonction du chemin défini dans le tableau des paramètres.

A présent, nous allons voir comment choisir uniquement les données qui correspondent à l’année spécifiée dans les paramètres. Pour cela, sélectionnez tout d’abord la colonne contenant les dates:

Une fois sélectionnée, allez dans:

RUBAN: Ajouter une colonne > Date et heure de début > Date > Année > Année

Cela permet de créer une nouvelle colonne « Year » qui sera l’année de la date correspondante.

Vous pouvez voir dans l’écran suivant le résultat. Notre but étant de filtrer sur l’année spécifiée dans les paramètres, nous allons tout d’abord filtrer la colonne sur une année (peu importe laquelle mais nous choisirons ici 2009).

Ensuite, comme tout à l’heure, nous allons modifier le code (en langage M) en cliquant sur « Editeur Avancé« 

Vous pouvez voir ci-dessous, dans l’encadré rouge, l’année 2009 que nous avons utilisée pour notre filtre. Nous allons donc la modifier en utilisant la fonction précédemment créée.

Vous devez donc remplacer 2009 par : fnGetParameter(« Année »)

Cette fonction va nous permettre de récupérer le bon paramètre en cherchant Année dans la colonne Parameter et en retournant la valeur de la cellule correspondante dans la colonne Valeur.

Finissez en cliquant sur « Fermer et charger dans« , puis choisissez « Créer une connexion uniquement« .

Regardons le résultat. Vous pouvez remarquer que nous avons 2008 dans la table des paramètres et donc dans la requête de Power Query, les données sont filtrées sur l’année 2008:

POWER PIVOT

Les données ont bien été chargées et transformées dans Power Query (Get Data) et chaque fois que vous cliquerez sur Actualiser (RUBAN: Données > Connexions > Actualiser), les données seront automatiquement rechargées, et transformées en fonction de vos paramètres.

Maintenant nous avons besoin de mettre les données dans un Cube, à savoir dans PowerPivot. Pour installer PowerPivot, voir l’article ICI.

 

Etape 4: Créer la connexion

Il faut savoir que cette partie apporte son lot de magie et va permettre de comprendre comment utiliser pleinement la notion de Power BI. En effet, nous allons charger directement le résultats des requêtes Power Query dans Powert Pivot. Ainsi, nous pourrons nous appuyer sur toute la puissance de Power Pivot pour réaliser nos analyses.

Avant de lancer Power Pivot, nous allons créer la connexion. Allez dans le Ruban comme ci-dessous:

RUBAN: Données > Connexions > Connexions

Sélectionnez la requête créée, puis cliquez sur « Propriétés« :

Sur l’onglet « Définition« , sélectionnez toute la chaîne de connexion et faites CTRL + C (copier).

 A présent, vous pouvez ouvrir Power Pivot, comme ci-dessous:

Ensuite, dans le ruban de Power Pivot:

RUBAN: Accueil > Obtenir des données externes > Autres sources

 Choisissez « Autres (OLEDB…) »

A présent, donnez un nom au résultat de votre requête (vous pouvez l’appeler comme la requête si vous le désirez). Puis dans Chaîne de connexion, faites CTRL + V pour coller ce que vous aviez précédemment.

Il ne vous reste plus qu’à cliquer sur SUIVANT puis sélectionner votre table et cliquer sur Terminer.

Vos données sont à présents bien chargées dans Power Pivot et elles répondent aux critères que vous avez pu définir dans votre tableau de paramétrage.

Vous pouvez constater le résultat ci-dessous. Nous voyons bien que seules les données avec une année égale à 2008 ont été chargées:

 

CONCLUSION

Nous venons de voir une méthode pour utiliser des paramètres depuis Excel. De nombreuses autres applications sont possibles.