Intégrer un tableau croisé complexe

Proposé par André Meyer-Roussilhon

10 février 2020

Restez informé, abonnez-vous à la newsletter (2 fois / mois)

8 + 8 =

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 :

Un tableau croisé complexe

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 :

Le remplissage vers le bas

É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) :
Une étape intermédiaire nécessaire
  • 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 :

Articles associés :

Délai entre deux dates, week-end et jours fériés

Délai entre deux dates, week-end et jours fériés

Une table du temps qui calcule automatiquement les jours fériés ? Vous en rêviez, daxone.fr vous l’offre ! Et j’en profite pour vous donner la méthode pour calculer le nombre de jours écoulés entre deux dates, en excluant les dimanches et les jours fériés (je suis sûr que ça va vous servir)

Power Query et M : le nouveau livre de référence

Power Query et M : le nouveau livre de référence

Le quatrième livre que je consacre à Power BI est sorti le 8 septembre : 306 pages entièrement dédiées à Power Query et au langage M, avec de nombreuses mises en pratique.
Plutôt que de séparer l’interface graphique (Power Query) et M (le code que génère l’interface), j’ai choisi de montrer comment les deux doivent être pensés dans une continuité : connaître ne serait-ce qu’un minimum du langage M permet d’améliorer, d’accélérer et d’étendre les possibilités du code généré automatiquement par l’interface graphique.

Exporter de Power BI vers CSV ou TXT sans être limité par le nombre de lignes

Exporter de Power BI vers CSV ou TXT sans être limité par le nombre de lignes

Lors de la dernière session de formation Power BI Expert que j’ai animée, un stagiaire a soulevé la question de la limite du nombre de lignes qu’il est possible d’exporter à partir de Power BI vers un fichier CSV. Cette limite est fixée à 30 000 lignes. Mais alors comment faire lorsque, ce qui n’est pas rare, vous souhaitez exporter une table ou un visuel contenant plus de 30 000 lignes ? Dans cet article je vous propose la solution.