[vc_row css= ».vc_custom_1538728120863{margin-bottom: 30px !important;} »][vc_column][vc_column_text]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[/vc_column_text][vc_empty_space height= »10px »][vc_column_text]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 ?[/vc_column_text][/vc_column][/vc_row][vc_row][vc_column][vc_custom_heading text= »Contexte et données » font_container= »tag:h3|text_align:left » use_theme_fonts= »yes »][vc_empty_space height= »10px »][vc_column_text]
Voici les données utilisées pour notre exemple
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).[/vc_column_text][vc_empty_space height= »10px »][/vc_column][/vc_row][vc_row css= ».vc_custom_1538728261184{margin-bottom: 30px !important;} »][vc_column][vc_custom_heading text= »Création d’une table avec le prix par jour et par produit » font_container= »tag:h3|text_align:left » use_theme_fonts= »yes »][vc_empty_space height= »10px »][vc_column_text]
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
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.
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 ».
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))
La fonction #duration permet de définir un résultat en jours. En savoir plus sur #duration.
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 ».
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 ».
Modifiez le format de la colonne date en spécifiant un format date.
Cliquez enfin sur fermer et charger dans puis choisir « Ne créer que la connexion ».[/vc_column_text][vc_empty_space][/vc_column][/vc_row][vc_row css= ».vc_custom_1538728261184{margin-bottom: 30px !important;} »][vc_column][vc_custom_heading text= »Faire le lien entre les deux tables » font_container= »tag:h3|text_align:left » use_theme_fonts= »yes »][vc_empty_space height= »10px »][vc_column_text]
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 ».
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.
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 ».
Finaliser
A présent, vous avez tout calculé.
Il ne vous reste plus qu’à charger les données en cliquant sur « Fermer et charger dans » puis en sélectionner « tableau ».[/vc_column_text][vc_empty_space][/vc_column][/vc_row][vc_row][vc_column width= »1/2″][vc_column_text][/vc_column_text][/vc_column][vc_column width= »1/2″][vc_column_text]
[/vc_column_text][/vc_column][/vc_row]