Power Query – Unpivot / Compléter cellules vides

Nous n’avons pas encore eu beaucoup de recettes sur Power Query et c’est dommage car c’est un outil (intégré à Excel 2016 et à installer avec Excel 2010 ou 2013) extrêmement puissant ! Je vais vous montrer à travers cet exemple une toute petite partie du potentiel de Power Query. Et dans de futurs articles, je vous montrerai d’autres utilités de Power Query.

Présentation du contexte

Ci-dessous, vous pouvez voir une extraction réalisée depuis un outil de trésorerie. Bien sûr j’ai modifié les données et j’ai simplifié l’extraction pour l’exemple.

Vous pouvez voir que sur la première ligne j’ai les dates et en colonne, la partie Crédit et les opérations correspondantes, puis la partie Débit. Par exemple en cellule B4, j’ai eu une rentrée d’argent de 1969 euros, le 15/15/2016.

Unpivot_01

Idéalement, j’aimerais pouvoir réaliser un tableau de bord sur ces données. Mais ce tableau (simplifié) est difficilement exploitable. J’aimerais avoir quelque chose de ce style pour créer des tableaux croisés dynamiques:

Unpivot_24

Utilisation de Power Query

Power Query va nous permettre de transformer le tableau d’origine en tableau de destination présenté ci-dessus.

La première étape est d’importer le tableau dans Power Query. Pour cela, sélectionnez votre tableau puis allez dans le ruban.

RUBAN: Données > Récupérer et transformer > A partir d’un tableau

Unpivot_02

Unpivot_03

Une nouvelle fenêtre va s’ouvrir avec le tableau importé:

Unpivot_04

Ajout d’une colonne personnalisée

Le but ici est de rajouter une colonne dans laquelle je vais définir le type de flux: IN ou OUT. Nous créerons ensuite une colonne NomFlux dans laquelle nous mettrons les opérations. L’idée est de séparer le type de flux et l’opération.

Commençons par créer cette première colonne.

RUBAN: Ajouter une colonne > Général > Ajouter une colonne personnalisée

Unpivot_05

Une nouvelle fenêtre apparaît. Entrez d’abord le nom de la nouvelle colonne (Flux) puis tapez la formule suivante (en langage M):

= if Texte.Range([Colonne1],0,1)=” ” then null else [Colonne1]

Cette formule évalue le premier caractère de la Colonne1. Si le caractère est un espace alors on mettra la valeur null, sinon, on remplira avec la valeur de la Colonne1.

Attention, la fonction Text.Range doit absolument s’écrire avec des majuscules au début de chaque mot pour que la fonction marche.

Cette fonction correspond à la fonction STXT d’Excel.

Le langage M est le nouveau langage Microsoft et est utilisé pour Power Query.Unpivot_06

Après avoir cliqué sur OK, vous pouvez voir la nouvelle colonne qui a été ajoutée au tableau:

Unpivot_07

Compléter les cellules Null

L’objectif ici est de remplir les valeurs NULL avec la première valeur non nulle au-dessus. Ainsi nous aurons IN pour les premières lignes et la valeur OUT pour les cellules du bas.

Sélectionnez la nouvelle colonne, faites un clic droit puis choisissez Remplir puis vers la bas.

Unpivot_08

Vous pouvez voir ainsi la colonne remplie comme ci-dessous.

Unpivot_09

Déplacez la colonne tout à gauche. Vous pouvez constater le résultat ci-dessous:

Unpivot_10

Supprimer les lignes

Certaines lignes ne nous intéressent pas: Dans la Colonne1 lorsque la valeur est IN ou OUT. En effet, nous ne souhaitons que les lignes contenant les opérations puisque nous avons déjà l’information IN ou OUT dans la colonne que nous venons de créer.

Dans la Colonne1, cliquez sur la petite flèche puis filtrez en désélectionnant IN et OUT comme ci-dessous:

Unpivot_11

A présent, nous n’avons que les lignes qui nous intéressent:

Unpivot_12

Supprimer les espaces inutiles

Dans la Colonne1, les opérations commencent par un espace et nous souhaitons les supprimer. Nous allons créer une nouvelle colonne personnalisée:

RUBAN: Ajouter une colonne > Général > Ajouter une colonne personnalisée

Unpivot_13Nommez d’abord la colonne: NomFlux.

Puis tapez la formule (en langage M encore !) suivante:

=Text.Trim([Colonne1])

Comme tout à l’heure, il faut bien faire attention aux majuscules.

Cette fonction est la même que sous Excel en anglais: TRIM, et en français: SUPPRESPACE. Pour comprendre la fonction Excel, vous pouvez voir l’article sur SUPPRESPACE.

Unpivot_14

En cliquant sur OK, vous obtenez la nouvelle colonne:

Unpivot_15

Supprimer une colonne

A présent, nous pouvons supprimer l’ancienne colonne qui ne nous servira plus. Sélectionnez la colonne puis supprimez-la:

RUBAN: Accueil > Gérer les colonnes > Supprimer les colonnes

Unpivot_16

Puis déplacez la colonne créée en deuxième position:

Unpivot_17

Unpivot: transformer le tableau

C’est la partie la plus importante de la recette ! Sélectionnez les colonnes à transformer, c’est à dire toutes les colonnes dont le titre est une date. Puis transformez:

RUBAN: Transformer > N’importe quelle colonne > Supprimer le tableau croisé dynamique des colonnes

Unpivot_18

Et vous pouvez voir que le tableau mis à jour ressemble beaucoup à ce que nous souhaitions réaliser:

Unpivot_19

Mettre à jour le format des colonnes

Nous allons commencer par renommer les deux nouvelles colonnes en cliquant deux fois sur le titre.

Renommez ainsi: Date et Montant.

Unpivot_20

Nous allons à présent modifier le format de la colonne Date car nous souhaitons avoir des données qui correspondent à des dates.

Sélectionnez la colonne, puis

RUBAN: Accueil > Transformer > Type de données > Date

Unpivot_21

Renommer le nom de la table

Jusqu’à présent, notre table s’appelait Tableau1. Pour que ce soit plus clair, nous allons renommer en T_Flux. Sur la partie de droite, dans Paramètres d’une requête, modifiez le Nom en écrivant T_Flux:

Unpivot_22

Charger les données

Nous arrivons à la fin de notre recette. A présent, il faut rapatrier notre tableau dans Excel.

RUBAN: Accueil > Fermer et charger

Unpivot_23

Et voilà le résultat:

Unpivot_24

Conclusion

Maintenant, il suffit de créer votre tableau croisé dynamique ou de réaliser le tableau de bord souhaité.

Unpivot_25

Si vous revenez dans Power Query (sélectionnez T_Flux sur la droite, puis clic droit et Modifier), vous pouvez voir sur la droite toutes les étapes qui ont été appliquées. En cliquant sur une étape, vous pouvez voir le tableau au moment de cette étape:

Unpivot_26

J’espère que cette recette a pu vous montrer une toute partie du potentiel de Power Query et que cela vous donnera envie d’aller plus loin.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *