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.
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:
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
Une nouvelle fenêtre va s’ouvrir avec le tableau importé:
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
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.
Après avoir cliqué sur OK, vous pouvez voir la nouvelle colonne qui a été ajoutée au tableau:
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.
Vous pouvez voir ainsi la colonne remplie comme ci-dessous.
Déplacez la colonne tout à gauche. Vous pouvez constater le résultat ci-dessous:
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:
A présent, nous n’avons que les lignes qui nous intéressent:
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
Nommez 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.
En cliquant sur OK, vous obtenez la nouvelle colonne:
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
Puis déplacez la colonne créée en deuxième position:
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
Et vous pouvez voir que le tableau mis à jour ressemble beaucoup à ce que nous souhaitions réaliser:
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.
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
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:
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
Et voilà le résultat:
Conclusion
Maintenant, il suffit de créer votre tableau croisé dynamique ou de réaliser le tableau de bord souhaité.
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:
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.