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)

Power Query 2016

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:

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.

23-11-2015 15-31-42

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

23-11-2015 15-33-31

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

23-11-2015 15-33-36

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:

23-11-2015 15-36-26

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

23-11-2015 15-37-00

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« :

23-11-2015 15-37-35

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:

23-11-2015 15-38-24

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

23-11-2015 15-50-39

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

23-11-2015 15-40-41

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 »)

23-11-2015 15-42-36

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:

23-11-2015 15-50-39

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.

23-11-2015 15-53-23

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).

23-11-2015 16-06-04

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

23-11-2015 16-10-26

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.

23-11-2015 16-11-01

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.

23-11-2015 16-11-31

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:

23-11-2015 16-12-27

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

23-11-2015 16-13-50

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

23-11-2015 16-14-28

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

23-11-2015 16-19-20

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

23-11-2015 16-20-13

Ensuite, dans le ruban de Power Pivot:

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

23-11-2015 16-20-51

 Choisissez « Autres (OLEDB…) »

23-11-2015 16-21-30

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.

23-11-2015 16-22-06

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

23-11-2015 16-24-50

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.23-11-2015 16-27-25

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:

23-11-2015 16-27-45

 

CONCLUSION

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

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

  • Pingback: Votre nouveau blog Cooking-Excel est enfin arrivé ! | Cooking-Excel

  • 29 juin 2016 at 8 h 48 min
    Permalink

    Bonjour,

    Cet article est très intéressant, et m’a beaucoup aidé.
    Cependant, sauriez-vous me dire comment procéder pour mettre à jour une connexion existante ? (sachant que, pour la création, j’ai procéder exactement comme indiqué dans cet article)
    Chaque fois, j’ai un message me disant qu’il faut faire les modifications directement dans Power Query et rien ne veut s’actualiser dans power pivot. Je suis bloquée…

    Merci d’avance pour votre réponse.

    Reply

Laisser un commentaire

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