25 février 2019

|

par : jeremy

|

Catégories: Excel, PowerQuery

Power Query : trouver une donnée entre deux dates

Excel inclut un jeu puissant de fonctionnalités appelées récupérer et transformer, qui offre des fonctionnalités de collecte et de mise en forme rapides et simples. Récupérer et transformer vous permet de connecter, combiner et optimiser les sources de données pour répondre à vos besoins d’analyse. Ces fonctionnalités sont également utilisées dans Power bi et dans le complément Power Query disponible pour les versions antérieures d’Excel. En savoir plus

Vous avez une liste de transactions de vente avec la quantité de produit pour chaque vente. Le prix unitaire dépend de la date de vente. En effet, le prix pour le mois de janvier est différent du prix du mois de février. Comment transformer vos données dans Power Query pour prendre en compte cette contrainte ?

Contexte et données

Voici les données utilisées pour notre exemple

Tanleaux de données

Vous pouvez voir deux tableaux. Le premier contient la liste des produit avec un prix valable pour une date de début et une date de fin. Par exemple, si nous prenons le Produit dont le code est “P1”, le prix est de 10 euros pour la période du 1er janvier au 31 janvier. Puis le prix passe à 10,20 euros pour le mois suivant.

Le second tableau contient un tableau simplifié de ventes avec la date, le code produit et la quantité. La question est donc de savoir comment trouver le prix unitaire en fonction de la date de vente. Ensuite nous pourrons calculer le prix total (Quantité x Prix unitaire).

Création d'une table avec le prix par jour et par produit

Objectif

Afin de résoudre notre problématique, notre objectif est de trouver un lien entre nos deux tables. Le lien qui servira de jointure devra être composé du code produit et de la date. Le problème est que dans le premier tableau, nous n’avons qu’une plage de date et non toutes les dates comprises dans cette plage. Notre première tâche sera donc de créer une liste des code produit et prix par jour.

Importer dans Power Query pour transformer

La première étape consiste donc à importer le premier tableau dans Power Query. Pour cela, il faut cliquer sur le bouton suivant:

Ruban : Données > Récupérer et transformer > A partir d’un tableau ou d’une plage

A partir d'un tableau ou d'une plage

Puis nous allons mettre à jour les types de données comme présenté sur l’écran suivant. Sélectionnez d’abord l’étape “Type Modifié” (à droite) puis modifiez directement le type dans le titre des colonnes.Type de données

Création d’une liste de dates entre deux dates définies

Pour créer une liste de dates entre deux dates, il existe une fonctionnalité dans Power Query : List.Dates

Cette fonction nécessite une date de départ (celle qui correspond à notre date de début de prix) et un nombre de jours. Comme nous n’avons que la date de fin de période, nous allons devoir calculer le nombre de jours entre le début et la fin de la période.

Différence entre deux dates

Nous allons ajouter une nouvelle colonne qui va contenir la différence entre la colonne “End Date” et la colonne “Start Date”.

soustraction entre deux dates

Dans le ruban, cliquez sur “Ajouter une colonne” puis sélectionnez la colonne “End Date” puis la colonne “Start Date” en maintenant la touche CTRL. Dans le ruban, cliquez ensuite sur Date puis sur Soustraction.

Création de la liste de dates

Nous pouvons à présent ajouter la liste de dates car nous avons toutes les informations nécessaires. Pour cela, allez dans le ruban et cliquez sur “Ajouter une colonne personnalisée” et tapez ensuite la formule suivante :

=List.Dates([Start Date],[Soustraction]+1,#duration(1,0,0,0,0))

La fonction #duration permet de définir un résultat en jours. En savoir plus sur #duration.

List.Dates

Votre nouvelle colonne contient à présent une liste de listes. Chaque cellule contient en fait la liste des dates depuis la date de début jusqu’à la date de fin. Nous allons à présent développer cette nouvelle colonne en cliquant sur le petit icône à côté du titre puis en choisissant “Développer sur de nouvelles lignes”.

développer liste

Nettoyer et fermer

Il ne reste plus qu’à supprimer les colonnes inutiles pour la suite des événements, à savoir les colonnes “Start Date”, “End Date” et “Soustraction”.

supprimer les colonnes

Modifiez le format de la colonne date en spécifiant un format date.

Format date

Cliquez enfin sur fermer et charger dans puis choisir “Ne créer que la connexion”.

Faire le lien entre les deux tables

Ajouter la table à Power Query

Tout comme pour la table précédente, nous allons donc ajouter “à partir d’un tableau ou d’une plage” puis mettre à jour le type de données. Vous devriez avoir l’écran suivant :

Fusionner les deux tables (jointure)

Commencez par cliquer sur “Fusionner des requêtes” (dans le ruban : Accueil > Combiner). Nous allons à présent définir les clés de la jointure. Nous souhaitons donc faire le lien entre le produit et la date pour chaque table. Commencez donc par sélectionner la colonne “Produit” puis en maintenant la touche CTRL, cliquez sur la colonne “Date”. Vous devriez voir apparaître des petits numéros à côté des colonnes.

Ensuite, choisissez la table Prix et refaites la même manipulation : sélectionner la colonne “Product” puis en maintenant la touche CTRL, cliquez sur la colonne “Date”.

jointure

Développer les nouvelles données

Vous pouvez maintenant cliquer sur le bouton développer en sélectionnant uniquement le prix et en pensant à décocher “Utiliser le nom de la colonne d’origine comme préfixe”.

Le prix unitaire pour chaque ligne est à présent le bon, correspondant à la fois au produit et à la date.

développer prix

Calculer le prix total

Pour terminer notre cas, nous allons calculer le chiffre d’affaire pour chaque ligne puis nous chargerons les données dans Excel. Pour le calcul, sélectionnez les deux dernières colonnes (“Quantité” et “Prix”) en maintenant le bouton CTRL. Dans le ruban, “Ajouter une colonne”, cliquer sur “Standard” puis “Multiplier”.

Multiplier

Finaliser

A présent, vous avez tout calculé.

tableau final

Il ne vous reste plus qu’à charger les données en cliquant sur “Fermer et charger dans” puis en sélectionner “tableau”.