Combiner les données de plusieurs sources de données (Power Query)

Important :  Cet article a été traduit automatiquement, voir l’avertissement. Vous pouvez consulter la version en anglais de cet article ici.

Remarque :  Les fonctionnalités de Power Query se trouvent dans le groupe Récupérer et transformer d’Excel 2016. Les informations fournies ici s’appliquent aux deux versions. Pour en savoir plus, voir Groupe Récupérer et transformer dans Excel 2016.

Dans ce didacticiel, vous allez utiliser l'éditeur de requête de Power Query pour importer des données depuis un fichier Excel local qui contient des informations sur le produit et à partir d'un flux qui OData contient des informations de commande de produit. Vous effectuez les opérations de transformation et d'agrégation et combinez les données de deux sources pour produire un rapport Total des ventes par produit et par an.

Pour effectuer ce didacticiel, vous avez besoin du classeur Products and Orders. Dans la boîte de dialogue Enregistrer sous, nommez le fichier Products and Orders.xlsx.

Contenu de ce didacticiel

Tâche 1 : importer des produits dans un classeur Excel

Étape 1 : se connecter à un classeur Excel

Étape 2 : promouvoir la première ligne en tant qu’en-têtes de colonne de table

Étape 3 : supprimer les autres colonnes pour afficher uniquement les colonnes utiles

Étapes Power Query créées

Étape 4 : importer une requête de produits

Tâche 2 : importer des données de commande à partir d’un flux OData

Étape 1 : se connecter à un flux OData

Étape 2 : développer une table Order_Details

Développer le lien de table Order_Details

Étape 3 : supprimer les autres colonnes pour afficher uniquement les colonnes utiles

Supprimer les colonnes sélectionnées

Étape 4 : calculer le total de chaque ligne Order_Details

Calculer le total de chaque ligne Order_Details

Étape 5 : transformer une colonne d’année OrderDate

Étape 6 : regrouper les lignes par ProductID et Year

Étape 7 : renommer une requête

Résultats de la requête finale

Étapes Power Query créées

Étape 8 : désactiver le téléchargement de la requête dans un classeur Excel

Désactiver le téléchargement d’une requête

Tâche 3 : combiner les requêtes Products et Total Sales

Étape 1 : fusionner ProductID dans une requête Total Sales

Étape 2 : développer une colonne de fusion

Développer le lien de table NewColumn

Étapes Power Query créées

Étape 3 : charger une requête Total Sales per Product dans un modèle de données Excel

Charger la requête Total Sales per Product dans le modèle de données Excel

Requête finale Total Sales per Product

Tâche 1 : importer des produits dans un classeur Excel

Au cours de cette tâche, vous allez importer des produits du fichier Products and Orders.xlsx dans un classeur Excel.

Étape 1 : se connecter à un classeur Excel

  1. Créez un classeur Excel.

  2. Dans l’onglet de ruban POWER QUERY, cliquez sur À partir d’un fichier > À partir d’Excel.

  3. Dans la boîte de dialogue Parcourir d’Excel, recherchez ou tapez le chemin d’accès au fichier Products and Orders.xlsx pour importer un fichier ou définir un lien vers un fichier.

  4. Dans le volet Navigateur, double-cliquez sur la feuille de calcul Products. Vous pouvez également cliquer sur Products, puis cliquer sur Modifier la requête. Lorsque vous modifiez une requête ou lorsque vous vous connectez à une nouvelle source de données, la fenêtre Éditeur de requête s’affiche.

    Remarque : Pour visionner une courte vidéo expliquant comment afficher l’Éditeur de requête, reportez-vous à la fin de cet article.

Étape 2 : promouvoir la première ligne en tant qu’en-têtes de colonne de table

Dans le volet Aperçu de la requête, la première ligne de la table ne contient pas le nom des colonnes. Pour promouvoir la première ligne en tant qu’en-têtes de colonne de la table :

  1. Cliquez sur l’icône de table ( Icône de table ) dans le coin supérieur gauche de l’aperçu des données.

  2. Cliquez sur Utiliser la première ligne pour les en-têtes.

Promouvoir la première ligne en tant qu’en-têtes de colonne de table

Étape 3 : supprimer les autres colonnes pour afficher uniquement les colonnes utiles

Dans cette étape, vous supprimez toutes les colonnes à l’exception de ProductID, ProductName, CategoryID et QuantityPerUnit.

  1. Dans le volet Aperçu de la requête, sélectionnez les colonnes ProductID, ProductName, CategoryID et QuantityPerUnit (en utilisant les combinaisons Ctrl+Clic ou Maj+Clic).

  2. Dans le ruban Éditeur de requête, cliquez sur Supprimer les colonnes > Supprimer d’autres colonnes ou cliquez avec le bouton droit sur un en-tête de colonne, puis cliquez sur Supprimer d’autres colonnes.

    Masquer les autres colonnes

Étapes Power Query créées

Tandis que vous effectuez des activités de requête dans Power Query, des étapes de requête sont créées et répertoriées dans le volet Paramètres d’une requête, dans la liste ÉTAPES APPLIQUÉES. Chaque étape de la requête a une formule Power Query correspondante, appelée également langage « M ». Pour plus d’informations sur le langage de formule Power Query, voir En savoir plus sur les formules Power Query.

Tâche

Étape de requête

Formule

Se connecter à un classeur Excel

Source

Source{[Name="Products"]}[Data]

Promouvoir la première ligne en tant qu’en-têtes de colonne de table

FirstRowAsHeader

Table.PromoteHeaders

(Produits)

Supprimer les autres colonnes pour afficher uniquement les colonnes utiles

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Étape 4 : importer une requête de produits

Dans cette étape, vous importez la requête Products dans votre classeur Excel.

  1. Dans le ruban Éditeur de requête, cliquez sur Appliquer et fermer. Les résultats apparaissent dans une nouvelle feuille de calcul Excel.

Haut de la page

Tâche 2 : importer des données de commande à partir d’un flux OData

Au cours de cette tâche, vous allez importer des données dans votre classeur Excel à partir du flux OData Northwind à l’adresse http://services.odata.org/Northwind/Northwind.svc.

Étape 1 : se connecter à un flux OData

  1. Dans l’onglet de ruban POWER QUERY, cliquez sur À partir d’autres sources > À partir d’un flux OData.

  2. Dans la boîte de dialogue Flux OData, entrez l’URL du flux OData Northwind.

  3. Cliquez sur OK.

  4. Dans le volet Navigateur, double-cliquez sur la table Commandes ou cliquez sur Commandes, puis sur Modifier.

Remarque :  Lorsque vous survolez une table, un aperçu de la table apparaît.

Survoler une source de données

Étape 2 : développer une table Order_Details

Dans cette étape, vous développez la table Order_Details liée à la table Orders pour combiner les colonnes ProductID, UnitPrice et Quantity de la table Order_Details dans la table Orders. L’opération de développement combine les colonnes d’une table liée dans une table d’objet. Lorsque la requête est exécutée, les lignes de la table liée (Order_Details) sont combinées avec les lignes de la table d’objet (Orders).

Dans Power Query, une colonne contenant un lien vers une table liée possède un lien Entrée ou Table. Un lien Entrée accède à un seul enregistrement connexe et représente unerelation un-à-un avec une table d’objet. Un lien Table accède à une table liée et représente une relation un-à-plusieurs avec une table d’objet. Un lien représente les propriétés de navigation dans une source de données au sein d’un modèle relationnel. Pour un flux OData, les propriétés de navigation représentent une entité avec une association de clé étrangère. Dans une base de données telle que SQL Server, les propriétés de navigation représentent les relations de clé étrangère dans la base de données.

Développer le lien de table Order_Details

Une fois que vous avez développé la table Order_Details, trois nouvelles colonnes et des lignes supplémentaires sont ajoutées à la table Orders, une pour chaque ligne dans la table imbriquée ou liée.

  1. Dans le volet Aperçu de la requête, accédez à la colonne Order_Details.

  2. Dans la colonne Order_Details, cliquez sur l’icône Développer ( Développer ).

  3. Dans le menu déroulant Développer :

    1. Cliquez sur (Sélectionner toutes les colonnes) pour effacer toutes les colonnes.

    2. Cliquez sur ProductID, UnitPrice et Quantity.

    3. Cliquez sur OK.

      Développer le lien de table Order_Details

      Remarque :  Dans Power Query, vous pouvez développer les tables liées à une colonne et effectuer des opérations d’agrégation sur les colonnes de la table liée avant de développer les données dans la table d’objet. Pour plus d’informations sur l’exécution des opérations d’agrégation, voir Agréger les données d’une colonne.

Étape 3 : supprimer les autres colonnes pour afficher uniquement les colonnes utiles

Dans cette étape, vous supprimez toutes les colonnes à l’exception de OrderDate, ProductID, UnitPrice et Quantity. Dans la tâche précédente, vous avez utilisé Supprimer d’autres colonnes. Pour cette tâche, vous devez supprimer les colonnes sélectionnées.

Supprimer les colonnes sélectionnées

  1. Dans le volet Aperçu de la requête, sélectionnez toutes les colonnes :

    1. Cliquez sur la première colonne (OrderID).

    2. Appuyez sur la touche Maj et cliquez sur la dernière colonne (Shipper).

    3. Appuyez sur la touche Ctrl et cliquez sur les colonnes OrderDate, Order_Details.ProductID, Order_Details.UnitPrice et Order_Details.Quantity.

  2. Cliquez avec le bouton droit sur un en-tête de colonne sélectionné, puis cliquez sur Supprimer les colonnes.

Étape 4 : calculer le total de chaque ligne Order_Details

Dans cette étape, vous créez une colonne personnalisée pour calculer le total de chaque ligne Order_Details.

Calculer le total de chaque ligne Order_Details

  1. Dans le volet Aperçu de la requête, cliquez sur l’icône de table ( Icône de table ) dans le coin supérieur gauche de l’aperçu.

  2. Cliquez sur Insérer une colonne > Personnalisée.

  3. Dans la boîte de dialogue Insérer une colonne personnalisée, dans la zone de texte Formule de colonne personnalisée, entrez [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. Dans la zone de texte Nouveau nom de colonne, entrez Line Total.

  5. Cliquez ensuite sur OK.

Calculer le total de chaque ligne Order_Details

Étape 5 : transformer une colonne d’année OrderDate

Dans cette étape, vous transformez la colonne OrderDate pour afficher l’année de la date de commande.

  1. Dans la grille Aperçu, cliquez avec le bouton droit sur la colonne OrderDate, puis cliquez sur Transformer > Year.

  2. Renommez la colonne OrderDate en Year :

    1. Double-cliquez sur la colonne OrderDate, puis entrez Year ou

    2. Cliquez avec le bouton droit sur la colonne OrderDate, cliquez sur Renommer, puis entrez Year.

Étape 6 : regrouper les lignes par ProductID et Year

  1. Dans le volet Aperçu de la requête, sélectionnez Year et Order_Details.ProductID.

  2. Cliquez avec le bouton droit sur un des en-têtes, puis cliquez sur Regrouper par.

  3. Dans la boîte de dialogue Regrouper par :

    1. Dans la zone de texte Nouveau nom de colonne, entrez Total Sales.

    2. Dans le menu déroulant Opération, sélectionnez Somme.

    3. Dans le menu déroulant Colonne, sélectionnez Line Total.

  4. Cliquez sur OK.

    Boîte de dialogue Regrouper par pour les opérations d’agrégation

Étape 7 : renommer une requête

Avant d’importer les données de ventes dans Excel, nommez la requête Total Sales :

  1. Dans le volet Paramètres d’une requête, dans la zone Nom, entrez Total Sales.

Résultats de la requête finale

Après avoir effectué chaque étape, vous aurez une requête Ventes totales sur le flux de données OData Northwind.

Total des ventes

Étapes Power Query créées

Tandis que vous effectuez des activités de requête dans Power Query, des étapes de requête sont créées et répertoriées dans le volet Paramètres d’une requête, dans la liste ÉTAPES APPLIQUÉES. Chaque étape de la requête a une formule Power Query correspondante, appelée également langage « M ». Pour plus d’informations sur le langage de formule Power Query, voir En savoir plus sur les formules Power Query.

Tâche

Étape de requête

Formule

Se connecter à un flux OData

Source

Source{[Name="Orders"]}[Data]

Développer la table Order_Details

Développer Order_Details

Table.ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Supprimer les autres colonnes pour afficher uniquement les colonnes utiles

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Calculer le total de chaque ligne Order_Details

InsertedColumns

Table.AddColumn

(RemovedColumns, "Personnalisée", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Transformer la colonne OrderDate pour afficher l’année

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Personnalisée", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Regrouper les lignes par ProductID et Year

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), type number}})

Étape 8 : désactiver le téléchargement de la requête dans un classeur Excel

Comme la requête Total Sales ne représente pas le rapport Total Sales per Product and Year, vous devez désactiver le téléchargement de la requête dans le classeur Excel. Lorsque l’option Charger la feuille de calcul est désactivée dans le volet Paramètres d’une requête, le résultat de données de cette requête n’est pas téléchargé, mais la requête peut toujours être combinée avec d’autres requêtes afin d’obtenir le résultat souhaité. Vous allez découvrir comment combiner cette requête avec la requête Products dans la tâche suivante.

Désactiver le téléchargement d’une requête

  1. Dans le volet Paramètres d’une requête, désactivez la case à cocher Charger la feuille de calcul.

  2. Dans le ruban Éditeur de requête, cliquez sur Appliquer et fermer. Dans le volet Requêtes de classeur, la requête Total Sales indique Le chargement est désactivé.

    Désactiver le téléchargement d’une requête

Haut de la page

Tâche 3 : combiner les requêtes Products et Total Sales

Power Query vous permet de combiner plusieurs requêtes en fusionnant ou en ajoutant celles-ci. L’opération de fusion est exécutée sur n’importe quelle requête Power Query avec une forme tabulaire, indépendamment de la source de données dont proviennent les données. Pour plus d’informations sur la combinaison de sources de données, voir Combiner plusieurs requêtes.

Dans cette tâche, vous combinez les requêtes Products et Total Sales en utilisant une étape de requête Fusionner et Développer.

Étape 1 : fusionner ProductID dans une requête Total Sales

  1. Dans le classeur Excel, accédez à la requête Products sur la feuille Sheet2.

  2. Dans l’onglet de ruban REQUÊTE, cliquez sur Fusionner.

  3. Dans la boîte de dialogue Fusionner, sélectionnez Products comme table primaire, puis Total Sales comme deuxième requête ou requête associée à fusionner. Total Sales devient une nouvelle colonne extensible.

  4. Pour associer Total Sales à Products via la colonne ProductID, sélectionnez la colonne ProductID dans la table Products, et la colonne Order_Details.ProductID dans la table Total Sales.

  5. Dans la boîte de dialogue Niveaux de confidentialité :

    1. Sélectionnez le niveau de confidentialité Organisationnel pour les deux sources de données.

    2. Cliquez sur Enregistrer.

  6. Cliquez sur OK.

    Note de sécurité :  Les niveaux de confidentialité empêchent les utilisateurs de combiner par inadvertance les données de plusieurs sources de données (potentiellement privées ou organisationnelles). Selon la requête, les utilisateurs pourraient envoyer par inadvertance des données de la source de données privée à une autre source de données pouvant être nuisible. Power Query analyse chaque source de données et classe celle-ci au niveau de confidentialité défini : Public, Organisationnel et Privé. Pour plus d’informations sur les niveaux de confidentialité, voir Niveaux de confidentialité.

    Boîte de dialogue Fusionner

Une fois que vous avez cliqué sur OK, l’opération de fusion crée une requête. Le résultat de la requête contient toutes les colonnes de la table primaire (Products) et une seule colonne contenant un lien de navigation vers la table liée (Total Sales). Une opération de développement ajoute de nouvelles colonnes à la table primaire ou à la table d’objet à partir de la table liée.

Fusion finale

Étape 2 : développer une colonne de fusion

Dans cette étape, vous développez la colonne de fusion nommée NewColumn pour créer deux colonnes dans la requête Products : Year et Total Sales.

Développer le lien de table NewColumn

  1. Dans le volet Aperçu de la requête, cliquez sur l’icône Développer de NewColumn ( Développer ).

  2. Dans le menu déroulant Développer :

    1. Cliquez sur (Sélectionner toutes les colonnes) pour effacer toutes les colonnes.

    2. Cliquez sur Year et sur Total Sales.

    3. Cliquez sur OK.

  3. Renommez ces deux colonnes Year et Total Sales.

  4. Effectuez un tri décroissant sur Total Sales pour déterminer les produits et les années pendant lesquelles les produits ont présenté le plus grand volume de ventes.

  5. Renommez la requête Total Sales per Product.

Développer le lien de table

Étapes Power Query créées

Tandis que vous effectuez les activités de requête Fusionner dans Power Query, des étapes de requête sont créées et répertoriées dans le volet Paramètres d’une requête, dans la liste ÉTAPES APPLIQUÉES. Chaque étape de la requête a une formule Power Query correspondante, appelée également langage « M ». Pour plus d’informations sur le langage de formule Power Query, voir En savoir plus sur les formules Power Query.

Tâche

Étape de requête

Formule

Fusionner ProductID dans la requête Total Sales

Source (source de données pour l’opération de fusion)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Développer une colonne de fusion

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

Étape 3 : charger une requête Total Sales per Product dans un modèle de données Excel

Dans cette étape, vous désactivez l’option Charger dans la feuille de calcul et chargez une requête dans le modèle de données Excel pour créer un rapport connecté au résultat de la requête. En plus de charger les résultats de la requête dans une feuille de calcul Excel, Power Query vous permet de charger un résultat de requête dans un modèle de données Excel. Une fois que vous avez chargé les données dans le modèle de données Excel, vous pouvez utiliser Power Pivot et Power View pour approfondir l’analyse des données.

Charger la requête Total Sales per Product dans le modèle de données Excel

  1. Dans le volet Paramètres d’une requête, décochez l’option Charger la feuille de calcul et cochez Charger dans le modèle de données.

  2. Pour charger la requête dans le modèle de données Excel, cliquez sur Appliquer et fermer.

Charger le modèle de données Excel

Requête finale Total Sales per Product

Une fois que vous avez effectué chaque étape, vous avez une requête Total Sales per Product qui combine les données du fichier Products and Orders.xlsx et du flux OData Northwind. Cette requête peut être appliquée à un modèle Power Pivot. Par ailleurs, les modifications apportées à la requête dans Power Query modifient et actualisent la table résultante dans le modèle Power Pivot.

Haut de la page

Remarque : L’Éditeur de requête s’affiche uniquement lorsque vous chargez, modifiez ou créez une requête à l’aide de Power Query. La vidéo suivante montre la fenêtre de l’Éditeur de requête qui s’affiche après la modification d’une requête à partir d’un classeur Excel. Pour afficher l’Éditeur de requête sans charger ou modifier une requête de classeur existante, dans la section Obtenir des données externes de l’onglet de ruban Power Query, sélectionnez À partir d’autres sources > Requête vide. La vidéo suivante montre une manière d’afficher l’Éditeur de requête.

Comment afficher l’Éditeur de requête dans Excel

Remarque : Avertissement traduction automatique : cet article a été traduit par un ordinateur, sans intervention humaine. Microsoft propose cette traduction automatique pour offrir aux personnes ne maîtrisant pas l’anglais l’accès au contenu relatif aux produits, services et technologies Microsoft. Comme cet article a été traduit automatiquement, il risque de contenir des erreurs de grammaire, de syntaxe ou de terminologie.

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×