Découvrir comment combiner plusieurs sources de données (Power Query)

Dans ce didacticiel, vous pouvez utiliser l’Éditeur de requête de Power Query pour importer des données à partir d’un fichier Excel local contenant des informations sur des produits et d’un flux OData contenant des informations sur des commandes de produits. Vous devez effectuer les étapes de transformation et d’agrégation et combiner les données des deux sources pour produire un rapport « Total Sales per Product and Year ».   

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

Dans cette tâche, vous importez des produits du fichier Products and Orders.xlsx (téléchargé et renommé ci-dessus) dans un classeur Excel, vous pouvez promouvoir des lignes en tant qu’en-têtes de colonnes, supprimer certaines colonnes et charger la requête dans une feuille de calcul.

Étape 1 : se connecter à un classeur Excel

  1. Créez un classeur Excel.

  2. Sélectionnez Données> Obtenir des données à partir > fichier > à partir d’un classer.

  3. Dans la boîte de dialogue Importer des données, recherchez le fichier Products.xlsx que vous avez téléchargé, puis sélectionnez Ouvrir.

  4. Dans le volet Navigateur, double-cliquez sur la table Produits. L’Éditeur Power Query s’affiche.

Étape 2 : examiner les étapes de la requête

Par défaut, Power Query ajoute automatiquement plusieurs étapes. Examinez chaque étape sous Étapes appliquées dans le volet Paramètres d’une requête pour en savoir plus.

  1. Cliquez avec le bouton droit sur l’étape Source, puis sélectionnez Modifier les paramètres. Cette étape a été créée lorsque vous avez importé le workbook.

  2. Cliquez avec le bouton droit sur l’étape de navigation, puis sélectionnez Modifier les paramètres. Cette étape a été créée lorsque vous avez sélectionné la table dans la boîte de dialogue Navigation.

  3. Cliquez avec le bouton droit sur l’étape Type modifié, puis sélectionnez Modifier les paramètres. Cette étape a été créée par Power Query, qui infère les types de données de chaque colonne. Sélectionnez la flèche vers le bas à droite de la barre de formule pour voir la formule complète.

É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 Aperçu desdonnées, sélectionnez les colonnes ProductID,ProductName,CategoryIDet QuantityPerUnit (utilisez Ctrl+Clic ou Shift+Clic).

  2. Sélectionnez Supprimer les > supprimer d’autres colonnes.

    Masquer les autres colonnes

Étape 4 : charger la requête Produits

Dans cette étape, vous chargez la requête Produits dans une feuille de calcul Excel.

  • Sélectionnez Accueil > Fermer & chargement. La requête apparaît dans une nouvelle feuille de calcul Excel.

Résumé : étapes Power Query créées dans la tâche 1

Lorsque 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 les formules Power Query, voir Créer des formules Power Query dans Excel.

Tâche

Étape de requête

Formule

Importer un feuille de calcul Excel

Source

= Excel.Workbook(File.Contents(« C:\Products and Orders.xlsx »), null, true)

Sélectionnez la table Produits

Naviguer

= Source{[Item="Products »,Kind="Table"]}[Data]

Power Query détecte automatiquement les types de données de colonne

Type modifié

= Table.TransformColumnTypes(Products_Table,{{"ProductID », Int64.Type}, {"ProductName », type text}, {"SupplierID », Int64.Type}, {"CategoryID », Int64.Type}, {"QuantityPerUnit », type text}, {"UnitPrice », type number}, {"UnitsInStock », Int64.Type}, {"UnitsOnOrder », Int64.Type}, {"ReorderLevel », Int64.Type}, {"Discontinued », type}})

Supprimer les autres colonnes pour afficher uniquement les colonnes utiles

Autres colonnes supprimées

= Table.SelectColumns(FirstRowAsHeader,{"ProductID », « ProductName », « CategoryID », « QuantityPerUnit"})

Dans cette tâche, vous importez des données dans votre feuille de calcul Excel à partir du flux OData Northwind d’http://services.odata.org/Northwind/Northwind.svc,développez le tableau Order_Details, supprimez des colonnes, calculez un total de ligne, transformez un fichier OrderDate, groupez des lignes par ProductID et Year, renommez la requête et désactivez le téléchargement de la requête dans le feuille de calcul Excel.

Étape 1 : se connecter à un flux OData

  1. Sélectionnez Données > obtenir des données à 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. Sélectionnez OK.

  4. Dans le volet navigateur, double-cliquez sur la table Commandes.

É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 s’exécute, les lignes de la table liée(Order_Details)sont combinées en lignes avec la table primaire(Commandes).

Dans Power Query, une colonne contenant une table liée contient la valeur Enregistrement ou Table dans la cellule. Ces colonnes sont appelées colonnes structurées. L’enregistrement indique un enregistrement lié unique et représente une relation un-à-un avec les données actuelles ou la table primaire. La table indique une table liée et représente une relation un-à-plusieurs avec la table actuelle ou primaire. Une colonne structurée représente une relation dans une source de données qui possède un modèle relationnel. Par exemple, une colonne structurée indique une entité ayant une association de clé étrangère dans un flux OData ou une relation de clé étrangère dans SQL Server données.

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. En mode Aperçu des données,faites défiler horizontalement jusqu’à Order_Details colonne.

  2. Dans la Order_Details colonne, sélectionnez l’icône développer ( Développer ).

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

    1. Sélectionnez (sélectionner toutes les colonnes) pour effacer toutes les colonnes.

    2. Sélectionnez ProductID,UnitPriceet Quantity.

    3. Sélectionnez OK.

      Développer le lien de table Order_Details

      Remarque : Dans Power Query, vous pouvez développer les tables liées à partir d’une colonne et agréger 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

  1. En mode Aperçu des données,sélectionnez les colonnes suivantes :

    1. Sélectionnez la première colonne, OrderID.

    2. Appuyez sur Lat+ Clic sur la dernière colonne, Expéditeur.

    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 sélectionnez Supprimer les autres 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.

  1. En mode Aperçu des données,sélectionnez l’icône de table ( Icône de table ) dans le coin supérieur gauche de l’aperçu.

  2. Cliquez sur Ajouter une colonne personnalisée.

  3. Dans la boîte de dialogue Colonne personnalisée, dans la zone formule de colonne personnalisée, entrez [Order_Details.UnitPrice] * [Order_Details.Quantity].

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

  5. Sélectionnez 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 Aperçu des données,cliquez avec le bouton droit sur la colonne OrderDate, puis sélectionnez Transformer > Year.

  2. Renommez la colonne OrderDate en Year :

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

    2. Right-Click dans la colonne OrderDate,sélectionnez Renommer,puis entrez Year.

Étape 6 : regrouper les lignes par ProductID et Year

  1. En mode Aperçu des données,sélectionnez Année et Order_Details.ProductID.

  2. Right-Click un des en-têtes, puis sélectionnez Grouper 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. Sélectionnez 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, renommez la requête :

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

Résultats : requête finale pour la tâche 2

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

Total des ventes

Résumé : étapes Power Query créées dans la tâche 2 

Lorsque 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 les formules Power Query, voir En savoir plus sur les formules Power Query.

Tâche

Étape de requête

Formule

Se connecter à un flux OData

Source

= OData.Feed(« http://services.odata.org/Northwind/Northwind.svc », null, [Implementation="2.0"])

Sélectionner un tableau

Navigation

= 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

Added Custom

= Table.AddColumn(RemovedColumns, « Custom », each [Order_Details.UnitPrice] * [Order_Details.Quantity])

= Table.AddColumn(#"Expanded Order_Details », « Line Total », each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Changer pour un nom plus significatif, Lne Total

Colonnes renommées

= Table.RenameColumns(InsertedCustom,{{"Custom », « Line Total"}})

Transformer la colonne OrderDate pour afficher l’année

Année extraite

= Table.TransformColumns(#"Grouped Rows »,{{"Year », Date.Year, Int64.Type}})

Modifier en 

Noms plus significatifs, OrderDate et Year

Colonnes 1 renommées

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}})

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 Productset Total Sales à l’aide d’une requête Fusion et d’une opération de développement, puis chargez la requête Total Sales per Product dans le modèle de données Excel.

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

  1. Dans le feuille de calcul Excel, accédez à la requête Produits sous l’onglet de feuille de calcul Produits.

  2. Sélectionnez une cellule dans la requête, puis sélectionnez Requête>Fusionner.

  3. Dans la boîte de dialogue Fusionner, sélectionnez Produits comme table primaire, puis sélectionnez Total Sales comme requête secondaire ou associée à fusionner. Total Sales devient une nouvelle colonne structurée avec une icône de développement.

  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. Sélectionnez Enregistrer.

  6. Sélectionnez 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 Définir les niveaux de confidentialité.

    Boîte de dialogue Fusionner

Résultat

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(Produits)et une seule colonne structurée table dans la table associée(Total Sales). Sélectionnez l’icône Développer pour ajouter de nouvelles colonnes à la table primaire à partir de la table secondaire ou liée.

Fusion finale

Étape 2 : développer une colonne fusionnée

Au cours de cette étape, vous développez la colonne fusionnée avec le nom NewColumn pour créer deux nouvelles colonnes dans la requête Produits : Year et Total Sales.

  1. En mode Aperçu des données,sélectionnez Développer l’icône ( Développer ) en face de NewColumn.

  2. Dans la liste de listes de développement :

    1. Sélectionnez (sélectionner toutes les colonnes) pour effacer toutes les colonnes.

    2. Sélectionnez Annéeet Total des ventes.

    3. Sélectionnez OK.

  3. Renommez ces deux colonnes Year et Total Sales.

  4. Pour savoir quels produits et quelles années les produits ont obtenu le plus haut volume de ventes, sélectionnez Trier par ordredécroit par total des ventes.

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

Résultat

Développer le lien de table

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

Dans cette étape, vous chargez une requête dans un modèle de données Excel,afin de créer un rapport connecté au résultat de la requête. Une fois que vous avez chargé les données dans le modèle de données Excel,vous pouvez utiliser Power Pivot pour analyser vos données.

  1. Sélectionnez Accueil> fermer & chargement.

  2. Dans la boîte de dialogue Importer des données, veillez à sélectionner Ajouter ces données au modèle de données. Pour plus d’informations sur l’utilisation de cette boîte de dialogue, sélectionnez le point d’interrogation (?).

Résultat

Vous avez une requête Total Sales per Product qui combine les données du fichier Products.xlsx et du flux OData Northwind. Cette requête est appliquée à un modèle Power Pivot. En outre, les modifications apportées à la requête modifient et actualisent la table résultante dans le modèle de données.

Résumé : étapes Power Query créées dans la tâche 3

Lorsque vous effectuez des activités de requête de fusion 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 les formules 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"}, « Total Sales », Join 10.LeftOuter)

Développer une colonne de fusion

Ventes totales étendues

= Table.ExpandTableColumn(source, « Total Sales », {"Year », « Total Sales"}, {"Total Sales.Year », « Total Sales.Total Sales"})

Renommer deux colonnes

Colonnes renommées

= Table.RenameColumns(#"Expanded Total Sales »,{{"Total Sales.Year », « Year"}, {"Total Sales.Total Sales », « Total Sales"}})

Trier le total des ventes dans l’ordre croissant

Lignes triées

= Table.Sort(#"Renamed Columns »,{{"Total Sales », Order.Ascending}})

Voir aussi

Aide de Power Query

Besoin d’aide ?

Développez vos compétences dans Office
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.

×