Après un long moment sans article, je reviens avec un article issue d’une demande d’un de mes clients.
Voici son besoin
Il extrait régulièrement des tables (manuellement) contenant des dizaines de milliers de lignes. Afin d’analyser dans Excel, il a besoin de lier ces tables. Toutefois, il a besoin d’une relation un peu particulière entre deux tables. La clé doit correspondre à la ligne la plus récente.
Prenons un exemple simple
Table1:
Dans cette table, nous avons un code correspondant à des clients.
Donc l commande cmd1 est pour le client C001
et le client C001 a fait 3 commandes.
Ce qui nous intéresse est de récupérer l’âge et le prénom du client dans la table 2:
Le problème est que nous avons plusieurs clients dans la table des clients. De ce fait il faut récupérer celui qui a la date de mise à jour la plus récente (besoin de départ). D’après ce tableau, nous devons donc récupérer la ligne correspondant à Sylvie.
Nous pourrions très bien le faire avec des fonctions EXCEL mais n’oublions pas que pour momn client, il réalisait des extractions manuelles de plusieurs dizaines de milliers de lignes et sur plusieurs tables (pas uniquement 2). De ce fait, PowerPivot semble le plus adapté en créant les imports de données qui seront nécessaires.
Pour notre exemple, nous allons uniquement créer des tables liés dans Powerpivot.
Le résultat sera le suivant:
Il faut à présent créer la relation, qui est impossible à ce niveau là du fait de la non-unicité de la clé. Notre but est donc de créer une clé UNIQUE et qui corresponde au besoin, c’est à dire la date la plus récente.
Voici comment nous allons procéder
- Création d’une colonne calculée qui récupère la date la plus récente de chaque Code. Pour C001, la date devra donc être 25/02/2014.
- Création d’une nouvelle colonne calculée qui sera la clé: Si la DateMax correspond à la date, cela signifie que la colonne Code doit correspondre à la nouvelle clé, sinon, il faudra trouver une clé unique qui ne sera pas utilisée mais nécessaire pour créer la relation dans PowerPivot. La limite est que nous ne devons pas avoir deux lignes avec la même date pour le même code (pour mon client, la date et l’heure est renseignée, de ce fait, nous n’aurons pas le problème).
- Enfin créer la relation entre les deux tables.
Calcul de la DateMax
Créer une nouvelle colonne calculée dans la table2 et entrer la formule suivante:
=MAXX ( FILTER( Table2 ; Table2[Code] = EARLIER(Table2[Code]) ) ; Table2[Date] )
Cette formule peut paraître compliquée mais en fait, elle permet de rechercher le maximum de Table2[Date] dans la colonne Table2[Code] pour le code recherché.
Dans notre exemple, le premier argument de MAXX est un filtre sur la table 2 pour toutes les lignes ayant le même Table2[Code] que la ligne en cours (contexte de ligne).
Petit rappels de fonctions utilisées:
MAXX
- Renvoie la valeur maximum dans une table pour une expression donnée
- MAXX(<table>,<expression>)
- Table: Table qui contient les lignes pour lesquelles l’expression sera évaluée.
- Expression à évaluer pour chaque ligne de la table.
FILTER
- Retourne une table qui représente un sous-ensemble d’une autre table ou expression.
- FILTER(<table>,<filter>)
- Table à filtrer. La table peut également être une expression qui donne une table.
- Expression booléenne qui doit être évaluée pour chaque ligne de la table.
EARLIER
- Retourne la valeur actuelle de la colonne spécifiée dans un test d’évaluation externe de la colonne indiquée.
- EARLIER(<column>, <number>)
- Colonne ou expression qui est résolue en une colonne.
- (Facultatif) Nombre positif pour le test d’évaluation externe.
- Le niveau d’évaluation extérieur suivant est représenté par 1 ; un décalage de deux niveaux vers l’extérieur est représenté par 2, et ainsi de suite. En cas d’omission, la valeur par défaut est 1.
Calcul de la nouvelle clé
Créer une nouvelle colonne calculée dans la table2 et entrer la formule suivante:
=IF( [DateMax] = [Date] ; [Code] ; [Code] & RAND() )
Si pour la ligne en cours, la date maximum correspond à la colonne date, c’est que nous sommes sur la ligne qui correspond à la date la plus récente. Dans ce cas, la clé doit être le code de la première colonne. DAns le cas contraire, nous devons créer une clé. Pour le faire, nous ajoutons une valeur aléatoire avec la fonction RAND. La probabilité d’avoir une valeur en double est impossible du fait que la fonction RAND retourne 15 chiffres après la virgule.
Calcul de la nouvelle Relation
Ensuite, il ne reste plus qu’à créer un Tableau Croisé Dynamique (comme ci-dessous) pour avoir un nouveau tableau reprenant les valeurs regroupées par la nouvelle relation.
Conclusion
Les relations dans PowerPivot sont souvent limitées et il faut utiliser des astuces pour y remédier.
Les principaux problèmes que nous pouvons rencontrer sont les suivants:
- Relation unique entre des tables: Chemin d’accès UNIQUE entre une table et une autre, donc une seule relation entre chaque paire de tables ;
- Une relation pour chaque colonne source: Une colonne source ne peut pas participer à plusieurs relations. La solution est de créer une nouvelle colonne, qui sera une copie de la première.
- Clés composites et colonnes de recherche: Les clés composite ne sont pas admise dans PowerPivot. Pour contourner le problème, il faut concaténer les colonnes qui forment la « clé composite ».
Bonjour, Merci pour l’article très interessant. J’ai effectué les indications pour avoir dans ma premiere table une colonne avec que des valeurs uniques (pas de doublons) et dans un table 2 également, d’autre valeurs uniques.
J’ai créé une relation entre ces 2 tables qui a fonctionné (Power pivot ne m’as pas mis d’erreur).
En revanche, lorsque je crée un TCD qui fait appel à ces 2 tables, ca ne fonctionne pas. Au lieu de me lier les données, il me met, pour une ligne de la table 1, toutes les lignes de la table 2 au lieu de me mettre uniquement celle qui correspond.
J’ai tenté de mettre un champ dans « valeurs » mais rien n’y fait.
Quelqu’un peut m’aider ?
Merci beaucoup