Power BI : Power Query et le pivot automatique des colonnes
Power Query est un add-in Excel et composant de Power BI qui fonctionne un peu comme un ETL (Extract, Transform and Load). A partir de n’importe quelle source de données (Base de données, Ficher plat, page web ou Hadoop), Excel est capable de récupérer les informations et de les transformer en fonction des besoins.
La nouvelle version de l’add-in propose de nouvelles fonctions, dont une très pratique, Table.UnpivotOtherColumns, qui va faire l’objet de ce billet.
Cette fonction permet de faire pivoter des colonnes d’une source de données. Et de manière dynamique! C’est-à-dire que la formule va accepter comme paramètre les colonnes qui ne devront pas pivoter, et toutes les autres pivoteront. Ce qui peut être pratique lorsque l’on récupère des données temporelles par exemple.
Ci-dessous un exemple concret, accompagné des copies d’écran.
Dans un fichier, voici un tableau assez basique.
Dans le ruban, cliquez sur « Power Query » puis sur « From File », afin d’aller chercher le fichier Excel contenant le tableau.
Power Query propose la structure du fichier. Dans mon exemple, le tableau se trouve dans la feuille 1 (sheet1). Donc dans la partie gauche, cliquez sur « Sheet1 »
J’utilise une fonction basique de Power Query pour définir la première ligne comme entête de colonne en cliquant sur le bouton :
Voici le résultat :
Maintenant je choisi les colonnes que je souhaite faire « pivoter ». Puis je fais un clic droit sur les colonnes pour faire apparaître le menu contextuel pour sélectionner la commande « Unpivot »
Ci-dessous, le résultat :
En regardant en détail la formule générée par Power Query, on remarque que les colonnes représentant les dates sont nommées dans la formule :
= Table.Unpivot(FirstRowAsHeader,{"2011", "2012", "2013", "2014"},"Attribute","Value")
La nouvelle version de l’add-in apporte une nouvelle fonction qui est « Table.UnpivotOtherColumns »
Je vais donc transformer la formule avec Table.UnpivotOtherColumns et en définissant maintenant la ou les colonnes qui ne doivent pas pivoter:
= Table.UnpivotOtherColumns(FirstRowAsHeader, {"Produits"}," Attribute","Value")
Voici le résultat (en fait rien n’a changé 
):
Et voici le tableau dans Excel après avoir cliqué sur “Done” :
Maintenant, je reviens sur le fichier d’origine, et que je rajoute une colonne (2015 par exemple)
Je rafraichi ma requête Power Query (en cliquant sur « Refresh » dans le volet « Query Settings »). Le tableau se met automatiquement à jour, en conservant le pivot et en rajoutant les informations de la nouvelle colonne :
Voila pour l’illustration de cette nouvelle fonction Power Query… une vidéo arrive pour illustrer ce billet.
Au plaisir de vous voir lors d’un IT Camp!. Les IT Camps sur SQL 2014 et Power BI débuteront en novembre.
Pour tester Windows Server 2012, Windows 8, SQL Server 2012 et SQL Server 2014 CTP1, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
· Windows Server 2012 :
-
- d'une image ISO : https://aka.ms/jeveuxwindows2012
- d'un fichier VHD avec un système préinstallé : https://aka.ms/jeveuxwindows2012
· SQL Server 2012 :
· Evaluation SQL Server 2014 CTP2 :
· Testez Azure gratuitement pendant un mois :