Ce matin une bonne nouvelle de la part de l’équipe Data Explorer : la fonction Unpivot arrive !

Blog Data Explorer – Latest update

Dès mes premiers tests sur PowerPivot (Octobre 2011), j’ai été bloqué par l’absence de cette feature. J’ai même essayé de la développer avec une fonction M récursive à base de SelectNames() mais je ne suis pas arrivé au bout.

Mais aujourd’hui, j’ai juste à faire un update de l’addin directement dans Excel.

La version est la 1.0.3264.4.

Voyons le cas d’usage concret.

Vous savez que je base une bonne partie de mes démos sur des données Open Data fournies par la France (très pauvre sur le sujet mais on a quand même quelques chiffres).
Le jeu de données est une feuille Excel crado contenant la fréquentation des musées d’Ile-de-France :

http://www.data.gouv.fr/DataSet/30382387

On passera sur le fait que l’administration française n’ait aucune notion sur les datasets et la qualité de données (on voit bien qu’un fonctionnaire a juste déposé son fichier Excel 97 sur le site).

Concentrons nous sur le fait que les années sont en colonnes. Pour faire une jolie table de faits et ainsi avoir un modèle facilement exploitable (croissance annuelle, slicer temporel, play axis dans un Scatter Graph ou dans GeoFlow…), il nous faut pivoter la table.

Les utilisateurs d’Excel me diront (m’ont déjà dit) :

“c’est facile, tu fais un Transpose”

En fait, le transpose inverse ligne et colonne, il ne pivote pas une ou plusieurs colonnes. Le seul outil que je connaisse capable de faire cela est SSIS.

C’est parti. Pour commencer, importons les données ; pour cela, pas besoin de mettre le fichier Excel sur votre bureau, il suffit directement de laisser Data Explorer aller le chercher.

On fait un peu le ménage dans les lignes et les colonnes pour ne conserver que le tableau de données

Dans le détails, ça donne :

• Remove Top Rows
• Use First Row as Headers
• Change Type to Number (colonne 1)
• Filter Value <> null et Number.NaN (colonne 1)
• Hide Columns “Evolutions xxx”

Ensuite, on lance la fonction de Unpivot

Il faut sélectionner l’ensemble des colonnes que l’ont souhaite pivoter. Le nom de la colonne devient la valeur d’une nouvelle colonne “Attribute” et la valeur de la cellule devient la la valeur de la nouvelle colonne “Value”.

Evidemment, tout cela est paramétrable dans la fonction M:

= Table.Unpivot(FilteredRows,{"TOTAL#(lf) 06", "TOTAL#(lf) 07", "TOTAL#(lf) 08", "TOTAL#(lf) 09", "TOTAL#(lf) 10"},"TOTAL","Fréquentation")

Petite subtilité

Dans notre exemple, nous devons pivoter 2 fois : la fréquentation totale et la fréquentation gratuite. Le problème c’est qu’en pivotant 2 fois, on génère la combinatoire des années “totales” et “gratuites”.

L’astuce que je prends est la suivante :

• je transforme la colonne pour n’avoir que l’année

=Table.ReplaceValue(ReplacedValue1,"GRAT#(lf)","20",Replacer.ReplaceText,{"GRATUIT"})

• je filtre pour ne garder que les lignes quand les 2 colonnes sont égales

=Table.SelectRows(ReplacedValue2, each [TOTAL] = [GRATUIT])

Et voila le résultat :