Dépivoter un tableau croisé complexe

Le fichier source

Le tableau que nous allons intégrer donne le nombre trimestriel de commandes pour différents clients, de 2018-T1 à 2020-T2 :

Ce type de structure rend impossible toute analyse dans Power BI, il s’agit donc bien entendu de mettre ces données « à plat », et de se débarrasser des éléments superflus : totaux en ligne et en colonne, cellules fusionnées, et en-têtes de colonnes sur plusieurs lignes.

Appelé dans Power Query, l’aperçu du fichier n’est pas encourageant :

C’est le moment de se retrousser les manches et de Transformer les données : direction Power Query !

Première étape, supprimer les totaux

Commençons par supprimer les totaux : ils se trouvent sur la dernière ligne du tableau, et dans la dernière colonne. Dans le premier cas (total en bas d’une colonne), aucun problème, la fonction Supprimer les lignes > Supprimer les lignes du bas > 1 fait le travail.

Pour le total en ligne (donc, le total pour un client), c’est plus compliqué : en effet, deux cas de figure se présentent. Soit la structure est figée (le nombre de colonnes ne change pas), soit elle évolue (dans notre exemple, on peut imaginer une nouvelle version du fichier avec 2020-T3 et 2020-T4). Dans le premier cas, une fois sélectionnée la colonne des totaux, la fonction Supprimer les colonnes fait l’affaire. Mais regardez le code M :

Table.RemoveColumns(#"Dernières lignes supprimées",{"Column25"})

C’est la colonne appelée « Column25 » qui sera supprimée, et ceci quelque soit son contenu. Une fois ajoutés les deux trimestres de 2020, un problème va survenir, et l’actualisation du rapport génèrera une erreur (un trimestre aura disparu, et le total lui, sera à nouveau présent). Cette suppression de la dernière colonne est donc prématurée, et nous devons la réserver pour plus tard.

Étape suivante, les cellules fusionnées

La colonne Type de compte présente des cellules fusionnées, justifiées dans Excel, mais pas du tout dans Power BI. Une fois la colonne 1 sélectionnée, la fonction Remplir vers le bas (onglet Transformer) rétablit facilement la situation :

Étapes suivantes, mettre sur une seule ligne les en-têtes de colonnes

Le but des nombreuses étapes qui suivent est simple : obtenir une seule ligne où se retrouveront les en-têtes de toutes nos données. L’ensemble des transformations se fait sans recours au langage M, et toutes les fonctions sont disponibles dans les rubans Accueil et Transformer de Power Query. Considérez la description ci-dessous comme une « recette » que vous pourrez adapter à toutes les situations.

• La première ligne de notre fichier Excel a été automatiquement promue en en-tête : or cette ligne n’apporte rien (c’est le canal de distribution), il s’agit donc de la supprimer. Pour ça, je rétrograde l’en-tête (Utiliser les en-têtes comme première ligne), et je supprime cette première ligne
• Je vais ensuite traiter le cas des cellules fusionnées dans les en-têtes de colonne :  pour cela, j’inverse lignes et colonnes (fonction Transposer la table), et j’applique à nouveau la fonction Remplir vers le bas :

J’en ai profité au passage pour supprimer la dernière ligne, où se trouvera toujours le total en colonne (total par client dans notre exemple)

• Attention, étape technique : dans la capture d’écran ci-dessus, les transactions figurent en réalité à partir de la colonne 4 (c’est là que se trouve le nombre de commandes). Pour pouvoir à terme traiter les trois premières colonnes comme je le souhaite, j’ai besoin de les fusionner temporairement (je les sélectionne toutes les trois, puis par clic-droit, j’invoque la fonction Fusionner les colonnes en utilisant le séparateur « : » ou n’importe quel autre) :

• Je transpose ensuite à nouveau la table, et le résultat, c’est que les en-têtes de colonnes du fichier initial sont maintenant « aplatis », ils se présentent sur une seule ligne :

• Je vais à présent pouvoir promouvoir cette ligne et en faire l’en-tête de colonne (Utiliser la première ligne pour les en-têtes) :

• Et il est maintenant possible de « dépivoter » le tableau croisé, en sélectionnant les 4 premières colonnes et en appelant par clic-droit la fonction Dépivoter les autres colonnes (pour tenir compte d’une évolution future du fichier) :

• Pour finir, il suffit de fractionner la colonne appelée Attribut (clic-droit, Fractionner > Par délimiteur), et renommer les colonnes :

Voilà, vous êtes au bout de vos peines ; ça paraît long mais en réalité c’est assez rapide, et surtout, vos n’aurez plus à le faire : les nouveaux trimestres seront automatiquement intégrés.

En résumé, les étapes appliquées :